如何统计多索引DataFrame中两组列的行重叠计数并生成矩阵

已将两个以日期为索引的DataFrame按列拼接为多层列索引的DataFrame（外层索引标记原表来源，内层为各产品价格列），完成相关性计算后，需要统计原df1与df2各产品列的非空行重叠计数（即两行同时不为空的行数），最终生成以df2产品列为行索引、df1产品列为列索引的计数矩阵。

示例数据与期望输出 #

示例代码（修正日期格式与缺失值以匹配期望输出逻辑） #

import pandas as pd

df1 = pd.DataFrame({
    'col1': ['2020-12-01', '2020-12-02', '2020-12-03'],
    'col2': [10, 11, 12],
    'col3': [13, 14, None]
})
df2 = pd.DataFrame({
    'col1': ['2020-12-01', '2020-12-02', '2020-12-03'],
    'A': [10, 9, None],
    'B': [None, 14, 2]
})

df1 = df1.set_index('col1')
df2 = df2.set_index('col1')

# 拼接为多层列索引DataFrame
concat_data1 = pd.concat([df1, df2], axis=1, keys=['df1', 'df2'])

拼接后的数据 #

df1          df2     
            col2  col3    A     B
col1                              
2020-12-01    10  13.0  10.0  NaN
2020-12-02    11  14.0   9.0  14.0
2020-12-03    12   NaN   NaN   2.0

期望输出 #

重叠计数矩阵=
     col2  col3
A      2     0
B      0     1

解决方案 #

通过集合交集快速计算非空行重叠数，生成目标矩阵：

# 提取每个列的非空行索引集合
df1_valid = {col: set(df1[col].dropna().index) for col in df1.columns}
df2_valid = {col: set(df2[col].dropna().index) for col in df2.columns}

# 初始化计数矩阵
count_matrix = pd.DataFrame(index=df2.columns, columns=df1.columns, dtype=int)

# 填充每对列的重叠计数
for df2_col in df2.columns:
    for df1_col in df1.columns:
        count_matrix.loc[df2_col, df1_col] = len(df1_valid[df1_col] & df2_valid[df2_col])

print("重叠计数矩阵=")
print(count_matrix)

说明 #

1. 将各列的非空行索引转为集合，利用集合交集操作快速定位同时非空的行
2. 遍历所有df2与df1的列对，交集长度即为对应列的重叠计数
3. 最终生成的DataFrame完全匹配需求的矩阵格式

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