如何pandas.DateFrame.merge()合并3个及以上dataframe
from functools import reduce
import pandas as pd
data1 = {"XH": ['01', '02'], "STATUS_1": ["GOOD", "GOOD"]}
data2 = {"XH": ['01', '02'], "STATUS_2": ["BAD", "BAD"]}
data3 = {"XH": ['01', '02'], "STATUS_3": ["TERRIBLE", "TERRIBLE"]}
df1 = pd.DataFrame(data=data1)
df2 = pd.DataFrame(data=data2)
df3 = pd.DataFrame(data=data3)
dfs = [df1, df2, df3]
df = reduce(lambda x, y: pd.merge(x, y, on="XH", how="outer"), dfs)
print(df)
output:
XH STATUS_1 STATUS_2 STATUS_3
0 01 GOOD BAD TERRIBLE
1 02 GOOD BAD TERRIBLE
由于merge每次只能合并两个dataframe，所以这里使用reduce和lambda函数简化merge的过程。但是值得注意的是，如果要合并的dataframe的columns name是一样的，很难再对合并后的dataframe进行列名重命名等操作。
例如：
from functools import reduce
import pandas as pd
data1 = {"XH": ['01', '02'], "STATUS": ["GOOD", "GOOD"]}
data2 = {"XH": ['01', '02'], "STATUS": ["BAD", "BAD"]}
data3 = {"XH": ['01', '02'], "STATUS": ["TERRIBLE", "TERRIBLE"]}
data4 = {"XH": ['01', '02'], "STATUS": ["FINE", "FINE"]}
data5 = {"XH": ['01', '02'], "STATUS": ["BAD", "BAD"]}
data6 = {"XH": ['01', '02'], "STATUS": ["GOOD", "TERRIBLE"]}
df1 = pd.DataFrame(data=data1)
df2 = pd.DataFrame(data=data2)
df3 = pd.DataFrame(data=data3)
df4 = pd.DataFrame(data=data4)
df5 = pd.DataFrame(data=data5)
df6 = pd.DataFrame(data=data6)
dfs = [df1, df2, df3, df4, df5, df6]
df = reduce(lambda x, y: pd.merge(x, y, on="XH", how="outer"), dfs)
print(df)
output:
XH STATUS_x STATUS_y STATUS_x STATUS_y STATUS_x STATUS_y
0 01 GOOD BAD TERRIBLE FINE BAD GOOD
1 02 GOOD BAD TERRIBLE FINE BAD TERRIBLE
这时候由于存在多个STATUS_x和STATUS_y，普通的reindex， rename等方法将很难起作用。
因此建议在进行合并操作前对每一个DataFrame的列名进行重命名，以使列名各不相同。
例如：
from functools import reduce
import pandas as pd
data1 = {"XH": ['01', '02'], "STATUS": ["GOOD", "GOOD"]}
data2 = {"XH": ['01', '02'], "STATUS": ["BAD", "BAD"]}
data3 = {"XH": ['01', '02'], "STATUS": ["TERRIBLE", "TERRIBLE"]}
data4 = {"XH": ['01', '02'], "STATUS": ["FINE", "FINE"]}
data5 = {"XH": ['01', '02'], "STATUS": ["BAD", "BAD"]}
data6 = {"XH": ['01', '02'], "STATUS": ["GOOD", "TERRIBLE"]}
data = [data1, data2, data3, data4, data5, data6]
dfs = []
length = 6
for i in range(length):
dfs.append("df"+str(i+1))
for j in range(length):
dfs[j] = pd.DataFrame(data=data[j])
old_val = dfs[j].columns.values.tolist()
new_val = ["XH"]
for each in old_val[1:]:
new_val.append(each+"_"+str(j+1))
col = dict(zip(old_val, new_val))
dfs[j] = dfs[j].rename(columns=col)
df = reduce(lambda x, y: pd.merge(x, y, on="XH", how="outer"), dfs)
print(df)
output:
XH STATUS_1 STATUS_2 STATUS_3 STATUS_4 STATUS_5 STATUS_6
0 01 GOOD BAD TERRIBLE FINE BAD GOOD
1 02 GOOD BAD TERRIBLE FINE BAD TERRIBLE
这样就不会产生列名重复而无法reindex和rename的烦恼了
|