DataFrame的级联and合并操作

1064 views 

import pandas as pd
import numpy as np

级联操作

  • pd.concat, pd.append

pandas使用pd.concat函数，与np.concatenate函数类似，只是多了一些参数：

objs
axis=0
keys
join='outer' / 'inner':表示的是级联的方式，outer会将所有的项进行级联（忽略匹配和不匹配），而inner只会将匹配的项级联到一起，不匹配的不级联
ignore_index=False
  • 匹配级联
df1 = pd.DataFrame(data=np.random.randint(0,100,size=(5,3)),columns=['A','B','C'])
df2 = pd.DataFrame(data=np.random.randint(0,100,size=(5,3)),columns=['A','D','C'])

pd.concat((df1,df2),axis=1) #行列索引都一致的级联叫做匹配级联

    A   B   C   A   D   C
0   42  7   83  53  6   51
1   5   59  43  96  63  24
2   52  56  0   2   21  96
3   42  52  44  24  57  31
4   53  90  31  45  19  78
  • 不匹配级联
    • 不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致，横向级联时行索引不一致
    • 有2种连接方式：
      • 外连接：补NaN（默认模式）
      • 内连接：只连接匹配的项
pd.concat((df1,df2),axis=0)
A B C D
0 49 43.0 80 NaN
1 59 82.0 36 NaN
2 97 49.0 99 NaN
3 99 28.0 35 NaN
4 59 76.0 87 NaN
0 23 NaN 6 90.0
1 15 NaN 81 41.0
2 98 NaN 55 54.0
3 90 NaN 45 0.0
4 22 NaN 34 48.0 
pd.concat((df1,df2),axis=0,join='inner') #inner直把可以级联的级联不能级联不处理
A C
0 49 80
1 59 36
2 97 99
3 99 35
4 59 87
0 23 6
1 15 81
2 98 55
3 90 45
4 22 34

  • 如果想要保留数据的完整性必须使用outer（外连接）

  • append函数的使用

df1.append(df1)
A B C
0 49 43 80
1 59 82 36
2 97 49 99
3 99 28 35
4 59 76 87
0 49 43 80
1 59 82 36
2 97 49 99
3 99 28 35
4 59 76 87

合并操作

  • merge与concat的区别在于，merge需要依据某一共同列来进行合并

  • 使用pd.merge()合并时，会自动根据两者相同column名称的那一列，作为key来进行合并。

  • 注意每一列元素的顺序不要求一致

一对一合并

from pandas import DataFrame

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,on='employee')
employee group hire_date
0 Bob Accounting 2008
1 Jake Engineering 2012
2 Lisa Engineering 2004

一对多合并

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)#on如果不写，默认情况下使用两表中公有的列作为合并条件
employee group hire_date supervisor
0 Lisa Accounting 2004 Carly
1 Jake Engineering 2016 Guido
2 Jake Engineering 2016 Steve

多对多合并

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='right')
employee group supervisor
0 Jake Engineering Carly
1 Lisa Engineering Carly
2 Jake Engineering Guido
3 Lisa Engineering Guido
4 NaN HR Steve 
pd.merge(df1,df5,how='right')
employee group supervisor
0 Jake Engineering Carly
1 Lisa Engineering Carly
2 Jake Engineering Guido
3 Lisa Engineering Guido
4 NaN HR Steve

key的规范化

  • 当两张表没有可进行连接的列时，可使用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
name hire_dates
0 Lisa 1998
1 Bobs 2016
2 Bill 2007 
pd.merge(df1,df5,left_on='employee',right_on='name')
employee group hire_date name hire_dates
0 Bobs Accounting 1998 Bobs 2016
1 Bill Marketing 2018 Bill 2007

内合并与外合并:outer取并集 inner取交集

df6 = DataFrame({'name':['Peter','Paul','Mary'],
               'food':['fish','beans','bread']}
               )
df7 = DataFrame({'name':['Mary','Joseph'],
                'drink':['wine','beer']})

pd.merge(df6,df7,how='outer')
name food drink
0 Peter fish NaN
1 Paul beans NaN
2 Mary bread wine
3 Joseph NaN beer 
df6 = DataFrame({'name':['Peter','Paul','Mary'],
               'food':['fish','beans','bread']}
               )
df7 = DataFrame({'name':['Mary','Joseph'],
                'drink':['wine','beer']})
pd.merge(df6,df7,how='inner')
name food drink
0 Mary bread wine