Pandas合并

作者: 闫_锋 | 来源:发表于2018-07-25 11:47 被阅读28次

    转载:
    https://morvanzhou.github.io/tutorials/data-manipulation/np-pd/3-6-pd-concat/

    import pandas as pd
    import numpy as np
    
    #定义资料集
    df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'])
    df2 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])
    df3 = pd.DataFrame(np.ones((3,4))*2, columns=['a','b','c','d'])
    
    #concat纵向合并
    res = pd.concat([df1, df2, df3], axis=0)
    
    #打印结果
    print(res)
    #     a    b    c    d
    # 0  0.0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0  0.0
    # 2  0.0  0.0  0.0  0.0
    # 0  1.0  1.0  1.0  1.0
    # 1  1.0  1.0  1.0  1.0
    # 2  1.0  1.0  1.0  1.0
    # 0  2.0  2.0  2.0  2.0
    # 1  2.0  2.0  2.0  2.0
    # 2  2.0  2.0  2.0  2.0
    
    #承上一个例子,并将index_ignore设定为True
    res = pd.concat([df1, df2, df3], axis=0, ignore_index=True)
    
    #打印结果
    print(res)
    #     a    b    c    d
    # 0  0.0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0  0.0
    # 2  0.0  0.0  0.0  0.0
    # 3  1.0  1.0  1.0  1.0
    # 4  1.0  1.0  1.0  1.0
    # 5  1.0  1.0  1.0  1.0
    # 6  2.0  2.0  2.0  2.0
    # 7  2.0  2.0  2.0  2.0
    # 8  2.0  2.0  2.0  2.0
    

    join(合并方式)

    import pandas as pd
    import numpy as np
    
    #定义资料集
    df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'], index=[1,2,3])
    df2 = pd.DataFrame(np.ones((3,4))*1, columns=['b','c','d','e'], index=[2,3,4])
    
    #纵向"外"合并df1与df2
    res = pd.concat([df1, df2], axis=0, join='outer')
    
    print(res)
    #     a    b    c    d    e
    # 1  0.0  0.0  0.0  0.0  NaN
    # 2  0.0  0.0  0.0  0.0  NaN
    # 3  0.0  0.0  0.0  0.0  NaN
    # 2  NaN  1.0  1.0  1.0  1.0
    # 3  NaN  1.0  1.0  1.0  1.0
    # 4  NaN  1.0  1.0  1.0  1.0
    
    
    #承上一个例子
    #纵向"内"合并df1与df2
    res = pd.concat([df1, df2], axis=0, join='inner')
    
    #打印结果
    print(res)
    #     b    c    d
    # 1  0.0  0.0  0.0
    # 2  0.0  0.0  0.0
    # 3  0.0  0.0  0.0
    # 2  1.0  1.0  1.0
    # 3  1.0  1.0  1.0
    # 4  1.0  1.0  1.0
    
    #重置index并打印结果
    res = pd.concat([df1, df2], axis=0, join='inner', ignore_index=True)
    print(res)
    #     b    c    d
    # 0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0
    # 2  0.0  0.0  0.0
    # 3  1.0  1.0  1.0
    # 4  1.0  1.0  1.0
    # 5  1.0  1.0  1.0
    
    

    join_axes (依照 axes 合并)

    import pandas as pd
    import numpy as np
    
    #定义资料集
    df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'], index=[1,2,3])
    df2 = pd.DataFrame(np.ones((3,4))*1, columns=['b','c','d','e'], index=[2,3,4])
    
    #依照`df1.index`进行横向合并
    res = pd.concat([df1, df2], axis=1, join_axes=[df1.index])
    
    #打印结果
    print(res)
    #     a    b    c    d    b    c    d    e
    # 1  0.0  0.0  0.0  0.0  NaN  NaN  NaN  NaN
    # 2  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0
    # 3  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0
    
    #移除join_axes,并打印结果
    res = pd.concat([df1, df2], axis=1)
    print(res)
    #     a    b    c    d    b    c    d    e
    # 1  0.0  0.0  0.0  0.0  NaN  NaN  NaN  NaN
    # 2  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0
    # 3  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0
    # 4  NaN  NaN  NaN  NaN  1.0  1.0  1.0  1.0
    

    append (添加数据)

    import pandas as pd
    import numpy as np
    
    #定义资料集
    df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'])
    df2 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])
    df3 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])
    s1 = pd.Series([1,2,3,4], index=['a','b','c','d'])
    
    #将df2合并到df1的下面,以及重置index,并打印出结果
    res = df1.append(df2, ignore_index=True)
    print(res)
    #     a    b    c    d
    # 0  0.0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0  0.0
    # 2  0.0  0.0  0.0  0.0
    # 3  1.0  1.0  1.0  1.0
    # 4  1.0  1.0  1.0  1.0
    # 5  1.0  1.0  1.0  1.0
    
    #合并多个df,将df2与df3合并至df1的下面,以及重置index,并打印出结果
    res = df1.append([df2, df3], ignore_index=True)
    print(res)
    #     a    b    c    d
    # 0  0.0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0  0.0
    # 2  0.0  0.0  0.0  0.0
    # 3  1.0  1.0  1.0  1.0
    # 4  1.0  1.0  1.0  1.0
    # 5  1.0  1.0  1.0  1.0
    # 6  1.0  1.0  1.0  1.0
    # 7  1.0  1.0  1.0  1.0
    # 8  1.0  1.0  1.0  1.0
    
    #合并series,将s1合并至df1,以及重置index,并打印出结果
    res = df1.append(s1, ignore_index=True)
    print(res)
    #     a    b    c    d
    # 0  0.0  0.0  0.0  0.0
    # 1  0.0  0.0  0.0  0.0
    # 2  0.0  0.0  0.0  0.0
    # 3  1.0  2.0  3.0  4.0
    

    Merge用于有key column的数据。

    import pandas as pd
    
    #定义资料集并打印出
    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)
    #    A   B key
    # 0  A0  B0  K0
    # 1  A1  B1  K1
    # 2  A2  B2  K2
    # 3  A3  B3  K3
    
    print(right)
    #    C   D key
    # 0  C0  D0  K0
    # 1  C1  D1  K1
    # 2  C2  D2  K2
    # 3  C3  D3  K3
    
    #依据key column合并,并打印出
    res = pd.merge(left, right, on='key')
    
    print(res)
         A   B key   C   D
    # 0  A0  B0  K0  C0  D0
    # 1  A1  B1  K1  C1  D1
    # 2  A2  B2  K2  C2  D2
    # 3  A3  B3  K3  C3  D3
    

    依据两组key合并

    import pandas as pd
    
    #定义资料集并打印出
    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)
    #    A   B key1 key2
    # 0  A0  B0   K0   K0
    # 1  A1  B1   K0   K1
    # 2  A2  B2   K1   K0
    # 3  A3  B3   K2   K1
    
    print(right)
    #    C   D key1 key2
    # 0  C0  D0   K0   K0
    # 1  C1  D1   K1   K0
    # 2  C2  D2   K1   K0
    # 3  C3  D3   K2   K0
    
    #依据key1与key2 columns进行合并,并打印出四种结果['left', 'right', 'outer', 'inner']
    res = pd.merge(left, right, on=['key1', 'key2'], how='inner')
    print(res)
    #    A   B key1 key2   C   D
    # 0  A0  B0   K0   K0  C0  D0
    # 1  A2  B2   K1   K0  C1  D1
    # 2  A2  B2   K1   K0  C2  D2
    
    res = pd.merge(left, right, on=['key1', 'key2'], how='outer')
    print(res)
    #     A    B key1 key2    C    D
    # 0   A0   B0   K0   K0   C0   D0
    # 1   A1   B1   K0   K1  NaN  NaN
    # 2   A2   B2   K1   K0   C1   D1
    # 3   A2   B2   K1   K0   C2   D2
    # 4   A3   B3   K2   K1  NaN  NaN
    # 5  NaN  NaN   K2   K0   C3   D3
    
    res = pd.merge(left, right, on=['key1', 'key2'], how='left')
    print(res)
    #    A   B key1 key2    C    D
    # 0  A0  B0   K0   K0   C0   D0
    # 1  A1  B1   K0   K1  NaN  NaN
    # 2  A2  B2   K1   K0   C1   D1
    # 3  A2  B2   K1   K0   C2   D2
    # 4  A3  B3   K2   K1  NaN  NaN
    
    res = pd.merge(left, right, on=['key1', 'key2'], how='right')
    print(res)
    #     A    B key1 key2   C   D
    # 0   A0   B0   K0   K0  C0  D0
    # 1   A2   B2   K1   K0  C1  D1
    # 2   A2   B2   K1   K0  C2  D2
    # 3  NaN  NaN   K2   K0  C3  D3
    

    Indicator

    import pandas as pd
    
    #定义资料集并打印出
    df1 = pd.DataFrame({'col1':[0,1], 'col_left':['a','b']})
    df2 = pd.DataFrame({'col1':[1,2,2],'col_right':[2,2,2]})
    
    print(df1)
    #   col1 col_left
    # 0     0        a
    # 1     1        b
    
    print(df2)
    #   col1  col_right
    # 0     1          2
    # 1     2          2
    # 2     2          2
    
    # 依据col1进行合并,并启用indicator=True,最后打印出
    res = pd.merge(df1, df2, on='col1', how='outer', indicator=True)
    print(res)
    #   col1 col_left  col_right      _merge
    # 0   0.0        a        NaN   left_only
    # 1   1.0        b        2.0        both
    # 2   2.0      NaN        2.0  right_only
    # 3   2.0      NaN        2.0  right_only
    
    # 自定indicator column的名称,并打印出
    res = pd.merge(df1, df2, on='col1', how='outer', indicator='indicator_column')
    print(res)
    #   col1 col_left  col_right indicator_column
    # 0   0.0        a        NaN        left_only
    # 1   1.0        b        2.0             both
    # 2   2.0      NaN        2.0       right_only
    # 3   2.0      NaN        2.0       right_only
    

    依据index合并

    import pandas as pd
    
    #定义资料集并打印出
    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)
    #     A   B
    # K0  A0  B0
    # K1  A1  B1
    # K2  A2  B2
    
    print(right)
    #     C   D
    # K0  C0  D0
    # K2  C2  D2
    # K3  C3  D3
    
    #依据左右资料集的index进行合并,how='outer',并打印出
    res = pd.merge(left, right, left_index=True, right_index=True, how='outer')
    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
    
    #依据左右资料集的index进行合并,how='inner',并打印出
    res = pd.merge(left, right, left_index=True, right_index=True, how='inner')
    print(res)
    #     A   B   C   D
    # K0  A0  B0  C0  D0
    # K2  A2  B2  C2  D2
    

    解决overlapping的问题

    import pandas as pd
    
    #定义资料集
    boys = pd.DataFrame({'k': ['K0', 'K1', 'K2'], 'age': [1, 2, 3]})
    girls = pd.DataFrame({'k': ['K0', 'K0', 'K3'], 'age': [4, 5, 6]})
    
    #使用suffixes解决overlapping的问题
    res = pd.merge(boys, girls, on='k', suffixes=['_boy', '_girl'], how='inner')
    print(res)
    #    age_boy   k  age_girl
    # 0        1  K0         4
    # 1        1  K0         5
    

    相关文章

      网友评论

        本文标题:Pandas合并

        本文链接:https://www.haomeiwen.com/subject/wokpmftx.html