Merging Pandas DataFrames Based on Multiple Columns

Step 1: Create Pandas DataFrames

# Create 1st DataFrame in Pandas

import pandas as pd

df1= pd.DataFrame({"State": ("TX", "NC", "OK", "NY", "TX"), 

                   "City": ("Austin", "Charlotte", "OK city", "New York", "San Marcos"),

                   "V1": (1, 2, 3, 4, 5),

                   "v2": (6, 7, 8, 9, 10),

                   "v3": (11, 12, 13, 14, 15)})

print(df1)

# OUTPUT OF df1 will be as follows:

   State     City    V1  v2  v3

0    TX      Austin   1   6  11

1    NC   Charlotte   2   7  12

2    OK     OK city   3   8  13

3    NY    New York   4   9  14

4    TX  San Marcos   5  10  15 

# Create 2nd DataFrame in Pandas

df2= pd.DataFrame({"State": ("NY", "TX", "NC", "TX",  "OK" , "SC"), 

                   "City": ("New York", "Austin", "Charlotte", "San Marcos", "OK city", "Columbia"),

                   "V4": (15, 16, 17, 18, 19, 20,),

                   "v5": (21,22,23,24,25, 26),

                   "v6": (27,28,29,30, 31, 32), 

                   "v7": (33,34,35, 36, 37, 38)})

print(df1)

# OUTPUT OF df2 will be as follows:

   State     City    V4  v5  v6  v7

0    NY    New York  15  21  27  33

1    TX      Austin  16  22  28  34

2    NC   Charlotte  17  23  29  35

3    TX  San Marcos  18  24  30  36

4    OK     OK city  19  25  31  37

5    SC    Columbia  20  26  32  38 

Step 2: Merging df1 and df2 based on "State" and "City" column

Code is given below

df_merged= pd.merge(df1, df2, on=["State", "City"])

print(df_merged)

# Merged DataFrame output will be as follows: 

State City V1 v2 v3 V4 v5 v6 v7

0 TX Austin 1 6 11 16 22 28 34

1 NC Charlotte 2 7 12 17 23 29 35

2 OK OK city 3 8 13 19 25 31 37

3 NY New York 4 9 14 15 21 27 33

4 TX San Marcos 5 10 15 18 24 30 36