You need to enable JavaScript to run this app. 如何使用Pandas连接两个DataFrame并生成分类值计数统计表
优惠活动
大模型
产品
解决方案
定价
更多

如何使用Pandas连接两个DataFrame并生成分类值计数统计表

实现逻辑核心

所有统计的分组粒度固定为State + District ID,禁止直接关联两张原始表——否则同一选区下有多条属性、多条事件记录时会产生笛卡尔积,导致所有计数翻倍。正确流程为:

  1. 分别对两张原始表按State + District ID分组,生成同一粒度的统计宽表
  2. 按关联键合并两个聚合后的宽表,补全空值为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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.13 10:45:10