WikiGalaxy
Merging DataFrames on Keys
Concept Overview:
Merging DataFrames is a powerful feature in data manipulation, allowing you to combine data from different sources based on common keys. This process is essential for data analysis, enabling the integration of disparate datasets into a cohesive structure for comprehensive insights.
Example 1: Inner Join
Inner Join:
An inner join merges DataFrames based on the intersection of keys, retaining only rows with matching keys in both DataFrames.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2'],
'key': ['K0', 'K1', 'K2']})
df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'],
'D': ['D0', 'D1', 'D2'],
'key': ['K0', 'K1', 'K2']})
result = pd.merge(df1, df2, on='key')
print(result)
Explanation:
The above code demonstrates an inner join on the 'key' column, resulting in a DataFrame that includes rows where the 'key' is present in both df1 and df2.
Console Output:
A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 C2 D2
Example 2: Left Join
Left Join:
A left join returns all rows from the left DataFrame and the matched rows from the right DataFrame, filling in NaNs for unmatched rows.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2'],
'key': ['K0', 'K1', 'K2']})
df2 = pd.DataFrame({'C': ['C0', 'C1'],
'D': ['D0', 'D1'],
'key': ['K0', 'K1']})
result = pd.merge(df1, df2, on='key', how='left')
print(result)
Explanation:
In this example, a left join is performed, resulting in all rows from df1 and matching rows from df2. Rows without matches in df2 are filled with NaN.
Console Output:
A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 NaN NaN
Example 3: Right Join
Right Join:
A right join returns all rows from the right DataFrame and the matched rows from the left DataFrame, filling in NaNs for unmatched rows.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1'],
'B': ['B0', 'B1'],
'key': ['K0', 'K1']})
df2 = pd.DataFrame({'C': ['C0', 'C1', 'C2'],
'D': ['D0', 'D1', 'D2'],
'key': ['K0', 'K1', 'K2']})
result = pd.merge(df1, df2, on='key', how='right')
print(result)
Explanation:
This right join includes all rows from df2 and matching rows from df1, with unmatched rows in df1 filled with NaN.
Console Output:
A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 NaN NaN K2 C2 D2
Example 4: Outer Join
Outer Join:
An outer join returns all rows from both DataFrames, filling in NaNs for unmatched rows from either DataFrame.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2'],
'key': ['K0', 'K1', 'K2']})
df2 = pd.DataFrame({'C': ['C0', 'C1'],
'D': ['D0', 'D1'],
'key': ['K0', 'K1']})
result = pd.merge(df1, df2, on='key', how='outer')
print(result)
Explanation:
The outer join includes all rows from both df1 and df2, filling unmatched rows with NaN.
Console Output:
A B key C D 0 A0 B0 K0 C0 D0 1 A1 B1 K1 C1 D1 2 A2 B2 K2 NaN NaN
Example 5: Cross Join
Cross Join:
A cross join returns the Cartesian product of two DataFrames, resulting in a DataFrame where each row from the first DataFrame is paired with every row from the second DataFrame.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1'],
'B': ['B0', 'B1']})
df2 = pd.DataFrame({'C': ['C0', 'C1'],
'D': ['D0', 'D1']})
result = pd.merge(df1.assign(key=1), df2.assign(key=1), on='key').drop('key', axis=1)
print(result)
Explanation:
The cross join results in a DataFrame with every combination of rows from df1 and df2, achieved by assigning a constant key to both DataFrames and merging on this key.
Console Output:
A B C D 0 A0 B0 C0 D0 1 A0 B0 C1 D1 2 A1 B1 C0 D0 3 A1 B1 C1 D1
