Python Pandas - Merge DataFrame with indicator value

To merge Pandas DataFrame, use the merge() function. In that, you can set the parameter indicator to True or False. If you want to check which dataframe has a specific record, then use −

indicator= True

As shown above, using above parameter as True, adds a column to the output DataFrame called “_merge”.

At first, let us import the pandas library with an alias −

import pandas as pd

Let us create DataFrame1 −

dataFrame1 = pd.DataFrame(
   {
      "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'],
      "Units": [100, 150, 110, 80, 110, 90]
   }
)

Create DataFrame2 −

dataFrame2 = pd.DataFrame(
   {
      "Car": ['BMW', 'Lexus', 'Tesla', 'Mustang', 'Mercedes', 'Jaguar'],
      "Reg_Price": [7000, 1500, 5000, 8000, 9000, 6000]

   }
)

Now, merge DataFrames with indicator value True −

# merge DataFrames with indicator value
mergedRes = pd.merge(dataFrame1, dataFrame2, how ="left",indicator=True)

Example

Following is the code −

import pandas as pd

# Create DataFrame1
dataFrame1 = pd.DataFrame(
   {
      "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'],
      "Units": [100, 150, 110, 80, 110, 90]
   }
)

print"DataFrame1 ...\n",dataFrame1

# Create DataFrame2
dataFrame2 = pd.DataFrame(
   {
      "Car": ['BMW', 'Lexus', 'Tesla', 'Mustang', 'Mercedes', 'Jaguar'],
      "Reg_Price": [7000, 1500, 5000, 8000, 9000, 6000]

   }
)

print"\nDataFrame2 ...\n",dataFrame2

# merge DataFrames with indicator value
mergedRes = pd.merge(dataFrame1, dataFrame2, how ="left",indicator=True)
print"\nMerged dataframe...\n", mergedRes

Output

This will produce the following output. Here, “both””in “_merge displays that both the value is in both the DataFrames −

DataFrame1 ...
       Car   Units
0      BMW    100
1    Lexus    150
2     Audi    110
3  Mustang     80
4  Bentley    110
5   Jaguar     90

DataFrame2 ...
        Car   Reg_Price
0       BMW       7000
1     Lexus       1500
2     Tesla       5000
3   Mustang       8000
4  Mercedes       9000
5    Jaguar       6000

Merged dataframe...
       Car   Units   Reg_Price    _merge
0      BMW    100      7000.0       both
1    Lexus    150      1500.0       both
2     Audi    110         NaN  left_only
3  Mustang     80      8000.0       both
4  Bentley    110         NaN  left_only
5   Jaguar     90      6000.0       both