pandas.DateFrame.merge()合并3个及以上dataframe

如何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的烦恼了