How to JOIN and MERGE Pandas DataFrame?
0

How to JOIN and MERGE Pandas DataFrame?

This recipe helps you JOIN and MERGE Pandas DataFrame
In [1]:
## How to JOIN and MERGE Pandas DataFrame
def Kickstarter_Example_91():
    print()
    print(format('How to JOIN and MERGE Pandas DataFrame','*^82'))

    import warnings
    warnings.filterwarnings("ignore")

    # load libraries
    import pandas as pd

    # Create a dataframe
    raw_data = {'subject_id': ['1', '2', '3', '4', '5'],
                'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'],
                'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches']}
    df_a = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])
    print(); print(df_a)

    # Create a second dataframe
    raw_data = {'subject_id': ['4', '5', '6', '7', '8'],
                'first_name': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'],
                'last_name': ['Bonder', 'Black', 'Balwner', 'Brice', 'Btisan']}
    df_b = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])
    print(); print(df_b)

    # Create a third dataframe
    raw_data = {'subject_id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],
                'test_id': [51, 15, 15, 61, 16, 14, 15, 1, 61, 16]}
    df_n = pd.DataFrame(raw_data, columns = ['subject_id','test_id'])
    print(); print(df_n)

    # Join the two dataframes along rows
    df_new = pd.concat([df_a, df_b])
    print(); print(df_new)

    # Join the two dataframes along columns
    df = pd.concat([df_a, df_b], axis=1)
    print(); print(df)

    # Merge two dataframes along the subject_id value
    df = pd.merge(df_new, df_n, on='subject_id')
    print(); print(df)

    # Merge two dataframes with both the left and right dataframes using the subject_id key
    df = pd.merge(df_new, df_n, left_on='subject_id', right_on='subject_id')
    print(); print(df)

    # Merge with outer join
    df = pd.merge(df_a, df_b, on='subject_id', how='outer')
    print(); print(df)

    # Merge with inner join
    df = pd.merge(df_a, df_b, on='subject_id', how='inner')
    print(); print(df)

    # Merge with right join
    df = pd.merge(df_a, df_b, on='subject_id', how='right')
    print(); print(df)

    # Merge with left join
    df = pd.merge(df_a, df_b, on='subject_id', how='left')
    print(); print(df)

    # Merge while adding a suffix to duplicate column names
    df = pd.merge(df_a, df_b, on='subject_id', how='left', suffixes=('_left', '_right'))
    print(); print(df)

    # Merge based on indexes
    df = pd.merge(df_a, df_b, right_index=True, left_index=True)
    print(); print(df)

Kickstarter_Example_91()
**********************How to JOIN and MERGE Pandas DataFrame**********************

  subject_id first_name last_name
0          1       Alex  Anderson
1          2        Amy  Ackerman
2          3      Allen       Ali
3          4      Alice      Aoni
4          5     Ayoung   Atiches

  subject_id first_name last_name
0          4      Billy    Bonder
1          5      Brian     Black
2          6       Bran   Balwner
3          7      Bryce     Brice
4          8      Betty    Btisan

  subject_id  test_id
0          1       51
1          2       15
2          3       15
3          4       61
4          5       16
5          7       14
6          8       15
7          9        1
8         10       61
9         11       16

  subject_id first_name last_name
0          1       Alex  Anderson
1          2        Amy  Ackerman
2          3      Allen       Ali
3          4      Alice      Aoni
4          5     Ayoung   Atiches
0          4      Billy    Bonder
1          5      Brian     Black
2          6       Bran   Balwner
3          7      Bryce     Brice
4          8      Betty    Btisan

  subject_id first_name last_name subject_id first_name last_name
0          1       Alex  Anderson          4      Billy    Bonder
1          2        Amy  Ackerman          5      Brian     Black
2          3      Allen       Ali          6       Bran   Balwner
3          4      Alice      Aoni          7      Bryce     Brice
4          5     Ayoung   Atiches          8      Betty    Btisan

  subject_id first_name last_name  test_id
0          1       Alex  Anderson       51
1          2        Amy  Ackerman       15
2          3      Allen       Ali       15
3          4      Alice      Aoni       61
4          4      Billy    Bonder       61
5          5     Ayoung   Atiches       16
6          5      Brian     Black       16
7          7      Bryce     Brice       14
8          8      Betty    Btisan       15

  subject_id first_name last_name  test_id
0          1       Alex  Anderson       51
1          2        Amy  Ackerman       15
2          3      Allen       Ali       15
3          4      Alice      Aoni       61
4          4      Billy    Bonder       61
5          5     Ayoung   Atiches       16
6          5      Brian     Black       16
7          7      Bryce     Brice       14
8          8      Betty    Btisan       15

  subject_id first_name_x last_name_x first_name_y last_name_y
0          1         Alex    Anderson          NaN         NaN
1          2          Amy    Ackerman          NaN         NaN
2          3        Allen         Ali          NaN         NaN
3          4        Alice        Aoni        Billy      Bonder
4          5       Ayoung     Atiches        Brian       Black
5          6          NaN         NaN         Bran     Balwner
6          7          NaN         NaN        Bryce       Brice
7          8          NaN         NaN        Betty      Btisan

  subject_id first_name_x last_name_x first_name_y last_name_y
0          4        Alice        Aoni        Billy      Bonder
1          5       Ayoung     Atiches        Brian       Black

  subject_id first_name_x last_name_x first_name_y last_name_y
0          4        Alice        Aoni        Billy      Bonder
1          5       Ayoung     Atiches        Brian       Black
2          6          NaN         NaN         Bran     Balwner
3          7          NaN         NaN        Bryce       Brice
4          8          NaN         NaN        Betty      Btisan

  subject_id first_name_x last_name_x first_name_y last_name_y
0          1         Alex    Anderson          NaN         NaN
1          2          Amy    Ackerman          NaN         NaN
2          3        Allen         Ali          NaN         NaN
3          4        Alice        Aoni        Billy      Bonder
4          5       Ayoung     Atiches        Brian       Black

  subject_id first_name_left last_name_left first_name_right last_name_right
0          1            Alex       Anderson              NaN             NaN
1          2             Amy       Ackerman              NaN             NaN
2          3           Allen            Ali              NaN             NaN
3          4           Alice           Aoni            Billy          Bonder
4          5          Ayoung        Atiches            Brian           Black

  subject_id_x first_name_x last_name_x subject_id_y first_name_y last_name_y
0            1         Alex    Anderson            4        Billy      Bonder
1            2          Amy    Ackerman            5        Brian       Black
2            3        Allen         Ali            6         Bran     Balwner
3            4        Alice        Aoni            7        Bryce       Brice
4            5       Ayoung     Atiches            8        Betty      Btisan