R语言将带年份列数据框转换为按观测排序的时间序列

需求说明 #

现有长结构数据框，需要拆分为revenue、cost两个独立的时间序列宽表：

• 行维度为所有不重复的公司
• 列维度为数据覆盖的全部年份
• 无对应观测的单元格填充n.a.

原始数据构造代码如下：

df1 = data.frame(year = c('2018','2019', '2020','2019','2020','2021'), 
             company=c('x','x','x','y','y','z'),
             revenue=c(45,78,13,89,48,70),
             cost=c(100,120,130,140,160,164),
             stringsAsFactors=FALSE)

目标revenue表结构如下：

2018    2019    2020   2021
1    x       45     78      13   n.a.
2    y     n.a.     89      48   n.a.
3    z     n.a.   n.a.    n.a.    70

实现代码 #

方法1：tidyverse方案（代码简洁，推荐） #

# 加载依赖包，未安装可先运行 install.packages("tidyverse")
library(dplyr)
library(tidyr)

# 构造revenue宽表
revenue_ts <- df1 %>%
  select(company, year, revenue) %>%
  pivot_wider(
    names_from = year,
    values_from = revenue,
    values_fill = "n.a."
  ) %>%
  # 按年份升序排列列
  select(company, sort(colnames(.)[-1])) %>%
  as.data.frame()

# 构造cost宽表
cost_ts <- df1 %>%
  select(company, year, cost) %>%
  pivot_wider(
    names_from = year,
    values_from = cost,
    values_fill = "n.a."
  ) %>%
  select(company, sort(colnames(.)[-1])) %>%
  as.data.frame()

运行后输出的revenue_ts和目标结构完全一致：

company 2018 2019 2020 2021
1       x   45   78   13 n.a.
2       y n.a.   89   48 n.a.
3       z n.a. n.a. n.a.   70

方法2：基础R方案（无需安装第三方包） #

# 获取全量公司、年份维度
all_company <- unique(df1$company)
all_year <- sort(unique(df1$year))

# 构造revenue宽表
revenue_ts <- reshape(
  df1[, c("company", "year", "revenue")],
  idvar = "company",
  timevar = "year",
  direction = "wide"
)
colnames(revenue_ts)[-1] <- gsub("revenue\\.", "", colnames(revenue_ts)[-1])
revenue_ts <- revenue_ts[, c("company", all_year)]
revenue_ts[is.na(revenue_ts)] <- "n.a."
rownames(revenue_ts) <- 1:nrow(revenue_ts)

# 构造cost宽表
cost_ts <- reshape(
  df1[, c("company", "year", "cost")],
  idvar = "company",
  timevar = "year",
  direction = "wide"
)
colnames(cost_ts)[-1] <- gsub("cost\\.", "", colnames(cost_ts)[-1])
cost_ts <- cost_ts[, c("company", all_year)]
cost_ts[is.na(cost_ts)] <- "n.a."
rownames(cost_ts) <- 1:nrow(cost_ts)

注：如果需要保留数值列格式做后续计算，可以去掉填充n.a.的步骤，R默认填充的NA为标准缺失值格式，导出结果时再统一替换为n.a.即可。

内容的提问来源于stack exchange，提问作者Laura Oh Chun Xing