pandas 的 merge() 以共同的欄位或索引把兩個 DataFrame 黏合。以下 7 個例子涵蓋 inner / left / outer、不同 key 名稱、多個 key、以索引 merge。
Example 1：Inner merge — 預設值，只保留兩邊 key 都對應的列 #
import pandas as pd
students = pd.DataFrame({
"id": [1,2,3],
"Name":["Alice", "Bob", "Cat"]
})
scores = pd.DataFrame({
"id":[1, 2, 4],
"Score": [80,90,70]
})
pd.merge(students, scores, on="id", how="inner")output:
id Name Score 0 1 Alice 80 1 2 Bob 90
Example 2：Left merge — 保留左邊全部列，缺的補 NaN #
import pandas as pd
students = pd.DataFrame({
"id": [1,2,3],
"Name":["Alice", "Bob", "Cat"]
})
scores = pd.DataFrame({
"id":[1, 2, 4],
"Score": [80,90,70]
})
# pd.merge(students, scores, on="id", how="inner")
pd.merge(students, scores, on="id", how="left")output:
id Name Score 0 1 Alice 80.0 1 2 Bob 90.0 2 3 Cat NaN
Example 3：Outer merge — 保留兩邊全部列 #
import pandas as pd
students = pd.DataFrame({
"id": [1,2,3],
"Name":["Alice", "Bob", "Cat"]
})
scores = pd.DataFrame({
"id":[1, 2, 4],
"Score": [80,90,70]
})
# pd.merge(students, scores, on="id", how="inner")
pd.merge(students, scores, on="id", how="outer")output:
id Name Score 0 1 Alice 80.0 1 2 Bob 90.0 2 3 Cat NaN 3 4 NaN 70.0
Example 4：欄位名稱不同時 merge — 用 left_on / right_on #
#join with different keys
import pandas as pd
#Student table
students = pd.DataFrame({
"id" : [1,2,3],
"name": ["Alice", "Bob", "Cat"]
})
#Score table
score = pd.DataFrame({
"student_id" : [2,3,4],
"score" : [90,75, 88]
})
pd.merge(
students,
score,
left_on="id",
right_on="student_id",
how="left"
)output:
id name student_id score 0 1 Alice NaN NaN 1 2 Bob 2.0 90.0 2 3 Cat 3.0 75.0
Example 5：以多個 key merge — on=["id", "date"] #
#Merge with multi keys
import pandas as pd
dfA = pd.DataFrame({
"id": [1, 1, 2, 3],
"date": ["2025-01-01", "2025-01-02", "2025-01-01", "2025-01-01"],
"amount": [100, 150, 200, 250]
})
dfB = pd.DataFrame({
"id": [1, 2, 2, 3],
"date": ["2025-01-01", "2025-01-01", "2025-01-03", "2025-01-01"],
"status": ["Paid", "Pending", "Paid", "Refunded"]
})
print(dfA, '\n')
print(dfB, '\n')
merged_df = pd.merge(
dfA, dfB,
on=["id", "date"],
how="outer"
)
print(merged_df)output:
id date amount 0 1 2025-01-01 100 1 1 2025-01-02 150 2 2 2025-01-01 200 3 3 2025-01-01 250 id date status 0 1 2025-01-01 Paid 1 2 2025-01-01 Pending 2 2 2025-01-03 Paid 3 3 2025-01-01 Refunded id date amount status 0 1 2025-01-01 100.0 Paid 1 1 2025-01-02 150.0 NaN 2 2 2025-01-01 200.0 Pending 3 2 2025-01-03 NaN Paid 4 3 2025-01-01 250.0 Refunded
Example 6：以索引 merge — left_index / right_index #
#Merge by index
import pandas as pd
df1 = pd.DataFrame({
"Age": [25,30,22]
}, index=["Alice", "Bob", "Cat"])
df2 = pd.DataFrame({
"City": ["HK", "Tokyo", "Sydney", "Taiwan"]
}, index=["Alice", "Bob", "Cat", "David"])
merged = pd.merge(
df1,
df2,
left_index=True,
right_index=True,
how='inner'
)
print(merged)output:
Age City Alice 25 HK Bob 30 Tokyo Cat 22 Sydney
Example 7：左用欄位、右用索引 merge — left_on + right_index #
#Merge by index
import pandas as pd
df1 = pd.DataFrame ({
"customer_id": [101,102,103],
"order": ["A", "B", "C"]
})
df2 = pd.DataFrame ({
"name": ["Alice", "Bob", "Cat"],
"city": ["HK", "Tokyo", "Sydney"]
}, index=[101,102,103])
result = pd.merge(
df1,
df2,
left_on="customer_id",
right_index=True
)
print (result)output:
customer_id order name city 0 101 A Alice HK 1 102 B Bob Tokyo 2 103 C Cat Sydney