第4部分：Python数据分析-pandas数据预处理

2023-12-25

本文介绍使用pandas对清洗完的数据进行整理以便后续的统计和分析工作，主要包括数据表的合并、排序、数值分列、数据分组及标记等工作。

一、数据表合并

内连接：

import pandas as pd
import numpy as np

df = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006],
                   "date": pd.date_range('20130102', periods=6),
                   "city": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
                   "age": [23, 44, 54, 32, 34, 32],
                   "category": ['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],
                   "price": [1200, np.nan, 2133, 5433, np.nan, 4432]},
                  columns=['id', 'date', 'city', 'category', 'age', 'price'])

df1 = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
                    "gender": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
                    "pay": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y', ],
                    "m-point": [10, 12, 20, 40, 40, 40, 30, 20]})

df_inner = pd.merge(df, df1, how='inner')
print(df_inner.dtypes)
print(df_inner.values)

id int64
date datetime64[ns]
city object
category object
age int64
price float64
gender object
pay object
m-point int64
dtype: object
[[1001 Timestamp(‘2013-01-02 00:00:00’) ‘Beijing ‘ ‘100-A’ 23 1200.0
‘male’ ‘Y’ 10]
[1002 Timestamp(‘2013-01-03 00:00:00’) ‘SH’ ‘100-B’ 44 nan ‘female’ ‘N’
12]
[1003 Timestamp(‘2013-01-04 00:00:00’) ‘ guangzhou ‘ ‘110-A’ 54 2133.0
‘male’ ‘Y’ 20]
[1004 Timestamp(‘2013-01-05 00:00:00’) ‘Shenzhen’ ‘110-C’ 32 5433.0
‘female’ ‘Y’ 40]
[1005 Timestamp(‘2013-01-06 00:00:00’) ‘shanghai’ ‘210-A’ 34 nan
‘male’ ‘N’ 40]
[1006 Timestamp(‘2013-01-07 00:00:00’) ‘BEIJING ‘ ‘130-F’ 32 4432.0
‘female’ ‘Y’ 40]]

左连接：
df_left=pd.merge(df,df1,how='left')
右连接
df_right=pd.merge(df,df1,how='right')
外连接：
df_outer=pd.merge(df,df1,how='outer')

二、设置索引列

import pandas as pd
import numpy as np

df = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006],
                   "date": pd.date_range('20130102', periods=6),
                   "city": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
                   "age": [23, 44, 54, 32, 34, 32],
                   "category": ['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],
                   "price": [1200, np.nan, 2133, 5433, np.nan, 4432]},
                  columns=['id', 'date', 'city', 'category', 'age', 'price'])

df1 = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
                    "gender": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
                    "pay": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y', ],
                    "m-point": [10, 12, 20, 40, 40, 40, 30, 20]})

df_inner = pd.merge(df, df1, how='inner')
df_inner.set_index('id')

三、排序（按索引、按数值）

import pandas as pd
import numpy as np

df = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006],
                   "date": pd.date_range('20130102', periods=6),
                   "city": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
                   "age": [23, 44, 54, 32, 34, 32],
                   "category": ['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],
                   "price": [1200, np.nan, 2133, 5433, np.nan, 4432]},
                  columns=['id', 'date', 'city', 'category', 'age', 'price'])

df1 = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
                    "gender": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
                    "pay": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y', ],
                    "m-point": [10, 12, 20, 40, 40, 40, 30, 20]})

df_inner = pd.merge(df, df1, how='inner')
# 按照列值进行排序
df_inner.sort_values(by=['age'],inplace=True)
print(df_inner)
# 按照索引列排序
df_inner.sort_index(inplace=True)
print(df_inner)

 id       date         city category  age   price  gender pay  m-point
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40
1 1002 2013-01-03 SH 100-B 44 NaN female N 12
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20
id date city category age price gender pay m-point
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10
1 1002 2013-01-03 SH 100-B 44 NaN female N 12
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40

四、数据分组

import pandas as pd
import numpy as np

df = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006],
                   "date": pd.date_range('20130102', periods=6),
                   "city": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
                   "age": [23, 44, 54, 32, 34, 32],
                   "category": ['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],
                   "price": [1200, np.nan, 2133, 5433, np.nan, 4432]},
                  columns=['id', 'date', 'city', 'category', 'age', 'price'])

df1 = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
                    "gender": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
                    "pay": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y', ],
                    "m-point": [10, 12, 20, 40, 40, 40, 30, 20]})

pd.set_option('display.max_rows', 500) # 打印最大行数
pd.set_option('display.max_columns', 500) # 打印最大列数
pd.set_option('max_colwidth',100)#设置value的显示长度为100，默认为50
pd.set_option('display.expand_frame_repr', False) # 关闭自动换行
df_inner = pd.merge(df, df1, how='inner')
# 单条件分组
df_inner['priceLevel'] = np.where(df_inner['price'] > 3000, 'high', 'low')
print(df_inner)
# 多条件分组
df_inner.loc[(df_inner['gender'] == 'female') & (df_inner['price'] >= 4000), 'highFemale'] = 1
print(df_inner)

 id       date         city category  age   price  gender pay  m-point priceLevel
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10 low
1 1002 2013-01-03 SH 100-B 44 NaN female N 12 low
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20 low
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40 high
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40 low
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40 high
id date city category age price gender pay m-point priceLevel highFemale
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10 low NaN
1 1002 2013-01-03 SH 100-B 44 NaN female N 12 low NaN
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20 low NaN
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40 high 1.0
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40 low NaN
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40 high 1.0

五、数据分列

import pandas as pd
import numpy as np

df = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006],
                   "date": pd.date_range('20130102', periods=6),
                   "city": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
                   "age": [23, 44, 54, 32, 34, 32],
                   "category": ['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],
                   "price": [1200, np.nan, 2133, 5433, np.nan, 4432]},
                  columns=['id', 'date', 'city', 'category', 'age', 'price'])

df1 = pd.DataFrame({"id": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
                    "gender": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],
                    "pay": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y', ],
                    "m-point": [10, 12, 20, 40, 40, 40, 30, 20]})

pd.set_option('display.max_rows', 500)  # 打印最大行数
pd.set_option('display.max_columns', 500)  # 打印最大列数
pd.set_option('max_colwidth', 100)  # 设置value的显示长度为100，默认为50
pd.set_option('display.expand_frame_repr', False)  # 关闭自动换行
df_inner = pd.merge(df, df1, how='inner')
print(df_inner)
# 对category字段的值进行分列100-A，100为类别号，A为size
split_df = pd.DataFrame((x.split('-') for x in df_inner['category']), index=df_inner.index, columns=['category_id', 'size'])
df_inner = pd.merge(df_inner, split_df, right_index=True, left_index=True)
print(df_inner)

 id       date         city category  age   price  gender pay  m-point
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10
1 1002 2013-01-03 SH 100-B 44 NaN female N 12
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40
id date city category age price gender pay m-point category_id size
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10 100 A
1 1002 2013-01-03 SH 100-B 44 NaN female N 12 100 B
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20 110 A
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40 110 C
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40 210 A
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40 130 F