如何用Pandas按分组统计指定搜索词对应的Total Items求和值？

问题背景 #

现有如下Pandas DataFrame：

import pandas as pd
from datetime import datetime
data = {'PRACTICE': [1,2,3,1,1], 'Postcode': ['BT1234', 'BT4321', 'AB1234', 'BT1234', 'BT1234'], 
        'month': [datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 3, 1), datetime(2013, 3, 1)],
        'VTN_NM': ['Gabapentin', 'Gabapentin', 'Diazepam', 'Diazepam', 'Gabapentin elixir'], 
        'Total Items': [6, 5, 11, 4, 3]}
df = pd.DataFrame(data)

搜索词列表：

search_terms = [
    'Gabapentin', 'Pregabalin', 'Tramadol', 'Oxycodone',
    'Morphine', 'Diazepam', 'Temazepam', 'Codeine',
    'Buprenorphine', 'Methadone', 'Methylphenidate'
]

需求：按PRACTICE、Postcode、month分组，当VTN_NM包含搜索词中任意项时，对对应行的Total Items求和，每个搜索词的求和结果存为单独列（如Gaba_count对应Gabapentin，Diaz_count对应Diazepam），预期输出如下：

解决方案 #

通过标记匹配项→分组求和→列重命名三步实现，具体代码如下：

import pandas as pd
from datetime import datetime

# 初始化数据
data = {'PRACTICE': [1,2,3,1,1], 'Postcode': ['BT1234', 'BT4321', 'AB1234', 'BT1234', 'BT1234'], 
        'month': [datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 3, 1), datetime(2013, 3, 1)],
        'VTN_NM': ['Gabapentin', 'Gabapentin', 'Diazepam', 'Diazepam', 'Gabapentin elixir'], 
        'Total Items': [6, 5, 11, 4, 3]}
df = pd.DataFrame(data)

search_terms = [
    'Gabapentin', 'Pregabalin', 'Tramadol', 'Oxycodone',
    'Morphine', 'Diazepam', 'Temazepam', 'Codeine',
    'Buprenorphine', 'Methadone', 'Methylphenidate'
]

# 步骤1：为每个搜索词创建匹配列，标记VTN_NM是否包含该词
for term in search_terms:
    df[term] = df['VTN_NM'].str.contains(term, case=False, na=False).astype(int)

# 步骤2：按指定列分组，计算每个搜索词对应的Total Items求和
grouped = df.groupby(['PRACTICE', 'Postcode', 'month']).apply(
    lambda x: pd.Series({
        term: (x[term] * x['Total Items']).sum() 
        for term in search_terms
    })
).reset_index()

# 步骤3：重命名列（自定义缩写规则）
name_mapping = {
    'Gabapentin': 'Gaba_count',
    'Diazepam': 'Diaz_count',
    'Pregabalin': 'Pregab_count',
    'Tramadol': 'Tramad_count',
    # 剩余搜索词可按需添加命名规则
}
grouped = grouped.rename(columns=name_mapping)

# 步骤4：调整日期格式并填充空值为0
grouped['Month'] = grouped['month'].dt.strftime('%Y.%m').str.replace('.0', '.')
grouped = grouped.drop(columns=['month']).fillna(0)

# 筛选示例需要的列展示
result = grouped[['PRACTICE', 'Postcode', 'Month', 'Gaba_count', 'Diaz_count']]
print(result.to_markdown(index=False))

代码说明 #

1. 匹配标记：用str.contains检测VTN_NM是否包含搜索词，转换为0/1整数列，确保只有匹配行参与后续求和。
2. 分组求和：分组后对标记列和Total Items相乘再求和，精准计算每个搜索词的对应总和。
3. 列重命名：根据需求将搜索词列名改为简洁格式，方便后续使用。
4. 格式调整：将日期列转换为YYYY.M格式，空值填充为0，对齐预期输出样式。

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