如何为每个大洲类别重复显示全部5个Rank区间（含计数为0的情况）

我有5个Rank区间，以及按大洲分类的各区间内国家计数数据，希望每个大洲对应的Rank列都显示全部5个区间，即便该区间计数为0。

当前合并后的数据 #

Continent      Rank            
Asia           (2.212, 15.753]     7
               (2.212, 15.753]     7
               (2.212, 15.753]     7
               (2.212, 15.753]     7
               (15.753, 29.227]    4
Australia      (2.212, 15.753]     7
Europe         (2.212, 15.753]     7
               (15.753, 29.227]    4
               (15.753, 29.227]    4
               (15.753, 29.227]    4
               (29.227, 42.701]    2
               (29.227, 42.701]    2
North America  (2.212, 15.753]     7
               (56.174, 69.648]    2
South America  (56.174, 69.648]    2
Name: Count, dtype: int64

用于合并的两个原始数据框 #

数据框1（Rank区间及对应基础计数） #

Rank
(2.212, 15.753]     7
(15.753, 29.227]    4
(29.227, 42.701]    2
(56.174, 69.648]    2
(42.701, 56.174]    0

数据框2（大洲与Rank的对应记录） #

Continent
Asia             (15.753, 29.227]
North America     (2.212, 15.753]
Asia              (2.212, 15.753]
Europe            (2.212, 15.753]
Europe           (15.753, 29.227]
North America    (56.174, 69.648]
Europe           (15.753, 29.227]
Asia              (2.212, 15.753]
Europe           (15.753, 29.227]
Asia              (2.212, 15.753]
Europe           (29.227, 42.701]
Europe           (29.227, 42.701]
Asia              (2.212, 15.753]
Australia         (2.212, 15.753]
South America    (56.174, 69.648]

当前使用的合并代码 #

merge1 = renew.reset_index()
merge1 = merge1.merge(counts,how='right', on='Rank')

merge1 = merge1.dropna()
merge1 = merge1.drop('index', axis=1)

merge1

要实现每个大洲对应全部5个Rank区间（含计数为0的情况），需先生成大洲和Rank的完整笛卡尔积，再结合实际计数数据填充值，步骤如下：

1. 预处理原始数据 #

假设数据框1命名为rank_counts，数据框2命名为continent_rank：

• 给数据框2添加列名，并按大洲和Rank分组统计实际出现次数：

# 补充列名
continent_rank.columns = ['Continent', 'Rank']
# 分组计数
continent_counts = continent_rank.groupby(['Continent', 'Rank']).size().reset_index(name='Actual_Count')

2. 生成完整的大洲-Rank组合 #

提取所有唯一的大洲和Rank区间，生成笛卡尔积确保所有组合都存在：

import pandas as pd

# 获取唯一的大洲和Rank列表
continents = continent_rank['Continent'].unique()
ranks = rank_counts.index.unique()

# 生成所有大洲与Rank的组合
full_combinations = pd.MultiIndex.from_product([continents, ranks], names=['Continent', 'Rank']).to_frame(index=False)

3. 合并数据并填充0 #

将完整组合与分组计数、Rank基础计数合并，缺失的计数填充为0：

# 合并完整组合与实际计数
result = full_combinations.merge(continent_counts, on=['Continent', 'Rank'], how='left')
# 合并Rank对应的基础计数
result = result.merge(rank_counts.reset_index(), on='Rank', how='left')
# 填充缺失的实际计数为0
result['Actual_Count'] = result['Actual_Count'].fillna(0).astype(int)

# 按需调整列名（例如将rank_counts的列名改为Base_Count）
result = result.rename(columns={rank_counts.name: 'Base_Count'})

4. 最终结果示例 #

处理后的数据会呈现每个大洲对应所有5个Rank区间，无数据的区间Actual_Count为0，同时保留对应的Base_Count：

Continent           Rank  Actual_Count  Base_Count
0          Asia  (2.212, 15.753]             4           7
1          Asia (15.753, 29.227]             1           4
2          Asia (29.227, 42.701]             0           2
3          Asia (56.174, 69.648]             0           2
4          Asia (42.701, 56.174]             0           0
5      Australia  (2.212, 15.753]             1           7
6      Australia (15.753, 29.227]             0           4
...

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