python - 如何在pandas中合并多个索引

Question

测试1.csv

"P","E","DT02","DT03"

1, 4, 20020111, 20121222

2, 5, 20021111, 20141222

3, 4, 20021112, 20151222

test2.csv

"P", "DT02", "dd"

1, 20020111, 1

2, 20021111, 1

3, 20021113, 0

代码:

df1 = pd.read_csv('test1.csv')

df2 = pd.read_csv('test2.csv')

mulidx = pd.MultiIndex.from_arrays([['info'] * 2 + ['2002', '2003'],["P", "E", "2002", "2003"]])

df1.columns = mulidx

print(df1)

  info         2002      2003

     P  E      DT02      DT03
0    1  4  20020111  20121222
1    2  5  20021111  20141222
2    3  4  20021112  20151222

mulidx2 = pd.MultiIndex.from_arrays([['info']+ ['2002']*2,["P", "DT02", "DTH"]])

df2.columns = mulidx2

print(df2)

    info      2002
     P      DT02 DTH
0    1  20020111   1
1    2  20021111   1
2    3  20021113   0

我要合并

  info         2002      2003

     P  E      DT02 DTH      DT03
0    1  4  20020111   1  20121222
1    2  5  20021111   1  20141222
2    3  4  20021112 NAN  20151222

如何在pandas中合并多个索引？

Answer 1

实现此目的的一种方法是展平多索引列，合并并将展平列名称扩展回多索引:

df1.columns = df1.columns.map('|'.join)

df2.columns= df2.columns.map('|'.join)

df_out = df1.merge(df2, on='info|P')

df_out.columns = df_out.columns.str.split('|',expand=True)

df_out

输出:

  info         2002      2003      2002    
     P  E    DT02_x      DT03    DT02_y DTH
0    1  4  20020111  20121222  20020111   1
1    2  5  20021111  20141222  20021111   1
2    3  4  20021112  20151222  20021113   0