如何用dplyr按id基于同一数据集批量创建衍生变量

原始数据集 #

df <- data.frame(year = c("2000", "2000", "2000", "2002", "2000", "2002", "2007"), 
                 id = c("X", "X", "X", "X", "Z", "Z", "Z"), 
                 product = c("apple", "orange", "orange", "orange", "cake", "cake", "bacon"), 
                 market = c("CHN", "USA", "USA", "USA", "SPA", "CHL", "CHL"), 
                 value = c(1, 2, 3, 4, 5, 6, 7))

衍生变量需求 #

需按id生成以下8个衍生变量：

• years_PM：该产品和市场的年份数（包含t-1年）
• value_PM：该产品和市场的总价值（包含t-1年）
• years_OPM：其他产品在其他市场的年份数（包含t-1年）
• years_SP_OM：同一产品在其他市场的年份数（包含t-1年）
• history：若该id有历史记录（包含t-1年）则赋值1，否则0
• year_id：同一id的年份数（包含t-1年）
• year_id_consecutive：同一id的连续年份数，若间隔超过2年则重置为0（包含t-1年）
• n_id_PM：该产品和市场中其他id的数量（仅统计t-1年）

期望输出数据集 #

df_new <- data.frame(year = c("2000", "2000", "2000", "2002", "2000", "2002", "2007"), 
                     id = c("X", "X", "X", "X", "Z", "Z", "Z"), 
                     product = c("apple", "orange", "orange", "orange", "cake", "cake", "bacon"), 
                     market = c("CHN", "USA", "USA", "USA", "SPA", "CHL", "CHL"), 
                     value = c(1, 2, 3, 4, 5, 6, 7), 
                     years_PM = c(0, 0, 0, 1, 0, 0, 0), 
                     value_PM = c(0, 0, 0, 5, 0, 0, 0), 
                     years_OPM = c(0, 0, 0, 1, 0, 0, 0), 
                     years_SP_OM = c(0, 0, 0, 0, 0, 1, 0), 
                     history = c(0, 0, 0, 1, 0, 1, 1), 
                     year_id = c(0, 0, 0, 1, 0, 1, 2), 
                     year_id_consecutive = c(0, 0, 0, 1, 0, 1, 0), 
                     n_id_PM = c(0, 0, 0, 0, 0, 0, 0))

解决方案代码 #

使用dplyr窗口函数结合分组操作，无需额外合并数据集，直接在原始数据上生成所有衍生变量：

library(dplyr)
library(purrr)

df_result <- df %>%
  mutate(year_num = as.integer(year)) %>%
  # 处理id相关基础变量
  group_by(id) %>%
  mutate(
    year_id = cumsum(!duplicated(year_num)) - 1,
    history = ifelse(row_number() == 1, 0, 1),
    year_diff = year_num - lag(year_num, default = first(year_num)),
    year_id_consecutive = accumulate(year_diff, ~ ifelse(.y > 2, 0, .x + 1)) - 1
  ) %>%
  ungroup() %>%
  # 处理产品-市场维度变量
  group_by(product, market) %>%
  mutate(
    years_PM = cumsum(!duplicated(year_num)) - 1,
    value_PM = cumsum(value) - value,
    n_id_PM = lag(n_distinct(id), default = 0) - ifelse(id %in% lag(unique(id), default = c()), 1, 0)
  ) %>%
  ungroup() %>%
  # 处理同产品异市场变量
  group_by(id, product) %>%
  mutate(
    years_SP_OM = lag(n_distinct(market), default = 0) - ifelse(market %in% lag(unique(market), default = c()), 1, 0)
  ) %>%
  ungroup() %>%
  # 处理异产品异市场变量
  group_by(id) %>%
  mutate(
    total_years = cumsum(!duplicated(paste(product, market, year_num))) - 1,
    years_OPM = total_years - years_PM
  ) %>%
  ungroup() %>%
  # 整理输出列
  select(year, id, product, market, value, 
         years_PM, value_PM, years_OPM, years_SP_OM, 
         history, year_id, year_id_consecutive, n_id_PM)

# 验证结果与期望一致
all.equal(df_result, df_new)

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