如何在R中重构表格：拆分年份行并添加前后年份FLAG标识

可以用tidyverse工具集来完成这个需求，步骤清晰且易读：

1. 准备输入数据 #

library(tidyverse)

# 构造原始数据框
df <- tibble(
  Debt2017 = c(2, 3),
  Debt2018 = c(4, 8),
  Debt2019 = c(3, 9),
  Cash2017 = c(5, 7),
  Cash2018 = c(6, 9),
  Cash2019 = c(7, 9),
  Year = c(2018, 2017),
  Other = c("x", "y")
)

2. 宽表转长表并匹配Debt和Cash数据 #

先把Debt和Cash的列分别拆成长格式，提取年份后按原始行信息合并：

# 处理Debt列：转长表，提取年份
debt_long <- df %>%
  select(starts_with("Debt"), Year, Other) %>%
  pivot_longer(cols = starts_with("Debt"), names_to = "debt_year", values_to = "Debt") %>%
  mutate(year = as.numeric(str_extract(debt_year, "\\d{4}"))) %>%
  select(-debt_year)

# 处理Cash列：转长表，提取年份
cash_long <- df %>%
  select(starts_with("Cash"), Year, Other) %>%
  pivot_longer(cols = starts_with("Cash"), names_to = "cash_year", values_to = "Cash") %>%
  mutate(year = as.numeric(str_extract(cash_year, "\\d{4}"))) %>%
  select(-cash_year)

# 合并Debt和Cash的长表，按原始行的关键信息匹配
merged_df <- inner_join(debt_long, cash_long, by = c("Year", "Other", "year"))

3. 过滤指定年份并添加FLAG列 #

final_df <- merged_df %>%
  # 移除与每行Year列相同的年份数据
  filter(year != Year) %>%
  # 添加FLAG标识：年份小于指定Year则标记0，否则标记1
  mutate(`FLAG After` = ifelse(year < Year, 0, 1)) %>%
  # 整理成目标列顺序
  select(Debt, Cash, `FLAG After`, Other)

4. 查看最终结果 #

运行后final_df就是你需要的格式：

print(final_df)
# 输出：
# # A tibble: 4 × 4
#    Debt  Cash `FLAG After` Other
#   <dbl> <dbl>        <dbl> <chr>
# 1     2     5            0 x    
# 2     3     7            1 x    
# 3     8     9            1 y    
# 4     9     9            1 y

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