⼀、透视表
piovt(功能较少)
展开
df=pd.read_csv(r'data\table.csv')
df.pivot(index='ID',columns='Gender',values='Height').head()
Gender F M
ID
1101 NaN 173.0
1102 192.0 NaN
1103 NaN 186.0
1104 167.0 NaN
1105 159.0 NaN
pivot_table
功能更多,速度更慢
pd.pivot_table(df,index='ID',columns='Gender',values='Height').head()
Gender F M
ID
1101 NaN 173.0
1102 192.0 NaN
1103 NaN 186.0
1104 167.0 NaN
1105 159.0 NaN
1.aggfunc
对组内进行聚合统计
pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum']).head()
mean sum
Gender F M F M
School
S_1 173.125000 178.714286 1385 1251
S_2 173.727273 172.000000 1911 1548
2.margins
汇总边际状态
pd.pivot_table(df,index='School',columns='Gender',values='Height',aggfunc=['mean','sum'],margins=True).head()
mean sum
Gender F M All F M All
School
S_1 173.125000 178.714286 175.733333 1385 1251 2636
S_2 173.727273 172.000000 172.950000 1911 1548 3459
All 173.473684 174.937500 174.142857 3296 2799 6095
3.行、列、值都可为多级索引
pd.pivot_table(df,index=['School','Class'],
columns=['Gender','Address'],
values=['Height','Weight'])
Height ... Weight
Gender F M ... F M
Address street_1 street_2 street_4 street_5 street_6 street_7 street_1 street_2 ... street_6 street_7 street_1 street_2 street_4 street_5 street_6 street_7
School Class ...
S_1 C_1 NaN 179.5 159.0 NaN NaN NaN 173.0 186.0 ... NaN NaN 63.0 82.0 NaN NaN NaN NaN
C_2 NaN NaN 176.0 162.0 167.0 NaN NaN NaN ... 63.0 NaN NaN NaN NaN 68.0 53.0 NaN
C_3 175.0 NaN NaN 187.0 NaN NaN NaN 195.0 ... NaN NaN NaN 70.0 68.0 NaN NaN 82.0
S_2 C_1 NaN NaN NaN 159.0 161.0 NaN NaN NaN ... 61.0 NaN NaN NaN 71.0 NaN NaN 84.0
C_2 NaN NaN NaN NaN NaN 188.5 175.0 NaN ... NaN 76.5 74.0 NaN 91.0 100.0 NaN NaN
C_3 NaN NaN 157.0 NaN 164.0 190.0 NaN NaN ... 81.0 99.0 NaN NaN 73.0 88.0 NaN NaN
C_4 NaN 176.0 NaN NaN 175.5 NaN NaN NaN ... 57.0 NaN NaN NaN NaN NaN NaN 82.0
[7 rows x 24 columns]
crosstab(交叉表)
典型用途分组统计(暂时不支持分组统计)
pd.crosstab(index=df['Address'],columns=df['Gender'])
Gender F M
Address
street_1 1 2
street_2 4 2
street_4 3 5
street_5 3 3
street_6 5 1
street_7 3 3
1.values和aggfunc
分组对数据进行聚合操作,这两个参数必须成对出现
pd.crosstab(index=df['Address'],columns=df['Gender'],values=1,aggfunc='count')
Gender F M
Address
street_1 1 2
street_2 4 2
street_4 3 5
street_5 3 3
street_6 5 1
street_7 3 3
二、其他变形方法
melt
将展开的数据压缩
pivoted=df.pivot(index='ID',columns='Gender',values='Math').head()
stacked=pivoted.reset_index().melt(id_vars=['ID'],value_vars=['F','M'],value_name='Math').dropna().set_index('ID').sort_index()
Gender Math
ID
1101 M 34.0
1102 F 32.5
1103 M 87.2
1104 F 80.4
1105 F 84.8
压缩与展开
1.stack
压缩,两个参数level和dropna
可看做将横向的索引放到纵向,类似melt,参数level可指定变化的列索引是哪⼀层
df_s = pd.pivot_table(df,index=['Class','ID'],columns='Gender',values=['Height','Weight'])
df_stacked = df_s.stack()
df_stacked.groupby('Class').head(2)
Height Weight
Class ID Gender
C_1 1101 M 173.0 63.0
1102 F 192.0 73.0
C_2 1201 M 188.0 68.0
1202 F 176.0 94.0
C_3 1301 M 161.0 68.0
1302 F 175.0 57.0
C_4 2401 F 192.0 62.0
2402 M 166.0 82.0
2. unstack
功能类似pivot_table
df_stacked.unstack()
df_stacked.unstack().equals(df_s)
True
三、哑变量与因子化
1. Dummy Variable(哑变量)
df_d = df[['Class','Gender','Weight']]
将上面的表格前两列转为哑变量
pd.get_dummies(df_d[['Class','Gender']]).head()
Class_C_1 Class_C_2 Class_C_3 Class_C_4 Gender_F Gender_M
0 1 0 0 0 0 1
1 1 0 0 0 1 0
2 1 0 0 0 0 1
3 1 0 0 0 1 0
4 1 0 0 0 1 0
2. factorize方法
用于自然数编码并且缺失值会被记做-1,其中sort参数表示是否排序后赋值
codes, uniques = pd.factorize(['b', None, 'a', 'c', 'b'], sort=True)
display(codes)
display(uniques)
[ 1 -1 0 2 1]
['a' 'b' 'c']
练习一
a
res=pd.pivot_table(df,index=['State','COUNTY','SubstanceName'],columns='YYYY',values='DrugReports',fill_value='-')
YYYY 2010 2011 2012 2013 2014 2015 2016 2017
State COUNTY SubstanceName
KY ADAIR Buprenorphine - 3 5 4 27 5 7 10
Codeine - - 1 - - - - 1
Fentanyl - - 1 - - - - -
Heroin - - 1 2 - 1 - 2
Hydrocodone 6 9 10 10 9 7 11 3
res.reset_index().rename_axis(columns={'YYYY':''})
State COUNTY SubstanceName 2010 2011 2012 2013 2014 2015 2016 2017
0 KY ADAIR Buprenorphine - 3 5 4 27 5 7 10
1 KY ADAIR Codeine - - 1 - - - - 1
2 KY ADAIR Fentanyl - - 1 - - - - -
3 KY ADAIR Heroin - - 1 2 - 1 - 2
4 KY ADAIR Hydrocodone 6 9 10 10 9 7 11 3
b
melted = result.melt(id_vars=result.columns[:3],value_vars=result.columns[-8:],var_name='YYYY',value_name='DrugReports').query('DrugReports != "-"')
res = melted.sort_values(by=['State','COUNTY','YYYY','SubstanceName']).reset_index().drop(columns='index')
State COUNTY SubstanceName YYYY DrugReports
0 KY ADAIR Hydrocodone 2010 6
1 KY ADAIR Methadone 2010 1
2 KY ADAIR Buprenorphine 2011 3
3 KY ADAIR Hydrocodone 2011 9
4 KY ADAIR Morphine 2011 2
练习二
a
pd.pivot_table(df,index=['日期','时间','维度','经度'],columns='方向',values=['烈度','深度','距离'],fill_value='-').stack(level=0).rename_axis(index={None:'地震参数'})
方向 east north north_east north_west south south_east south_west west
日期 时间 维度 经度 参数
1912.08.09 12:29:00 AM 40.6 27.2 深度 - - - - - 16 - -
烈度 - - - - - 6.7 - -
距离 - - - - - 4.3 - -
1912.08.10 12:23:00 AM 40.6 27.1 深度 - - - - - - 15 -
烈度 - - - - - - 6 -
b
df_result = res.unstack().stack(0)[(~(res.unstack().stack(0)=='-')).any(1)].reset_index()
df_result.columns.name=None
df_result = df_result.sort_index(axis=1).astype({'深度':'float64','烈度':'float64','距离':'float64'})
方向 日期 时间 深度 烈度 经度 维度 距离
0 south_east 1912.08.09 12:29:00 AM 16.0 6.7 27.2 40.6 4.3
1 south_west 1912.08.10 12:23:00 AM 15.0 6.0 27.1 40.6 2.0
2 south_west 1912.08.10 12:30:00 AM 15.0 5.2 27.1 40.6 2.0
3 south_east 1912.08.11 12:19:04 AM 30.0 4.9 27.2 40.6 4.3
4 south_west 1912.08.11 12:20:00 AM 15.0 4.5 27.1 40.6 2.0