2.1.4.6.Merging joining and Concatenating

Concatenation

• 注意: Dataframe的維度必須相同

pd.concate([df1, df2, df3])

pd.concate([df1, df2, df3], axis = 1)

Merge

• 只能做橫向合併

• 預設的merge的方式為inner

  • Inner join: 找出table間有共同key值的資料

    pd.merge(left, right, how = 'inner', on = 'key')

  • 指定多個key進行inner join

    pd.merge(left, right, on = ['key1', 'key2'])

  • right join: 以right的key為主, 加入left

    pd.merge(left, right, how = 'right', on = ['key1', 'key2'])

  • left join: 以left的key為主, 加入right

    pd.merge(left, right, how = 'left', on = ['key1', 'key2'])

  • outer join: left與right的left join table跟left與right的right join table 做union

    pd.merge(left, right, how = 'outer', on = ['key1', 'key2'])

• 如果除了key以外有其他的column也是同名的, 將會採用[列名_表名]作為新的column值

left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'C': ['A0', 'A1', 'A2', 'A3'],
                     'D': ['B0', 'B1', 'B2', 'B3']})

right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                          'C': ['C0', 'C1', 'C2', 'C3'],
                          'D': ['D0', 'D1', 'D2', 'D3']}) 

pd.merge(left,right,how='inner',on='key')
OUT:     
       C_x    D_x    key    C_y    D_y
0    A0    B0    K0    C0    D0
1    A1    B1    K1    C1    D1
2    A2    B2    K2    C2    D2
3    A3    B3    K3    C3    D3

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, how='outer')