如何按ID分组计算各国家对应Type的占比并生成新变量

原始数据 #

目标结果 #

Python (Pandas) 实现 #

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    'ID': [1,1,1,1,2,2,2,2],
    'Country': ['Austria','Austria','Austria','Belgium','Czech','Czech','Denmark','Denmark'],
    'Type': ['A','A','A','A','B','B','B','C']
})

# 计算每个ID组的总条数
id_total = df.groupby('ID').size().reset_index(name='total')

# 计算(ID, Country, Type)组合的计数与占比
grouped_data = df.groupby(['ID', 'Country', 'Type']).size().reset_index(name='count')
grouped_data = grouped_data.merge(id_total, on='ID')
grouped_data['ratio'] = grouped_data['count'] / grouped_data['total']

# 将Type转为宽格式，填充占比，其余补0
pivot_result = grouped_data.pivot_table(
    index=['ID', 'Country', 'Type'],
    columns='Type',
    values='ratio',
    fill_value=0
).reset_index()

# 合并回原始数据，确保每条记录匹配对应占比
final_result = df.merge(pivot_result, on=['ID', 'Country', 'Type'], how='left')

print(final_result)

R 实现 #

library(dplyr)
library(tidyr)

# 构造原始数据
df <- data.frame(
    ID = c(1,1,1,1,2,2,2,2),
    Country = c("Austria","Austria","Austria","Belgium","Czech","Czech","Denmark","Denmark"),
    Type = c("A","A","A","A","B","B","B","C")
)

# 计算分组占比
grouped_ratio <- df %>%
    group_by(ID) %>%
    mutate(total = n()) %>%
    group_by(ID, Country, Type, total) %>%
    summarise(count = n(), .groups = "drop") %>%
    mutate(ratio = count / total)

# 将Type转为宽格式
pivot_df <- grouped_ratio %>%
    pivot_wider(
        id_cols = c(ID, Country, Type),
        names_from = Type,
        values_from = ratio,
        values_fill = 0
    )

# 合并回原始数据
final_result <- df %>%
    left_join(pivot_df, by = c("ID", "Country", "Type"))

print(final_result)

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