Pandas多索引面板数据按年份分组逐元素除法实现问题

样本数据 #

import pandas as pd
import numpy as np

arrays1 = [['country1','country1','country1','country1','country2', 'country2', 'country2', 'country2'],
           [2000, 2001, 2000, 2001,2000, 2001, 2000, 2001],
           ['agri1','agri1', 'cons2','cons2', 'agri1','agri1', 'cons2','cons2']]
arrays = [['country1', 'country1', 'country2', 'country2'],
          ['agri1', 'cons2', 'agri1', 'cons2']]
index = pd.MultiIndex.from_arrays(arrays1, names=('country','Year','sector'))
columns1 = pd.MultiIndex.from_arrays(arrays, names=('country','sector'))
df = pd.DataFrame(np.array([[24, 20, 30, 20],[16, 14, 10, 25],[28, 22, 6, 28],
                   [11, 10, 10, 4],[6, 7, 12, 16],[19, 24, 6, 9],
                   [22, 9, 10, 15],[9, 1, 4, 2]]),index=index, columns=columns1)

数据结构如下：

country1        country2       
               agri1 cons2 agri1 cons2
country  Year sector                    
country1 2000 agri1     24    20    30    20
              cons2     16    14    10    25
country2 2000 agri1     28    22     6    28
              cons2     11    10    10     4
country1 2001 agri1      6     7    12    16
              cons2     19    24     6     9
country2 2001 agri1     22     9    10    15
              cons2      9     1     4     2

已实现的TOTAL列生成 #

借助Shubham Sharma的方案，已通过以下代码生成TOTAL列（横向求和，排除索引与列匹配的单元格）：

# 匹配索引与列的国家，排除匹配单元格后求和
ix = df.index.get_level_values('country') 
cx = df.columns.get_level_values('country')
m = ix.values[:, None] == cx.values

df[('TOTAL','EX')] = df.mask(m).sum(axis=1)
df[('TOTAL','GR')] = df.iloc[:,:-1].sum(axis=1)

得到的结果如下：

country1        country2       TOTAL     
               agri1 cons2 agri1 cons2    EX    GR
country  Year sector                                
country1 2000 agri1     24    20    30    20  50.0    94
              cons2     16    14    10    25  35.0    65
country2 2000 agri1     28    22     6    28  50.0    84
              cons2     11    10    10     4  21.0    35
country1 2001 agri1      6     7    12    16  28.0    41
              cons2     19    24     6     9  15.0    58
country2 2001 agri1     22     9    10    15  31.0    56
              cons2      9     1     4     2  10.0    16

问题描述 #

现在需要对前几列（除最后两列TOTAL）执行按年份分组的逐元素除法，即每个元素除以对应年份组内的TOTAL.GR值。尝试以下代码时出现维度不匹配错误：

df.iloc[:,:-2] = df.iloc[:,:-2].div(df[('TOTAL','GR')].values,axis=1)

报错信息：

ValueError: Unable to coerce to Series, length must be 4: given 8

尝试指定level=1按年份分组仍报错：

df.iloc[:,:-2] = df.iloc[:,:-2].div(df[('TOTAL','GR')].values,axis=1, level=1)

同样出现上述维度不匹配错误。

期望结果 #

期望的最终结果格式如下：

country1        country2       TOTAL     
               agri1    cons2   agri1    cons2    EX    GR
country  Year sector                                
country1 2000 agri1   24/94   20/65    30/84   20/35  50.0    94
              cons2   16/94   14/65    10/84   25/35  35.0    65
country2 2000 agri1   28/94   22/65     6/84   28/35  50.0    84
              cons2   11/94   10/65    10/84    4/35  21.0    35
country1 2001 agri1    6/41    7/58    12/56   16/16  28.0    41
              cons2   19/41   24/58     6/56    9/16  15.0    58
country2 2001 agri1   22/41    9/58    10/56   15/16  31.0    56
              cons2    9/41    1/58     4/56    2/16  10.0    16

解决方案 #

问题出在div的对齐逻辑上，需要先将对应年份的TOTAL.GR值广播到同一年份的所有行，再执行除法。以下是两种可行方案：

方案1：利用索引映射实现广播 #

# 按Year分组，提取每个年份对应的GR值
year_gr_map = df.groupby(level='Year')[('TOTAL','GR')].first().to_dict()

# 为每行匹配对应年份的GR值，生成与原数据行数一致的Series
gr_series = df.index.get_level_values('Year').map(year_gr_map)

# 执行逐元素除法
df.iloc[:,:-2] = df.iloc[:,:-2].div(gr_series, axis=0)

# 可选：转换为分数格式显示
df.iloc[:,:-2] = df.apply(
    lambda row: [f"{int(val * gr_series.loc[row.name])}/{gr_series.loc[row.name]}" 
                 for val in row.iloc[:-2]],
    axis=1, result_type='expand'
)

方案2：重置索引后处理再还原 #

# 重置索引，方便按年份匹配GR值
df_reset = df.reset_index()

# 按Year分组，将GR值广播到同一年份的所有行
df_reset['gr_broadcast'] = df_reset.groupby('Year')[('TOTAL','GR')].transform('first')

# 执行除法
df_reset.iloc[:,3:-3] = df_reset.iloc[:,3:-3].div(df_reset['gr_broadcast'], axis=0)

# 可选：转换为分数格式
df_reset.iloc[:,3:-3] = df_reset.apply(
    lambda row: [f"{int(val * row['gr_broadcast'])}/{row['gr_broadcast']}" 
                 for val in row.iloc[3:-3]],
    axis=1, result_type='expand'
)

# 还原MultiIndex并删除临时列
df = df_reset.set_index(['country','Year','sector']).drop('gr_broadcast', axis=1)

这两种方案都能解决维度不匹配问题，实现按年份分组的逐元素除法需求。

内容的提问来源于stack exchange，提问作者埃塞ABELA