Recipe: How to JOIN and MERGE Pandas DataFrame?
DATA MUNGING PYTHON PANDAS DATAFRAME PANDAS CHEATSHEET PANDAS DATAFRAME TUTORIAL

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


Stuck at work?
Can't find the recipe you are looking for. Let us know and we will find an expert to create the recipe for you. Click here
Companies using this Recipe
3 developers from Accenture
2 developers from Infosys
2 developers from Tata Consultancy Services
1 developer from Birlasoft
1 developer from Ernst & Young
1 developer from ICU Medical
1 developer from LTI
1 developer from Scotiabank
1 developer from YASH Technologies
1 developer from C-DAC