Pandas数据处理(2): 重复行处理、数据类型转换与数据变形

3、重复行处理

import pandas as pd
import numpy as np
df

	EmployeeID	birthdate_key	age	city_name	department	job_title	gender
0	1318	1/3/1954	61	Vancouver	Executive	CEO	M
1	1319	1/3/1957	58	Vancouver	Executive	VP Stores	F
2	1320	1/2/1955	57	Vancouver	Executive	Legal Counsel	F
3	1321	1/2/1959	57	Vancouver	Executive	VP Human Resources	M
4	1322	1/9/1958	57	Vancouver	Executive	VP Finance	M
5	1323	1/9/1962	53	Vancouver	Executive	Exec Assistant, VP Stores	M
6	1322	1/9/1958	57	Vancouver	Executive	VP Finance	M
7	1319	1/3/1957	58	Vancouver	Executive	VP Stores	F

#检测重复值
df.duplicated()

0 False
1 False
2 False
3 False
4 False
5 False
6 True
7 True
dtype: bool

#删除重复行
df.drop_duplicates()

	EmployeeID	birthdate_key	age	city_name	department	job_title	gender
0	1318	1/3/1954	61	Vancouver	Executive	CEO	M
1	1319	1/3/1957	58	Vancouver	Executive	VP Stores	F
2	1320	1/2/1955	57	Vancouver	Executive	Legal Counsel	F
3	1321	1/2/1959	57	Vancouver	Executive	VP Human Resources	M
4	1322	1/9/1958	57	Vancouver	Executive	VP Finance	M
5	1323	1/9/1962	53	Vancouver	Executive	Exec Assistant, VP Stores	M

#按照指标去除
df.drop_duplicates(subset=["age"],keep="last")#保存最后出现的行(若是first则是最早的)

	EmployeeID	birthdate_key	age	city_name	department	job_title	gender
0	1318	1/3/1954	61	Vancouver	Executive	CEO	M
5	1323	1/9/1962	53	Vancouver	Executive	Exec Assistant, VP Stores	M
6	1322	1/9/1958	57	Vancouver	Executive	VP Finance	M
7	1319	1/3/1957	58	Vancouver	Executive	VP Stores	F

4、数据类型转换

df=pd.read_csv("data/random_data.csv")
df

	product_name	quantity	unit_price	category	rating	gender
0	Laptop	11	119.98	Electronics	4.5	Female
1	Mouse	6	26.09	Electronics	3.8	Male
2	Keyboard	2	361.70	NaN	5.0	Male
3	Monitor	1	88.81	NaN	4.2	Female
4	Headphones	11	175.60	Electronics	3.5	Male
5	USB Drive	23	406.39	NaN	NaN	Female
6	Printer	25	NaN	Peripherals	4.9	Female
7	Scanner	24	356.09	Electronics	2.8	Male
8	Webcam	6	435.50	NaN	NaN	Female
9	Speaker	24	184.76	Peripherals	4.0	Female

print(df.dtypes)
#int转string
df["quantity"]=df["quantity"].astype("float")
df

product_name object
quantity int64
unit_price float64
category object
rating float64
gender object
dtype: object

	product_name	quantity	unit_price	category	rating	gender
0	Laptop	11.0	119.98	Electronics	4.5	Female
1	Mouse	6.0	26.09	Electronics	3.8	Male
2	Keyboard	2.0	361.70	NaN	5.0	Male
3	Monitor	1.0	88.81	NaN	4.2	Female
4	Headphones	11.0	175.60	Electronics	3.5	Male
5	USB Drive	23.0	406.39	NaN	NaN	Female
6	Printer	25.0	NaN	Peripherals	4.9	Female
7	Scanner	24.0	356.09	Electronics	2.8	Male
8	Webcam	6.0	435.50	NaN	NaN	Female
9	Speaker	24.0	184.76	Peripherals	4.0	Female

#将gender转换成category类型便于管理
df["gender"]=df.gender.astype("category")
df.gender

0 Female
1 Male
2 Male
3 Female
4 Male
5 Female
6 Female
7 Male
8 Female
9 Female
Name: gender, dtype: category
Categories (2, object): ['Female', 'Male']

df["ismale"]=df.gender.map({"Female":False,"Male":True})
df

	product_name	quantity	unit_price	category	rating	gender	ismale
0	Laptop	11.0	119.98	Electronics	4.5	Female	False
1	Mouse	6.0	26.09	Electronics	3.8	Male	True
2	Keyboard	2.0	361.70	NaN	5.0	Male	True
3	Monitor	1.0	88.81	NaN	4.2	Female	False
4	Headphones	11.0	175.60	Electronics	3.5	Male	True
5	USB Drive	23.0	406.39	NaN	NaN	Female	False
6	Printer	25.0	NaN	Peripherals	4.9	Female	False
7	Scanner	24.0	356.09	Electronics	2.8	Male	True
8	Webcam	6.0	435.50	NaN	NaN	Female	False
9	Speaker	24.0	184.76	Peripherals	4.0	Female	False

5、数据变形

data={
    "ID":[1,2],
    "name":["Alice Smith","Bob Smith"],
    "Math":[90,85],
    "English":[88,92],
    "Science":[95,89]
}
df=pd.DataFrame(data)
df

	ID	name	Math	English	Science
0	1	Alice Smith	90	88	95
1	2	Bob Smith	85	92	89

#将宽表转换成长表
df2=pd.melt(df,id_vars=["ID","name"],var_name="lessons",value_name="Score")
df2.sort_values("name")

	ID	name	lessons	Score
0	1	Alice Smith	Math	90
2	1	Alice Smith	English	88
4	1	Alice Smith	Science	95
1	2	Bob Smith	Math	85
3	2	Bob Smith	English	92
5	2	Bob Smith	Science	89

#将长表转成宽表(原路返回)
pd.pivot(df2,index=["ID","name"],columns="lessons",values="Score")

	lessons	English	Math	Science
ID	name
1	Alice Smith	88	90	95
2	Bob Smith	92	85	89

#name列分开
df[["first name","last name"]]=df["name"].str.split(" ",expand=True)
df

	ID	name	Math	English	Science	first name	last name
0	1	Alice Smith	90	88	95	Alice	Smith
1	2	Bob Smith	85	92	89	Bob	Smith