求助：基于所属国家替换DataFrame中城市值为同国同日期中位数

现有两个DataFrame：

1. 名为cities的DataFrame，以城市为列名、日期为索引：

NY  LA  Rome  London  Milan
date                                   
2023-01-01   1  81    26      55     95
2023-01-02  92  42    96      98      7
2023-01-03  14   4    60      88     73

2. 名为countries的DataFrame，存储城市与国家的映射关系：

City Country
0      NY      US
1      LA      US
2  London      UK
3    Rome   Italy
4   Milan   Italy

需求：把cities里每个城市的数值，替换成该日期下其所属国家所有城市数值的中位数。预期输出如下（比如2023-01-01的NY值41是1和81的中位数）：

NY  LA  Rome  London  Milan
date                                   
2023-01-01  41  41  60.5      55   60.5
2023-01-02  67  67  51.5      98   51.5
2023-01-03   9   9  66.5      88   66.5

尝试用groupby没成功，求解决方法。

核心思路是先把宽表转成适合分组的长表，关联国家信息后计算中位数，最后再转回原宽表结构，具体步骤如下：

分步实现 #

1. 将宽表转长表：把cities的日期从索引转成列，再用melt把城市列拆成行，方便后续关联国家：

cities_long = cities.reset_index().melt(id_vars='date', var_name='City', value_name='Value')

转换后的结构大概是这样：

date    City  Value
0  2023-01-01      NY      1
1  2023-01-02      NY     92
2  2023-01-03      NY     14
3  2023-01-01      LA     81
...

2. 关联国家映射表：把长表和countries按城市名合并，给每个城市加上所属国家：

merged = cities_long.merge(countries, on='City', how='left')

3. 分组计算中位数：按日期+国家分组，计算每组的中位数：

country_median = merged.groupby(['date', 'Country'])['Value'].median().reset_index(name='Country_Median')

4. 转回宽表结构：把中位数合并回长表，再用pivot恢复原表的列结构（城市为列、日期为索引）：

result_long = merged.merge(country_median, on=['date', 'Country'], how='left')
result = result_long.pivot(index='date', columns='City', values='Country_Median')

执行完这些代码，result就是你要的输出结果。

简化链式调用 #

可以把步骤合并成一行链式代码，更简洁：

result = (
    cities.reset_index()
          .melt(id_vars='date', var_name='City', value_name='Value')
          .merge(countries, on='City')
          .groupby(['date', 'Country'])['Value']
          .median()
          .reset_index(name='Median')
          .merge(countries, on='Country')
          .pivot(index='date', columns='City', values='Median')
)

关键说明 #

• 宽表转长表是因为原cities的列是城市，没法直接按国家分组，转成每行对应一个城市+日期的结构后，才能方便关联国家并分组计算。
• 最后用pivot转回宽表，保证输出和原cities的列名、索引完全一致。

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