【python数据分析（12）】Pandas实现对Excel列表数据整合（merge纵向合并、concat横向连接）

1. 背景

在进行表格操作的时候，经常需要将两个excel表格数据进行横向合并，或者对原有的数据进行纵向扩充，这时候，就可以使用Pandas里面的 merge 纵向合并和 concat 横向连接功能了，如下：

2. 纵向合并

pd.merge(left, right, how=‘inner’, on=None, left_on=None, right_on=None,left_index=False, right_index=False, sort=True,
suffixes=(’_x’, ‘_y’), copy=True)

left：第一个df
right：第二个df
on：参考键（left_on左边df的参考键、right_on右边df的参考键）
left_index、right_index：左右的参考索引
how：合并方式
sort：排序参数
suffixes=('_x', '_y') 相同列标题时，主动添加后缀部分

样本数据生成：

df1 = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
df2 = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                      'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']})
df3 = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                    'key2': ['K0', 'K1', 'K0', 'K1'],
                    'A': ['A0', 'A1', 'A2', 'A3'],
                    'B': ['B0', 'B1', 'B2', 'B3']})
df4 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                    'key2': ['K0', 'K0', 'K0', 'K0'],
                    'C': ['C0', 'C1', 'C2', 'C3'],
                    'D': ['D0', 'D1', 'D2', 'D3']})

2.1 按照相同的列标题进行数据合并

print(df1)
print(df2)
print(pd.merge(df1, df2, on='key'))

–> 输出的结果为：（最后的输出结果是按住key字段数据进行纵向合并）

  key   A   B
0  K0  A0  B0
1  K1  A1  B1
2  K2  A2  B2
3  K3  A3  B3

  key   C   D
0  K0  C0  D0
1  K1  C1  D1
2  K2  C2  D2
3  K3  C3  D3

  key   A   B   C   D
0  K0  A0  B0  C0  D0
1  K1  A1  B1  C1  D1
2  K2  A2  B2  C2  D2
3  K3  A3  B3  C3  D3

2.2 按照多列标题进行数据合并

print(df3)
print(df4)
print(pd.merge(df3, df4, on=['key1','key2']))

–> 输出的结果为：（按照两列相同的数据为依据进行纵向数据合并，不同的行数据会被舍弃）

  key1 key2   A   B
0   K0   K0  A0  B0
1   K0   K1  A1  B1
2   K1   K0  A2  B2
3   K2   K1  A3  B3

  key1 key2   C   D
0   K0   K0  C0  D0
1   K1   K0  C1  D1
2   K1   K0  C2  D2
3   K2   K0  C3  D3

  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.3 合并方式

根据前面的列出的参数可知参数how：

1）合并方式默认是inner，也就是取交集的意思；

2）此外还有outer取并集，数据缺失范围NaN；

3） how = 'left'，按照左侧的df（下面的df3）为参考合并，数据缺失范围NaN

4） how = 'right'，按照右侧的df（下面的df4）为参考合并，数据缺失范围NaN

print(pd.merge(df3, df4,on=['key1','key2'], how = 'inner'))  

print(pd.merge(df3, df4, on=['key1','key2'], how = 'outer'))  

print(pd.merge(df3, df4, on=['key1','key2'], how = 'left'))  

print(pd.merge(df3, df4, on=['key1','key2'], how = 'right'))  

–> 输出的结果为：

  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

  key1 key2    A    B    C    D
0   K0   K0   A0   B0   C0   D0
1   K0   K1   A1   B1  NaN  NaN
2   K1   K0   A2   B2   C1   D1
3   K1   K0   A2   B2   C2   D2
4   K2   K1   A3   B3  NaN  NaN
5   K2   K0  NaN  NaN   C3   D3

  key1 key2   A   B    C    D
0   K0   K0  A0  B0   C0   D0
1   K0   K1  A1  B1  NaN  NaN
2   K1   K0  A2  B2   C1   D1
3   K1   K0  A2  B2   C2   D2
4   K2   K1  A3  B3  NaN  NaN

  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
3   K2   K0  NaN  NaN  C3  D3

2.4 按照行标题和索引标签进行数据合并

left_on, right_on, left_index, right_index → 有时候合并的两组的依据数据内容是一样，但是标题（可以是行标题，也可以是索引标签的name）不一样时候，可以单独设置左键与右键

1） 都是以行标题为键（left_on、right_on）

df1 = pd.DataFrame({'lkey':list('bbacaab'),
                   'data1':range(7)})
df2 = pd.DataFrame({'rkey':list('abd'),
                   'date2':range(3)})
print(df1)
print(df2)                   
print(pd.merge(df1, df2, left_on='lkey', right_on='rkey'))

–> 输出的结果为：（依据的键的内容也会在数组列表中，可扩展）

  lkey  data1
0    b      0
1    b      1
2    a      2
3    c      3
4    a      4
5    a      5
6    b      6

  rkey  date2
0    a      0
1    b      1
2    d      2

  lkey  data1 rkey  date2
0    b      0    b      1
1    b      1    b      1
2    b      6    b      1
3    a      2    a      0
4    a      4    a      0
5    a      5    a      0

2） 一个以行标题，一个以索引标签（left_on、right_index）

df1 = pd.DataFrame({'key':list('abcdfeg'),
                   'data1':range(7)})
df2 = pd.DataFrame({'date2':range(100,105)},
                  index = list('abcde'))
print(df1)
print(df2)
print(pd.merge(df1, df2, left_on='key', right_index=True))

–> 输出的结果为：（会选取相同的部分，不符合的会被舍弃）

  key  data1
0   a      0
1   b      1
2   c      2
3   d      3
4   f      4
5   e      5
6   g      6

   date2
a    100
b    101
c    102
d    103
e    104

  key  data1  date2
0   a      0    100
1   b      1    101
2   c      2    102
3   d      3    103
5   e      5    104

所以left_on, right_on, left_index, right_index可以相互组合（四种搭配）：

left_on + right_on, left_on + right_index, left_index + right_on, left_index + right_index

3） sort参数，这里可以使用，但是建议使用sort_values，sort_index，比较精细化

4）suffixes=('_x', '_y')默认当数据的标题重名时候是以_x,和_y作为后缀的，可以进行修改

df1 = pd.DataFrame({'key':list('adcbfeg'),
                   'data1':[1,3,2,4,5,9,7]})
df2 = pd.DataFrame({'data1':range(100,105)},
                  index = list('abcde'))
print(df1)
print(df2)
print(pd.merge(df1, df2, left_on='key', sort=True,suffixes=('_1', '_2'), right_index=True))

–> 输出的结果为：

  key  data1
0   a      1
1   d      3
2   c      2
3   b      4
4   f      5
5   e      9
6   g      7

   data1
a    100
b    101
c    102
d    103
e    104

  key  data1_1  data1_2
0   a        1      100
3   b        4      101
2   c        2      102
1   d        3      103
5   e        9      104

3. 横向连接

pd.concat(objs, axis=0, join=‘outer’, join_axes=None, ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, copy=True)

括号内为默认参数

s1 = pd.Series([1,2,3])
s2 = pd.Series([2,3,4])
s3 = pd.Series([1,2,3],index = ['a','c','h'])
s4 = pd.Series([2,3,4],index = ['b','e','d'])
print(pd.concat([s1,s2]))
print(pd.concat([s3,s4]).sort_index())

–> 输出的结果为：

0    1
1    2
2    3
0    2
1    3
2    4
dtype: int64

a    1
b    2
c    2
d    4
e    3
h    3
dtype: int64

3.1 默认axis=0，行+行，当axis=1,列+列

print(pd.concat([s3,s4], axis=1))

–> 输出的结果为：

     0    1
a  1.0  NaN
b  NaN  2.0
c  2.0  NaN
d  NaN  4.0
e  NaN  3.0
h  3.0  NaN

3.2 连接方式：join，join_axes

join：{‘inner’，‘outer’}，默认为“outer”。如何处理其他轴上的索引。outer为联合和inner为交集。

join_axes：指定联合的index

s5 = pd.Series([1,2,3],index = ['a','b','c'])
s6 = pd.Series([2,3,4],index = ['b','c','d'])
print(pd.concat([s5,s6], axis= 1))
print(pd.concat([s5,s6], axis= 1, join='inner'))
print(pd.concat([s5,s6], axis= 1, join_axes=[['a','b','d']]))

–> 输出的结果为：

     0    1
a  1.0  NaN
b  2.0  2.0
c  3.0  3.0
d  NaN  4.0

   0  1
b  2  2
c  3  3

    0    1
a  1.0  NaN
b  2.0  2.0
d  NaN  4.0

3.3 多层次索引标签（覆盖列名）

1） keys：序列，默认值无。使用传递的键作为最外层构建层次索引

sre = pd.concat([s5,s6], keys = ['one','two'])
print(sre,type(sre))
print(sre.index)

–> 输出的结果为：

one  a    1
     b    2
     c    3
two  b    2
     c    3
     d    4
dtype: int64 <class 'pandas.core.series.Series'>
MultiIndex([('one', 'a'),
            ('one', 'b'),
            ('one', 'c'),
            ('two', 'b'),
            ('two', 'c'),
            ('two', 'd')],
           )

2） axis = 1, 覆盖列名（相当于指定新的列标题）

sre = pd.concat([s5,s6], axis=1, keys = ['one','two'])
print(sre,type(sre))

–> 输出的结果为：

   one  two
a  1.0  NaN
b  2.0  2.0
c  3.0  3.0
d  NaN  4.0 <class 'pandas.core.frame.DataFrame'>

★★★★★ 3.4 数据修补

1）pd.combine_first() 进行数据的修补，是将括号里面的数据df2补充到前面的数据df1中

df1 = pd.DataFrame([[np.nan, 3., 5.], [-4.6, np.nan, np.nan],[np.nan, 7., np.nan]])
df2 = pd.DataFrame([[-42.6, np.nan, -8.2], [-5., 1.6, 4]],index=[1, 2])
print(df1)
print(df2)
print(df1.combine_first(df2))

–> 输出的结果为：

     0    1    2
0  NaN  3.0  5.0
1 -4.6  NaN  NaN
2  NaN  7.0  NaN

      0    1    2
1 -42.6  NaN -8.2
2  -5.0  1.6  4.0

     0    1    2
0  NaN  3.0  5.0
1 -4.6  NaN -8.2
2 -5.0  7.0  4.0

2） update() 直接df2覆盖df1，相同index位置，更新df1中的数据

df1.update(df2)
print(df1)

–> 输出的结果为：（注意对比和上面的结果）

      0    1    2
0   NaN  3.0  5.0
1 -42.6  NaN -8.2
2  -5.0  1.6  4.0