如何将两个关联的Pandas DataFrame合并为多级索引DataFrame？

我有两个关联的DataFrame，想找个简便方法把它们合并成以(p,c,a)为多级索引、仅含value列的结构，以下是示例代码和数据：

第一个DataFrame（df） #

import pandas as pd
df = pd.DataFrame( [[
   1,0,1,0],
    [1,1,0,0],
    [1,0,0,1],
  [0,1,0,1]], columns=["c1","c2","c3", "c4"]
 )
idx= pd.Index(['p1','p2','p3','p4'])
df = df.set_index(idx)

输出结果：

c1  c2  c3  c4
p1  1   0   1   0
p2  1   1   0   0
p3  1   0   0   1
p4  0   1   0   1

第二个DataFrame（df2） #

df2 = pd.DataFrame( [[
   0,10,30,0],
    [20,10,0,0],
    [0,10,0,6],
  [15,0,18,5]], columns=["c1","c2","c3", "c4"]
 )
idx2= pd.Index(['a1','a2','a3','a4'])
df2 = df2.set_index(idx2)

输出结果：

c1  c2  c3  c4
a1  0   10  30  0
a2  20  10  0   0
a3  0   10  0   6
a4  15  0   18  5

期望的最终结果 #

value
p1  c1  a2  20
        a4  15
    c3  a1  30
        a4  18
p2  c1  a2  20
        a4  15
    c2  a1  10
        a2  10
        a3  10
p3  c1  a2  20
        a4  15
    c4  a3  6
        a4  5
p4  c2  a1  10
        a2  10
        a3  10
    c4  a3  6
        a4  5

可以通过重塑数据+筛选匹配项+合并的步骤实现，具体操作如下：

步骤1：将两个DataFrame转为长格式 #

先把宽格式的df和df2转成长格式，筛选出有效数据：

# 处理df：只保留值为1的记录，提取p和c的对应关系
df_long = df.stack().reset_index()
df_long.columns = ['p', 'c', 'flag']
df_long = df_long[df_long['flag'] == 1].drop('flag', axis=1)

# 处理df2：只保留值非0的记录，提取a、c和对应value
df2_long = df2.stack().reset_index()
df2_long.columns = ['a', 'c', 'value']
df2_long = df2_long[df2_long['value'] != 0]

步骤2：按关联列c合并数据 #

两个DataFrame通过c列关联，直接用merge合并即可得到所有匹配的组合：

merged = pd.merge(df_long, df2_long, on='c')

步骤3：设置多级索引并排序 #

把p、c、a设为多级索引，排序后只保留value列：

result = merged.set_index(['p', 'c', 'a']).sort_index()[['value']]

完整代码 #

import pandas as pd

# 初始化原始数据
df = pd.DataFrame( [[1,0,1,0], [1,1,0,0], [1,0,0,1], [0,1,0,1]], columns=["c1","c2","c3", "c4"] )
idx= pd.Index(['p1','p2','p3','p4'])
df = df.set_index(idx)

df2 = pd.DataFrame( [[0,10,30,0], [20,10,0,0], [0,10,0,6], [15,0,18,5]], columns=["c1","c2","c3", "c4"] )
idx2= pd.Index(['a1','a2','a3','a4'])
df2 = df2.set_index(idx2)

# 转长格式并筛选有效数据
df_long = df.stack().reset_index()
df_long.columns = ['p', 'c', 'flag']
df_long = df_long[df_long['flag'] == 1].drop('flag', axis=1)

df2_long = df2.stack().reset_index()
df2_long.columns = ['a', 'c', 'value']
df2_long = df2_long[df2_long['value'] != 0]

# 合并并生成目标多级索引DataFrame
merged = pd.merge(df_long, df2_long, on='c')
result = merged.set_index(['p', 'c', 'a']).sort_index()[['value']]

print(result)

运行后输出结果与期望完全一致。

内容的提问来源于stack exchange，提问作者simon leung