Joining Two DataFrames with Pandas:

  • When you join two DataFrames, you combine each row from one dataset with the matching row(s) from the other, rather than simply appending one DataFrame’s rows or columns beneath or beside the others.

Two DataFrames with Different Columns:

  • When you join two DataFrames with different columns, Pandas will automatically add columns to the resulting DataFrame to accommodate all the columns from both DataFrames.
  • If a row in one DataFrame doesn’t have a matching row in the other DataFrame, Pandas will fill in the missing values with NaN.
  • In the below example, we have two DataFrames, df1 and df2, with different columns.
python
1import pandas as pd
2df1 = pd.DataFrame({
3    'A': ['A1', 'A2', 'A3'],
4    'B': ['B1', 'B2', 'B3'],
5    'C': ['C1', 'C2', 'C3']
6})
7
8df2 = pd.DataFrame({
9    'A': ['A4', 'A5', 'A6'],
10    'D': ['D4', 'D5', 'D6'],
11    'E': ['E4', 'E5', 'E6']
12})

Concatenating with join=‘outer’ (default):

  • When you concatenate two DataFrames with join=‘outer’, Pandas will include all the columns from both DataFrames in the resulting DataFrame.
  • In the below example, we concatenate df1 and df2 with join=‘outer’.
    • The keys=[“df1”, “df2”] argument adds a multi-level index to the resulting DataFrame to indicate which rows came from which DataFrame.
    • The axis=0 argument tells Pandas to concatenate the DataFrames row-wise.
python
13result_outer = pd.concat([df1, df2], keys=["df1", "df2"], join='outer', axis = 0)
14print("Outer Join (All columns):")
15print(result_outer)

Output:

1Outer Join (All columns):
2        A    B    C    D    E
3df1 0  A1   B1   C1  NaN  NaN
4    1  A2   B2   C2  NaN  NaN
5    2  A3   B3   C3  NaN  NaN
6df2 0  A4  NaN  NaN   D4   E4
7    1  A5  NaN  NaN   D5   E5
8    2  A6  NaN  NaN   D6   E6

Concatenating with join=‘inner’:

  • When you concatenate two DataFrames with join=‘inner’, Pandas will only include the columns that are common to both DataFrames in the resulting DataFrame.
python
13result_inner = pd.concat([df1, df2], keys=["df1", "df2"], join='inner', axis = 0)
14print("Inner Join (Only common columns):")
15print(result_inner)

Output:

1Inner Join (Only common columns):
2        A
3df1 0  A1
4    1  A2
5    2  A3
6df2 0  A4
7    1  A5
8    2  A6