pandas 7 合并 merge 水平合并，数据会变宽

• pd.merge( df1, df2, on=['key1', 'key2'], left_index=True, right_index=True, how=['left', 'right', 'outer', 'inner'], indicator='indicator_column', suffixes=['_boy', '_girl'] )

from __future__ import print_function
import pandas as pd

merging two df by key/keys， on='key'. (may be used in database)

# merging two df by key/keys. (may be used in database)
# simple example
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                       'A': ['A0', 'A1', 'A2', 'A3'],
                       'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                        'C': ['C0', 'C1', 'C2', 'C3'],
                        'D': ['D0', 'D1', 'D2', 'D3']})
print(left)
print(right)
res = pd.merge(left, right, on='key')  # 基于列标签为‘key’合并
print(res)

>   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

consider two keys， on=['key1', 'key2']

# consider two keys
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']})
print(left)
print(right)

>   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

res = pd.merge(left, right, on=['key1', 'key2'], how='inner')  # default for how='inner'
print(res)  # 求交集
res = pd.merge(left, right, on=['key1', 'key2'], how='outer')  # default for how='inner'
print(res)  # 求并集

>   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

how = ['left', 'right', 'outer', 'inner']

# how = ['left', 'right', 'outer', 'inner']
res = pd.merge(left, right, on=['key1', 'key2'], how='right')
print(res)  # 以右边的数据为标准，来合并

>   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

显示数据的来源，多_merge这一列，默认是false不显示:indicator=True 或 indicator='indicator_column' 自定义indicator列名称

# indicator
df1 = pd.DataFrame({'col1':[0,1], 'col_left':['a','b']})
df2 = pd.DataFrame({'col1':[1,2,2],'col_right':[2,2,2]})

print(df1)
print(df2)
res = pd.merge(df1, df2, on='col1', how='outer', indicator=True)
print(res)

>    col1 col_left
> 0     0        a
> 1     1        b
 
>    col1  col_right
> 0     1          2
> 1     2          2
> 2     2          2

>    col1 col_left  col_right      _merge
> 0     0        a        NaN   left_only
> 1     1        b        2.0        both
> 2     2      NaN        2.0  right_only
> 3     2      NaN        2.0  right_only

# give the indicator a custom name
res = pd.merge(df1, df2, on='col1', how='outer', indicator='indicator_column')
print(res)  # 设置这列的标题为'indicator_column'

>    col1 col_left  col_right indicator_column
> 0     0        a        NaN        left_only
> 1     1        b        2.0             both
> 2     2      NaN        2.0       right_only
> 3     2      NaN        2.0       right_only

handle overlapping 信息重叠： suffixes=['_boy', '_girl']

# handle overlapping
boys = pd.DataFrame({'k': ['K0', 'K1', 'K2'], 'age': [1, 2, 3]})
girls = pd.DataFrame({'k': ['K0', 'K0', 'K3'], 'age': [4, 5, 6]})
res = pd.merge(boys, girls, on='k', suffixes=['_boy', '_girl'], how='inner')

print(boys)
print(girls)
print(res)

>     k  age
> 0  K0    1
> 1  K1    2
> 2  K2    3

>     k  age
> 0  K0    4
> 1  K0    5
> 2  K3    6

>     k  age_boy  age_girl
> 0  K0        1         4
> 1  K0        1         5

res = pd.merge(boys, girls, on='k', suffixes=['_boy', '_girl'], how='outer')
print(res)  # 这里的K0，K1理解成姓名，同一个姓名对应了不同的年龄，说明信息重叠了。

>     k  age_boy  age_girl
> 0  K0      1.0       4.0
> 1  K0      1.0       5.0
> 2  K1      2.0       NaN
> 3  K2      3.0       NaN
> 4  K3      NaN       6.0

merged by index： left_index=True, right_index=True

# merged by index
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'])

print(left)
print(right)
 
>      A   B
> K0  A0  B0
> K1  A1  B1
> K2  A2  B2
 
>      C   D
> K0  C0  D0
> K2  C2  D2
> K3  C3  D3

# left_index and right_index  默认是False 
res = pd.merge(left, right, left_index=True, right_index=True, how='outer')
print(res)  # 并
res = pd.merge(left, right, left_index=True, right_index=True, how='inner')
print(res)  # 交

>       A    B    C    D
> K0   A0   B0   C0   D0
> K1   A1   B1  NaN  NaN
> K2   A2   B2   C2   D2
> K3  NaN  NaN   C3   D3

>      A   B   C   D
> K0  A0  B0  C0  D0
> K2  A2  B2  C2  D2

join：join function in pandas is similar with merge. If know merge, you will understand join

END