python - 在 python 中合并多个表后更改重复的列名

Question

我已将 4 个文件合并为一个。

df1:
ID   name    location   case     pass
1    John      NY       tax       Y
2    Jack      NJ       payment   N
3    John      CA       remote    Y
4    Rose      MA       income    Y
df2:
ID   name    location   case   pass
1    John      NY       car     N
2    Jack      NJ       train   Y
3    John      CA       car     Y
4    Rose      MA       bike    N
df3:
ID   name    location   case     pass
1    John      NY       spring    Y
2    Jack      NJ       spring    Y
3    John      CA       fall      Y
4    Rose      MA       winter    N
df4:
ID   name    location   case    pass
1    John      NY       red      N
2    Jack      NJ       green    N
3    John      CA       yellow   Y
4    Rose      MA       yellow   Y

这是我合并这些表的方式。

dfs = [df1,df2,df3,df4]
df_final = reduce(lambda left,right: pd.merge(left,right,on=[ID,name,location]), dfs)

但是结果有点难读。我需要将那些 case_x,case_y,pass_x,pass_y 转换为特定的列名。合并表格时可以这样做吗？

 ID   name    location     case_x  pass_x  case_y      pass_y   case_x      pass_x  case_y   pass_y
    1    John      NY       tax       Y      car       N        spring      Y       red      N
    2    Jack      NJ       payment   N      train     Y        spring      Y      green     N
    3    John      CA       remote    Y      car       Y        fall        Y      yellow    Y 
    4    Rose      MA       income    Y      bike      N        winter      N      yellow    Y  

这是我的预期输出，

ID   name    location  case_money  pass_money  case_trans   pass_trans   case_season      pass_season  case_color  pass_color
1    John      NY       tax       Y           car                N        spring                 Y       red      N 
2    Jack      NJ       payment   N           train              Y        spring                 Y      green     N
3    John      CA       remote    Y           car                Y        fall                   Y      yellow    Y 
4    Rose      MA       income    Y           bike               N        winter                 N      yellow    Y  

Answer 1

我使用 concat 和 pivot_table 的方法:

names = ['money', 'trans', 'season', 'color']
dfs = [df1,df2,df3,df4]

new_df = (pd.concat(d.assign(name=n) for n,d in zip(names, dfs))
            .pivot_table(index=['ID','location', 'location'],
                         columns='name',
                         values=['case','pass'],
                         aggfunc='first')
         )
new_df.columns = [f'{x}_{y}' for x,y in new_df.columns]