Pandas基于条件求和创建新列（类SUMPRODUCT功能）技术咨询

Got it, let's tackle this problem step by step. You want to add a new column to df_t that calculates the sum of count from df_o where the state matches and the month falls into a specific range (looks like 2011-2015 based on your example column name). Here's how to do it in Pandas, similar to Excel's SUMPRODUCT with conditions:

方法1：分组聚合 + 合并（推荐，处理缺失值更灵活） #

这种方法先对原始数据df_o做条件过滤和分组求和，再把结果合并到目标表df_t中：

import pandas as pd

# 1. 先处理df_o的month过滤（假设month是类似"2015-12"的字符串格式）
# 提取年份并筛选2011-2015的记录
df_o['year'] = df_o['month'].str[:4].astype(int)
filtered_o = df_o[(df_o['year'] >= 2011) & (df_o['year'] <= 2015)]

# 如果month是datetime类型，用下面的过滤方式：
# df_o['month'] = pd.to_datetime(df_o['month'])
# filtered_o = df_o[(df_o['month'].dt.year >= 2011) & (df_o['month'].dt.year <= 2015)]

# 2. 按state分组，对count列求和
state_total_counts = filtered_o.groupby('state')['count'].sum().reset_index(name='total_counts')

# 3. 合并到df_t，匹配的是df_t的level_1列（对应state）
df_t = df_t.merge(state_total_counts, left_on='level_1', right_on='state', how='left')

# 可选：删除多余的state列，填充匹配不到的NaN为0
df_t = df_t.drop('state', axis=1)
df_t['total_counts'] = df_t['total_counts'].fillna(0)

方法2：字典映射（更简洁） #

如果你的数据没有复杂的缺失值处理需求，可以用字典映射快速生成新列：

import pandas as pd

# 1. 同样先过滤month范围
df_o['year'] = df_o['month'].str[:4].astype(int)
filtered_o = df_o[(df_o['year'] >= 2011) & (df_o['year'] <= 2015)]

# 2. 创建state到总count的字典
state_count_map = filtered_o.groupby('state')['count'].sum().to_dict()

# 3. 直接给df_t添加新列，用level_1匹配字典中的值，缺失值填充0
df_t['total_counts'] = df_t['level_1'].map(state_count_map).fillna(0)

补充说明 #

• 两种方法的核心都是先过滤符合month条件的记录，再按state聚合求和，这和Excel中SUMPRODUCT((条件1)*(条件2)*(求和列))的逻辑完全一致。
• 如果你的"特定month值"不是年份范围，而是具体的几个月份（比如["2015-10", "2015-11", "2015-12"]），只需要把过滤条件改成df_o['month'].isin(target_months)即可。

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