数据分析 四 pandas的拼接操作
pandas的拼接操作
pandas的拼接分为两种：
- 级联：pd.concat, pd.append
- 合并：pd.merge, pd.join
1. 使用pd.concat()级联
pandas使用pd.concat函数，与np.concatenate函数类似，只是多了一些参数：
objs
axis=0
keys
join='outer' / 'inner':表示的是级联的方式，outer会将所有的项进行级联（忽略匹配和不匹配），而inner只会将匹配的项级联到一起，不匹配的不级联
ignore_index=False1)匹配级联
import pandas as pd from pandas import Series,DataFrame import numpy as np
df1 = DataFrame(data=np.random.randint(0,100,size=(3,4)),index=['a','b','c']) df2 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['a','d','c']) pd.concat((df1,df1),axis=0) ============ 0 1 2 a 5 53 94 b 5 26 13 c 65 60 90 a 5 53 94 b 5 26 13 c 65 60 90
2) 不匹配级联
不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致，横向级联时行索引不一致
有2种连接方式：
- 外连接：补NaN（默认模式）
- 内连接：只连接匹配的项
pd.concat((df1,df2),axis=0,join='inner') # pd.concat((df1,df2),axis=1)
0 1 2 a 15 46 58 b 56 28 94 c 26 49 98 a 43 37 93 d 63 91 82 c 40 34 16
2. 使用pd.merge()合并
merge与concat的区别在于，merge需要依据某一共同的列来进行合并
使用pd.merge()合并时，会自动根据两者相同column名称的那一列，作为key来进行合并。
注意每一列元素的顺序不要求一致
参数：
- how：out取并集 inner取交集
- on：当有多列相同的时候，可以使用on来指定使用那一列进行合并，on的值为一个列表
1) 一对一合并
df1 = DataFrame({'employee':['Bob','Jake','Lisa'],
'group':['Accounting','Engineering','Engineering'],
})
df1
=================
employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
df2 = DataFrame({'employee':['Lisa','Bob','Jake'],
'hire_date':[2004,2008,2012],
})
df2
===============
employee hire_date
0 Lisa 2004
1 Bob 2008
2 Jake 2012
pd.merge(df1,df2)
pd.merge(df1,df2) ==================== employee group hire_date 0 Bob Accounting 2008 1 Jake Engineering 2012 2 Lisa Engineering 2004
2) 多对一合并
df3 = DataFrame({ 'employee':['Lisa','Jake'], 'group':['Accounting','Engineering'], 'hire_date':[2004,2016]}) df3
employee group hire_date
0 Lisa Accounting 2004
1 Jake Engineering 2016
df4 = DataFrame({'group':['Accounting','Engineering','Engineering'],
'supervisor':['Carly','Guido','Steve']
})
df4
===========
group supervisor
0 Accounting Carly
1 Engineering Guido
2 Engineering Steve
pd.merge(df3,df4) ===== employee group hire_date supervisor 0 Lisa Accounting 2004 Carly 1 Jake Engineering 2016 Guido 2 Jake Engineering 2016 Steve
3) 多对多合并
df1 = DataFrame({'employee':['Bob','Jake','Lisa'],
'group':['Accounting','Engineering','Engineering']})
df1
employee group 0 Bob Accounting 1 Jake Engineering 2 Lisa Engineering
df5 = DataFrame({'group':['Engineering','Engineering','HR'],
'supervisor':['Carly','Guido','Steve']
})
df5
group supervisor 0 Engineering Carly 1 Engineering Guido 2 HR Steve
pd.merge(df1,df5,how='outer') ======= employee group supervisor 0 Bob Accounting NaN 1 Jake Engineering Carly 2 Jake Engineering Guido 3 Lisa Engineering Carly 4 Lisa Engineering Guido 5 NaN HR Steve
4) key的规范化
- 当列冲突时，即有多个列名称相同时，需要使用on=来指定哪一个列作为key，配合suffixes指定冲突列名
df1 = DataFrame({'employee':['Jack',"Summer","Steve"],
'group':['Accounting','Finance','Marketing']})
df1
===============
employee group
0 Jack Accounting
1 Summer Finance
2 Steve Marketing
f2 = DataFrame({'employee':['Jack','Bob',"Jake"],
'hire_date':[2003,2009,2012],
'group':['Accounting','sell','ceo']})
df2
================
employee group hire_date
0 Jack Accounting 2003
1 Bob sell 2009
2 Jake ceo 2012
pd.merge(df1,df2,on='group',how='outer') ============== employee_x group employee_y hire_date 0 Jack Accounting Jack 2003.0 1 Summer Finance NaN NaN 2 Steve Marketing NaN NaN 3 NaN sell Bob 2009.0 4 NaN ceo Jake 2012.0
当两张表没有可进行连接的列时，可使用left_on和right_on手动指定merge中左右两边的哪一列列作为连接的列
df1 = DataFrame({'employee':['Bobs','Linda','Bill'],
'group':['Accounting','Product','Marketing'],
'hire_date':[1998,2017,2018]})
df1
==============
employee group hire_date
0 Bobs Accounting 1998
1 Linda Product 2017
2 Bill Marketing 2018
df5 = DataFrame({'name':['Lisa','Bobs','Bill'],
'hire_dates':[1998,2016,2007]})
df5
=============
hire_dates name
0 1998 Lisa
1 2016 Bobs
2 2007 Bill
pd.merge(df1,df5,left_on='employee',right_on='name',how='outer') ================== employee group hire_date hire_dates name 0 Bobs Accounting 1998.0 2016.0 Bobs 1 Linda Product 2017.0 NaN NaN 2 Bill Marketing 2018.0 2007.0 Bill 3 NaN NaN NaN 1998.0 Lisa
5) 内合并与外合并:out取并集 inner取交集
- 内合并：只保留两者都有的key（默认模式）
df6 = DataFrame({'name':['Peter','Paul','Mary'],
'food':['fish','beans','bread']}
)
df7 = DataFrame({'name':['Mary','Joseph'],
'drink':['wine','beer']})
外合并 how='outer'：补NaN
df6 = DataFrame({'name':['Peter','Paul','Mary'],
'food':['fish','beans','bread']}
)
df7 = DataFrame({'name':['Mary','Joseph'],
'drink':['wine','beer']})
