如何使用Pandas连接两个DataFrame并生成分类值计数统计表
实现逻辑核心
所有统计的分组粒度固定为State + District ID,禁止直接关联两张原始表——否则同一选区下有多条属性、多条事件记录时会产生笛卡尔积,导致所有计数翻倍。正确流程为:
- 分别对两张原始表按
State + District ID分组,生成同一粒度的统计宽表 - 按关联键合并两个聚合后的宽表,补全空值为0
关于左表/右表的选择:合并时以你最终需要保留的选区来源为左表——如果要保留所有有属性记录的选区(即使没有事件),就把属性聚合表放左边;反之则把事件聚合表放左边,两种实现逻辑在Pandas和Postgres中完全通用。
Pandas 实现步骤
首先构造和你给出的结构完全一致的示例数据:
import pandas as pd # 表1:种族、党派属性表 df_attr = pd.DataFrame({ "State": ["GA", "SC", "VA", "VA", "NY", "GA"], "District ID": [1, 5, 4, 4, 2, 1], "Race": ["White", "Black", "Black", "White", "White", "Black"], "Party": ["Dem", "Dem", "Ind", "Repub", "Dem", "Dem"] }) # 表2:事件记录表 df_event = pd.DataFrame({ "State": ["GA", "SC", "VA", "NY", "GA"], "District ID": [1, 5, 4, 2, 1], "Event Type": ["A; B; C", "B; A", "A; C", "B", "A; C"] })
第一步:属性表聚合
对Race、Party做one-hot编码后按选区分组求和,再调整列名格式:
# 生成种族、党派的one-hot列 attr_dummies = pd.get_dummies(df_attr[["Race", "Party"]]) # 关联键拼回去,按选区分组求和 attr_grouped = pd.concat([df_attr[["State", "District ID"]], attr_dummies], axis=1)\ .groupby(["State", "District ID"], as_index=False)\ .sum() # 列名改为要求的带#的格式 attr_grouped.columns = [f"# {col.split('_')[1]}" if "_" in col else col for col in attr_grouped.columns]
第二步:事件表聚合
先拆分分号分隔的多值事件,再做one-hot编码按选区求和:
# 拆分分号分隔的事件字符串 event_split = df_event["Event Type"].str.split(r";\s+") # 展开事件列表,逐行生成one-hot编码,再还原回原始行粒度 event_dummies = event_split.explode().str.get_dummies().groupby(level=0).sum() # 关联键拼回去,按选区分组求和 event_grouped = pd.concat([df_event[["State", "District ID"]], event_dummies], axis=1)\ .groupby(["State", "District ID"], as_index=False)\ .sum() # 列名改为要求的带# Event X的格式 event_grouped.columns = [f"# Event {col}" if col in ["A", "B", "C"] else col for col in event_grouped.columns]
第三步:合并聚合结果
以属性表的选区为基准左连接,空值补0,调整列顺序匹配要求:
final_df = pd.merge(attr_grouped, event_grouped, on=["State", "District ID"], how="left") final_df = final_df.fillna(0).astype(int) # 调整列顺序匹配示例格式 col_order = ["State", "District ID", "# Event A", "# Event B", "# Event C", "# White", "# Black", "# Dem", "# Repub", "# Ind"] final_df = final_df[col_order]
Postgres SQL 实现逻辑
假设两张表的表名分别为voter_attr(属性表)、district_event(事件表),用CTE分别完成两个表的聚合,再做关联:
WITH attr_agg AS ( -- 属性表按选区聚合种族、党派计数 SELECT state, district_id, SUM(CASE WHEN race = 'White' THEN 1 ELSE 0 END) AS "# White", SUM(CASE WHEN race = 'Black' THEN 1 ELSE 0 END) AS "# Black", SUM(CASE WHEN party = 'Dem' THEN 1 ELSE 0 END) AS "# Dem", SUM(CASE WHEN party = 'Repub' THEN 1 ELSE 0 END) AS "# Repub", SUM(CASE WHEN party = 'Ind' THEN 1 ELSE 0 END) AS "# Ind" FROM voter_attr GROUP BY state, district_id ), event_agg AS ( -- 事件表拆分多值字段后按选区聚合事件计数 SELECT state, district_id, SUM(CASE WHEN event_val = 'A' THEN 1 ELSE 0 END) AS "# Event A", SUM(CASE WHEN event_val = 'B' THEN 1 ELSE 0 END) AS "# Event B", SUM(CASE WHEN event_val = 'C' THEN 1 ELSE 0 END) AS "# Event C" FROM district_event, -- 拆分分号分隔的事件字符串,先去空格再转数组展开 LATERAL unnest(string_to_array(regexp_replace(event_type, '\s+', ''), ';')) AS event_val GROUP BY state, district_id ) -- 关联两个聚合结果,空值补0 SELECT a.state AS State, a.district_id AS "District ID", COALESCE(e."# Event A", 0) AS "# Event A", COALESCE(e."# Event B", 0) AS "# Event B", COALESCE(e."# Event C", 0) AS "# Event C", a."# White", a."# Black", a."# Dem", a."# Repub", a."# Ind" FROM attr_agg a LEFT JOIN event_agg e ON a.state = e.state AND a.district_id = e.district_id ORDER BY a.state, a.district_id;
常见踩坑提示
- 不要直接关联原始表,否则同一选区下的多行记录会做笛卡尔积,所有计数会被放大n倍
- 多值事件字段不要直接用
LIKE匹配计数,遇到名称重叠(比如事件名含"AB")会出现误判 - 合并后的空值必须补0,否则会出现计数列的空值
内容的提问来源于stack exchange,提问作者archingfork
相关产品推荐
相关产品推荐

