Python中如何对比DataFrame列与列表，不存在项计数设为0？

我是Python新手，正在处理一个小型数据分析问题。将DataFrame与指定列表对比后，仅能得到存在项的计数值，但需要为列表中不存在的项设置计数为0。

原始数据 #

Location    Region          Category
1   Duliajan    Western Asset   PE
2   Duliajan    Western Asset   SE
3   Duliajan    Western Asset   SE
4   Duliajan    Western Asset   SE
5   HAPJAN      Central Asset   COL
6   HAPJAN      Central Asset   OTH
7   KATHAL      Central Asset   COL
8   KATHAL      Central Asset   DP-PD
9   STF         Trunk Line      PE
10  STF         Trunk Line      PE
11  STF         Trunk Line      GL
12  STF         Trunk Line      PE
13  OTHERS      Eastern Asset   OTH
14  OTHERS      Eastern Asset   OTH

我的代码 #

a_location = ['NAGAJAN','JORAJAN','KATHAL','HEBEDA','MAKUM','BAREKURI','BAGHJAN',
        'Duliajan','LANGKASHI','HAPJAN']
category = ['DP/PD','ID','ENC','SE','COL','GL','COT','PE','FI','OTH']

df1 = df[df['Location'] .isin (a_location)]  # 修正原代码拼写错误：a_loction → a_location
print(df1)

a_data = df.groupby(['Location','Category']).size().reset_index(name="count")
print(a_data)

当前输出 #

Unnamed: 0       Location         Region Category  
0           1  Duliajan Area  Western Asset       PE           
1           2  Duliajan Area  Western Asset       SE           
2           3  Duliajan Area  Western Asset       SE           
3           4  Duliajan Area  Western Asset       SE        
4           5         HAPJAN  Central Asset      COL           
5           6         HAPJAN  Central Asset      OTH           
6           7     KATHALGURI  Central Asset      COL           
7           8     KATHALGURI  Central Asset    DP-PD          


        Location Category  count
0  Duliajan Area       PE      1
1  Duliajan Area       SE      3
2         HAPJAN      COL      1
3         HAPJAN      OTH      1
4     KATHALGURI      COL      1
5     KATHALGURI    DP-PD      1
6         OTHERS      OTH      2
7      STF-FTNGB       GL      1
8      STF-FTNGB       PE      3

需求：为a_location和category列表中所有可能的组合（即使原DataFrame中不存在该组合）设置计数为0，同时排除列表外的Location和Category项。

核心思路是先构建指定列表的所有组合，再和原统计结果做左连接，最后填充缺失值为0。另外注意原数据中DP-PD与列表内DP/PD的格式差异，需要先统一。

步骤1：统一Category格式 #

原数据中的DP-PD和列表内的DP/PD属于同一类别，先替换统一：

df['Category'] = df['Category'].replace('DP-PD', 'DP/PD')

步骤2：过滤指定Location的数据 #

只保留a_location范围内的Location项：

filtered_df = df[df['Location'].isin(a_location)]

步骤3：生成所有需要的组合 #

用pd.MultiIndex.from_product生成a_location和category的笛卡尔积（所有组合），再转为DataFrame：

import pandas as pd

all_combinations = pd.MultiIndex.from_product(
    [a_location, category],
    names=['Location', 'Category']
).to_frame(index=False)

步骤4：分组统计并左连接 #

对过滤后的数据分组统计，再和所有组合左连接，将缺失的计数填充为0：

# 分组统计存在的组合计数
grouped_data = filtered_df.groupby(['Location', 'Category']).size().reset_index(name='count')

# 左连接并填充0
result = pd.merge(all_combinations, grouped_data, on=['Location', 'Category'], how='left').fillna(0)

# 将count转为整数类型
result['count'] = result['count'].astype(int)

完整代码 #

import pandas as pd

# 构建原始DataFrame（如果已存在可跳过）
data = [
    ['Duliajan', 'Western Asset', 'PE'],
    ['Duliajan', 'Western Asset', 'SE'],
    ['Duliajan', 'Western Asset', 'SE'],
    ['Duliajan', 'Western Asset', 'SE'],
    ['HAPJAN', 'Central Asset', 'COL'],
    ['HAPJAN', 'Central Asset', 'OTH'],
    ['KATHAL', 'Central Asset', 'COL'],
    ['KATHAL', 'Central Asset', 'DP-PD'],
    ['STF', 'Trunk Line', 'PE'],
    ['STF', 'Trunk Line', 'PE'],
    ['STF', 'Trunk Line', 'GL'],
    ['STF', 'Trunk Line', 'PE'],
    ['OTHERS', 'Eastern Asset', 'OTH'],
    ['OTHERS', 'Eastern Asset', 'OTH']
]
df = pd.DataFrame(data, columns=['Location', 'Region', 'Category'])

a_location = ['NAGAJAN','JORAJAN','KATHAL','HEBEDA','MAKUM','BAREKURI','BAGHJAN',
        'Duliajan','LANGKASHI','HAPJAN']
category = ['DP/PD','ID','ENC','SE','COL','GL','COT','PE','FI','OTH']

# 统一Category格式
df['Category'] = df['Category'].replace('DP-PD', 'DP/PD')

# 过滤指定Location
filtered_df = df[df['Location'].isin(a_location)]

# 生成所有组合
all_combinations = pd.MultiIndex.from_product(
    [a_location, category],
    names=['Location', 'Category']
).to_frame(index=False)

# 统计并合并
grouped_data = filtered_df.groupby(['Location', 'Category']).size().reset_index(name='count')
result = pd.merge(all_combinations, grouped_data, on=['Location', 'Category'], how='left').fillna(0)
result['count'] = result['count'].astype(int)

print(result)

最终结果示例 #

输出会包含所有指定组合，不存在的组合计数为0，比如：

Location Category  count
0   NAGAJAN    DP/PD      0
1   NAGAJAN       ID      0
2   NAGAJAN      ENC      0
...
8    Duliajan       PE      1
9    Duliajan       FI      0
10   Duliajan      OTH      0
...

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