一.append和assign
1.append方法
(a)利用序列号添加行(必须指定name)
df_append=df.loc[:3,['Gender','Height']].copy()
Gender Height
0 M 173
1 F 192
2 M 186
3 F 167
s = pd.Series({'Gender':'F','Height':188},name='new_row')
df_append.append(s)
Gender Height
0 M 173
1 F 192
2 M 186
3 F 167
new_row F 188
(b)用DataFrame添加表
df_temp = pd.DataFrame({'Gender':['F','M'],'Height':[188,176]},index=['new_1','new_2'])
df_append.append(df_temp)
Gender Height
0 M 173
1 F 192
2 M 186
3 F 167
new_1 F 188
new_2 M 176
2.assign方法
该方法主要用于添加列,列名直接由参数指定:
s = pd.Series(list('abcd'),index=range(4))
df_append.assign(Letter=s)
Gender Height Letter
0 M 173 a
1 F 192 b
2 M 186 c
3 F 167 d
二.combine与update
1.comine方法
comine和update都是用于表的填充函数,可以根据某种规则填充
(a)填充对象
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [8, 7], 'B': [6, 5]})
df1.combine(df2,lambda x,y:x if x.mean()>y.mean() else y)
A B
0 8 6
1 7 5
df2 = pd.DataFrame({'B': [8, 7], 'C': [6, 5]},index=[1,2])
df1.combine(df2,lambda x,y:x if x.mean()>y.mean() else y)
A B C
0 NaN NaN NaN
1 NaN 8.0 6.0
2 NaN 7.0 5.0
df1.combine(df2,lambda x,y:x if x.mean()>y.mean() else y,overwrite=False)
A B C
0 1.0 NaN NaN
1 2.0 8.0 6.0
2 NaN 7.0 5.0
df1.combine(df2,lambda x,y:x if x.mean()>y.mean() else y,fill_value=-1)
A B C
0 1.0 -1.0 -1.0
1 2.0 8.0 6.0
2 -1.0 7.0 5.0
(b)combine_first方法
这个方法作用是用df2填补df1的缺失值,功能比较简单,但很多时候会比combine更常用,下面举两个例子:
df1 = pd.DataFrame({'A': [None, 0], 'B': [None, 4]})
df2 = pd.DataFrame({'A': [1, 1], 'B': [3, 3]})
df1.combine_first(df2)
A B
0 1.0 3.0
1 0.0 4.0
df1 = pd.DataFrame({'A': [None, 0], 'B': [4, None]})
df2 = pd.DataFrame({'B': [3, 3], 'C': [1, 1]}, index=[1, 2])
df1.combine_first(df2)
A B C
0 NaN 4.0 NaN
1 0.0 3.0 1.0
2 NaN 3.0 1.0
2.update方法
(a)三个特点
1.返回的框索引只会与被调用框的一致(默认使用左连接,下一节会介绍)
2.第二个框中的nan元素不会起作用
3.没有返回值,直接在df上操作
(b)举例
df1 = pd.DataFrame({'A': [1, 2, 3],
'B': [400, 500, 600]})
df2 = pd.DataFrame({'B': [4, 5, 6],
'C': [7, 8, 9]})
df1.update(df2)
df1
A B
0 1 4
1 2 5
2 3 6
df1 = pd.DataFrame({'A': ['a', 'b', 'c'],
'B': ['x', 'y', 'z']})
df2 = pd.DataFrame({'B': ['d', 'e']}, index=[1,2])
df1.update(df2)
df1
A B
0 a x
1 b d
2 c e
df1 = pd.DataFrame({'A': [1, 2, 3],
'B': [400, 500, 600]})
df2 = pd.DataFrame({'B': [4, np.nan, 6]})
df1.update(df2)#缺失值不会填充
df1
A B
0 1 4.0
1 2 500.0
2 3 6.0
三.concat方法
concat方法可以在两个维度上拼接,默认纵向凭借(axis=0),拼接方式默认外连接
所谓外连接,就是取拼接方向的并集,而’inner’时取拼接方向(若使用默认的纵向拼接,则为列的交集)的交集
df1 = pd.DataFrame({'A': ['A0', 'A1'],
'B': ['B0', 'B1']},
index = [0,1])
df2 = pd.DataFrame({'A': ['A2', 'A3'],
'B': ['B2', 'B3']},
index = [2,3])
df3 = pd.DataFrame({'A': ['A1', 'A3'],
'D': ['D1', 'D3'],
'E': ['E1', 'E3']},
index = [1,3])
pd.concat([df1,df2])
A B
0 A0 B0
1 A1 B1
2 A2 B2
3 A3 B3
pd.concat([df1,df2],axis=1)
A B A B
0 A0 B0 NaN NaN
1 A1 B1 NaN NaN
2 NaN NaN A2 B2
3 NaN NaN A3 B3
#join设置为内连接(由于axis=0,因此列取交集)
pd.concat([df3,df1],join='inner')
A
1 A1
3 A3
0 A0
1 A1
#join设置为外链接
pd.concat([df3,df1],join='outer',sort=True)
A B D E
1 A1 NaN D1 E1
3 A3 NaN D3 E3
0 A0 B0 NaN NaN
1 A1 B1 NaN NaN
四.merge与join
1.merge函数
merge函数的作用是将两个pandas对象横向合并,遇到重复的索引项时会使用笛卡尔积,默认inner连接,可选left、outer、right连接
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
'key2': ['K0', 'K1', 'K0', 'K1'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
right2 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3']})
pd.merge(left, right, on='key1')
key1 key2_x A B key2_y C D
0 K0 K0 A0 B0 K0 C0 D0
1 K0 K1 A1 B1 K0 C0 D0
2 K1 K0 A2 B2 K0 C1 D1
3 K1 K0 A2 B2 K0 C2 D2
4 K2 K1 A3 B3 K0 C3 D3
pd.merge(left, right, on=['key1','key2'])
key1 key2 A B C D
0 K0 K0 A0 B0 C0 D0
1 K1 K0 A2 B2 C1 D1
2 K1 K0 A2 B2 C2 D2
2.join函数
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2']},
index=['K0', 'K1', 'K2'])
right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
'D': ['D0', 'D2', 'D3']},
index=['K0', 'K2', 'K3'])
left.join(right)
A B C D
K0 A0 B0 C0 D0
K1 A1 B1 NaN NaN
K2 A2 B2 C2 D2