读取多个标题的excel文件并在R中融化多个列

Question

假设我有一个excel表，如下所示

​

您可以从this link下载示例数据

我需要融化2020-09，2020-10，2020-11作为date，并为每一对id和name提取adj_price。

在R中如何将其转换为数据帧，如下所示？在此之前，非常感谢。

​

​

Answer 1

我提出了这个解决方案，它可能是优化的，但输出是您想要的，它应该适用于任何数量的列。

library(tidyverse)

df1 <- readxl::read_xlsx(path = "path to test_data.xlsx")

# get all dates from the column names
cols <- colnames(df1)[3:ncol(df1)]
dates <- cols[grep("^[0-9][0-9][0-9][0-9]-[0-9][0-9]$", cols)]

# make a vector that will be used to make column names
colnames(df1)[3:ncol(df1)] <- rep(dates, rep(3, length(dates)))


# make a table with id, name and dates

finaldf <- df1[-1,] %>% pivot_longer(cols = 3:last_col(), names_to = "dates", values_to = "values")

indicators <- df1[-1,]
colnames(indicators) <- c("id", "name", df1[1, 3:ncol(df1)])
indicators <- indicators %>% pivot_longer(cols = 3:last_col(), names_to = "indicator", values_to = "values")

# final join and formatting
finaldf <- cbind(finaldf, indicators[, "indicator"]) %>% 
  filter(indicator == "adj_price") %>% 
  select(-indicator) %>% 
  rename("adj_price" = values) %>% 
  mutate(adj_price = as.numeric(adj_price))

输出：

> finaldf
  id             name   dates adj_price
1  1     Stracke-Huel 2020-09      3.80
2  1     Stracke-Huel 2020-10      3.72
3  1     Stracke-Huel 2020-11      3.70
4  2     Gleason-Mann 2020-09      7.25
5  2     Gleason-Mann 2020-10      7.50
6  2     Gleason-Mann 2020-11      7.50
7  3 Bauch-Cartwright 2020-09        NA
8  3 Bauch-Cartwright 2020-10     13.03
9  3 Bauch-Cartwright 2020-11     12.38