正文
pandas基础(2)_多重索引
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1：多重索引的构造
>>> #下面显示构造pd.MultiIndex
>>> df1=DataFrame(np.random.randint(0,150,size=(6,3)),columns=['java','html5','python'])
>>> import pandas as pd
>>> df1=DataFrame(np.random.randint(0,150,size=(6,3)),columns=['java','html5','python'],index=pd.MultiIndex.from_arrays([['张三','张三','侯少','侯少','a','a'],['M','E','M','E','M','E']]))
>>> df1#因为Python自身的原因，对汉字的识别不是太好，所以汉字被？代替了
java html5 python
???? M 2 13 76
E 141 67 84
M 116 83 8
E 70 118 125
a M 74 0 76
E 111 31 8
>>> #使用元组tuple创建
df2=DataFrame(np.random.randint(0,150,size=(6,3)),columns=['java','html','python'],index=pd.MultiIndex.from_tuples([('a','1'),('a','11'),('b','1'),('b','11'),('c','1'),('c','11')]))
>>> df2
java html python
a 1 32 144 99
11 104 101 16
b 1 93 98 41
11 59 30 45
c 1 91 17 149
11 9 28 59
>>> #使用product
df2=DataFrame(np.random.randint(0,150,size=(6,3)),columns=['java','html','python'],index=pd.MultiIndex.from_product([['zhangsan ','lisi','wangwu'],['mid','end']]))
>>> df2
java html python
zhangsan mid 50 128 54
end 3 4 91
lisi mid 4 93 110
end 116 123 122
wangwu mid 88 25 54
end 48 146 57
>>> #对dataFrame同样可以设置成多重索引
df2=DataFrame(np.random.randint(0,150,size=(3,6)),columns=pd.MultiIndex.from_product([['java','html','python'],['mid','end']]),index=['张三','李四','王五'])
>>> df2
java html python
mid end mid end mid end
???? 33 38 112 70 113 110
???? 29 46 132 91 117 128
???? 73 56 118 82 132 39
>>>
>>> df2['java','mid']#查询某一列
???? 33
???? 29
???? 73
Name: (java, mid), dtype: int32
>>> s['zhangsan':'lisi']#其实就是一个Series
Series([], dtype: int64)
>>> s.iloc[0:3]
a 0 1
1 2
b 0 3
dtype: int64
>>> #切片
>>> df2['张三':'王五']
java html python
mid end mid end mid end
???? 33 38 112 70 113 110
???? 29 46 132 91 117 128
???? 73 56 118 82 132 39
>>>df2.iloc[0:4]#推荐使用
Df2[‘张三’，‘期中’]和df2.loc[‘张三’].loc[‘期中’]
#如何一级索引有多个，对二级索引会遇到问题，也就是说，无法直接对二级进行索引
必须把二级索引变成一级索引才可以进行索引
>>> df2.stack()
html java python
???? end 70 38 110
mid 112 33 113
end 91 46 128
mid 132 29 117
end 82 56 39
mid 118 73 132
>>> #stack =堆----》行
end mid
???? html 70 112
java 38 33
python 110 113
html 91 132
java 46 29
python 128 117
html 82 118
java 56 73
python 39 132
>>> #默认为-1
2：多重索引的计算
>>> df2
java html python
mid end mid end mid end
???? 33 38 112 70 113 110
???? 29 46 132 91 117 128
???? 73 56 118 82 132 39
>>> df1.sum()
java 514
html5 312
python 377
dtype: int64
>>> df1.sum(axis=0)
java 514
html5 312
python 377
dtype: int64
>>> df1.sum(axis=1)#对列
???? M 91
E 292
M 207
E 313
a M 150
E 150
dtype: int64
>>> df1.sum(axis=1)#对列求和，得到每行的和
???? M 91
E 292
M 207
E 313
a M 150
E 150
dtype: int64
>>> df1.std
<bound method DataFrame.std of java html5 python
???? M 2 13 76
E 141 67 84
M 116 83 8
E 70 118 125
a M 74 0 76
E 111 31 8>
>>> #求方差
>>> df1.std(axis=1)
???? M 39.929104
E 38.759945
M 55.344376
E 29.938827
a M 43.312816
E 54.064776
dtype: float64
>>> df1.max()
java 141
html5 118
python 125
dtype: int32
3多重索引的拼接
>>> nd = np.random.randint(0,10,size=(3,3))
>>> nd
array([[9, 9, 4],
[7, 2, 4],
[1, 6, 1]])
>>> np.concatenate ((nd,nd),axis=0)#在列方向就行拼接
array([[9, 9, 4],
[7, 2, 4],
[1, 6, 1],
[9, 9, 4],
[7, 2, 4],
[1, 6, 1]])
>>> np.concatenate ([nd,nd],axis=1)#在行方向进行拼接
array([[9, 9, 4, 9, 9, 4],
[7, 2, 4, 7, 2, 4],
[1, 6, 1, 1, 6, 1]])
>>> def make_df(cols,inds):
data = {c:[c+str(i) for i in cols]for c in cols}
return DataFrame(data,index=inds,columns=cols)
>>> make_df(['A','B'],[1,2])
A B
1 AA BA
2 AB BB
>>> df1=make_df(list('AB'),[0,1])
>>> df2=make_df(list('AB'),[2,3])
>>> pd.concat ([df1,df2])#默认在列方向进行拼接
A B
0 AA BA
1 AB BB
2 AA BA
3 AB BB
>>> #优先增加行数
>>> pd.concat ((df1,df2),axis=1)
A B A B
0 AA BA NaN NaN
1 AB BB NaN NaN
2 NaN NaN AA BA
3 NaN NaN AB BB
>>> #注意index在级联时可以重复
3)
>>> #列名可以相同但是不建议
>>> df3= make_df(list('AB'),[0,1])
>>> df4=make_df(list('VB'),[1,2])
>>> pd.concat((df3,df4))#只能传入一个参数
A B V
0 AA BA NaN
1 AB BB NaN
1 NaN BV VV
2 NaN BB VB
>>> #3种连接方式
>>> #1：外连接：补NaN（默认模式）
>>> df1= make_df(list('AB'),[1,3])
>>> df2= make_df(list('AB'),[2,4])
>>> df2= make_df(list('BC'),[2,4])
>>> pd.concat ([df1,df2],join='inner')#连接都有的部分
B
1 BA
3 BB
2 BB
4 BC
>>> pd.concat ([df1,df2],join='outer')
A B C
1 AA BA NaN
3 AB BB NaN
2 NaN BB CB
4 NaN BC CC
>>> #内连接只连接匹配项
>>> #3：连接指定轴 join_axes所以CDF的F便不显示了
>>> df3= make_df(list('ACD'),[0,1,2])
>>> df4= make_df(list('CDF'),[3,4,5])
>>> pd.concat([df3,df4],join_axes=[df3.columns])
A C D
0 AA CA DA
1 AC CC DC
2 AD CD DD
3 NaN CC DC
4 NaN CD DD
5 NaN CF DF
>>> #join_axes 某一个DataFrame列索引为新的列索引值
>>> #3使用append()函数添加
>>> #concat方法属于pandas
>>> #append()在后面添加
>>> #concat([df1,df2])
>>> #df1.append(df2)
>>> #merge与concat的区别是，merge需要依据某一共同的行或列来进行合并
>>> #使用pd.merge（）合并时，会自动根据两者相同column名称的那一属性，作为key来进行合并，注意每一列的顺序不要求一致
>>> #一对一合并
>>> df1 = DataFrame({'employee':['po','sara','danis'],'group':['sail','counting','marcketing']})
>>> df2 = DataFrame({'employee':['po','sara','danis'],'work_time':[2,3,1]})
>>> df1
employee group
0 po sail
1 sara counting
2 danis marcketing
>>> df2
employee work_time
0 po 2
1 sara 3
2 danis 1
>>> pd.merge (df1,df2)
employee group work_time
0 po sail 2
1 sara counting 3
2 danis marcketing 1
>>> pd.concat([df1,df2])
employee group work_time
0 po sail NaN
1 sara counting NaN
2 danis marcketing NaN
0 po NaN 2.0
1 sara NaN 3.0
2 danis NaN 1.0
>>> df3 = DataFrame({'employee':['po','sara','liulei'],'work_time':[2,3,1]})
>>> pd.merge(df1,df3)
employee group work_time
0 po sail 2
1 sara counting 3
>>> #merge只合并相同属性里面都有的项
>>> #下面是merge的多对一的合并
>>> df1 = DataFrame({'employee':['po','sara','danis'],'work_time':[2,3,1]})
>>> df2 = DataFrame({'employee':['po','po','danis'],'group':['sail','counting','marcketing']})
>>> pd.merge(df1,df2)
employee work_time group
0 po 2 sail
1 po 2 counting
2 danis 1 marcketing
>>> #出现了两个po
>>> #下面是多对多的合并
>>> df1 = DataFrame({'employee':['po','sara','danis'],'group':['sail','counting','marcketing']})
>>> df1 = DataFrame({'employee':['po','po','danis'],'group':['sail','counting','marcketing']})
>>> df2 = DataFrame({'employee':['po','po','danis'],'work_time':[2,3,1]})
>>> pd.merge(df1,df2)
employee group work_time
0 po sail 2
1 po sail 3
2 po counting 2
3 po counting 3
4 danis marcketing 1
>>> #1*2*2的模式
>>> #使用merge多对多可以来处理重名等数据的情况
>>> df3= DataFrame({'employee':['po','Summer','Flower'],'group':['sail','marking','serch'],'WorkTime':[1,2,3]})
>>> df4= DataFrame({'employee':['po','Summer','Flower'],'group':['sail','marking','serch'],'salary':[12000,20000,10002]})
>>> df3
WorkTime employee group
0 1 po sail
1 2 Summer marking
2 3 Flower serch
>>> df4
employee group salary
0 po sail 12000
1 Summer marking 20000
2 Flower serch 10002
>>> pd.merge(df3,df4)
WorkTime employee group salary
0 1 po sail 12000
1 2 Summer marking 20000
2 3 Flower serch 10002
>>> df3= DataFrame({'employee':['po','Winter','Flower'],'group':['marketing','marking','serch'],'WorkTime':[1,2,3]})
>>> pd.merge(df3,df4)
WorkTime employee group salary
0 3 Flower serch 10002
>>> pd.merge(df3,df4,on='employee')
WorkTime employee group_x group_y salary
0 1 po marketing sail 12000
1 3 Flower serch serch 10002
>>> #出现两行数据的原因是指定了employee相同就可以合并
>> pd.merge(df3,df4,on='group')
WorkTime employee_x group employee_y salary
0 2 Winter marking Summer 20000
1 3 Flower serch Flower 10002
>>> pd.merge(df3,df4,on='group',suffixes=['_A','_B'])
WorkTime employee_A group employee_B salary
0 2 Winter marking Summer 20000
1 3 Flower serch Flower 10002
>>> df3= DataFrame({'employee':['po','Winter','Flower'],'group':['marketing','marking','serch'],'WorkTime':[1,2,3]})
>>> df4= DataFrame({'employer':['po','Summer','Flower'],'group':['sail','marking','serch'],'salary':[12000,20000,10002]})
>>> pd.merge(df3,df4)
WorkTime employee group employer salary
0 2 Winter marking Summer 20000
1 3 Flower serch Flower 10002
>>> pd.merge(df3,df4,left_on='employee',right_on='employer')
WorkTime employee group_x employer group_y salary
0 1 po marketing po sail 12000
1 3 Flower serch Flower serch 10002
>>> #df3主键key为employee和df4主键为employer,两者不同但又想相互匹配时，可以指定前者的left_on为employee和后者的right_on为employer这时两者可以进行匹配
>>> #内合并与外合并
>>> #内合并只保留两者都有的数据
>>> df1=DataFrame({'age':[18,22,33],'height':[175,169,180]})
>>> df1=DataFrame({'age':[18,23,32],'height':[175,169,180]})
>>> df2=DataFrame({'age':[18,22,33],'weight':[175,169,180]})
>>> pd.merge(df1,df2)
age height weight
0 18 175 175
>>> pd.merge(df1,df2,how='outer')
age height weight
0 18 175.0 175.0
1 23 169.0 NaN
2 32 180.0 NaN
3 22 NaN 169.0
4 33 NaN 180.0
>>> #默认为内合并，通过how可以指定合并类型
>>>
>>> pd.merge(df1,df2,how='left')
age height weight
0 18 175 175.0
1 23 169 NaN
2 32 180 NaN
>>> pd.merge(df1,df2,how='right')
age height weight
0 18 175.0 175
1 22 NaN 169
2 33 NaN 180
>>> #left保留前者的数据，right保留后者数据
>>> #left保留前者df1的数据，right保留后者df2数据
>>> #下面是列冲突
>>> df3= DataFrame({'employee':['po','Winter','Flower'],'group':['marketing','marking','serch'],'WorkTime':[1,2,3]})
>>> df4= DataFrame({'employee':['po','Summer','Flower'],'group':['sail','marking','serch'],'salary':[12000,20000,10002]})
>>> pd.merge(df3,df4)
WorkTime employee group salary
0 3 Flower serch 10002
>>> pd.merge(df3,df4,on='employee',suffixes=['_李','_王'])
WorkTime employee group_?? group_?? salary
0 1 po marketing sail 12000
1 3 Flower serch serch 10002
>>> #因为两者的employee和group相同，当指定employee为主键时，suffixes修改的就是group
4：总结：
多重索引也是pandas里非常重要的知识点，要牢牢掌握
