在R中实现多列数据长格式转宽格式的正确方法

问题背景 #

尝试将多列数据框从长格式转宽格式，但使用tidytable的pivot_wider得到的结果不符合预期，具体细节如下：

1. 原始数据框结构 #

structure(list(Material = c("XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", 
"XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", 
"XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", 
"XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", 
"XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", "XAB-03-05-10", 
"XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", 
"XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", 
"XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", 
"XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", 
"XAB-03-05-10"), `Calendar Year/Week` = c("2023/28", "2023/29", 
"2023/30", "2023/31", "2023/32", "2023/33", "2023/34", "2023/35", 
"2023/36", "2023/37", "2023/38", "2023/39", "2023/40", "2023/41", 
"2023/42", "2023/43", "2023/44", "2023/45", "2023/28", "2023/29", 
"2023/30", "2023/31", "2023/32", "2023/33", "2023/34", "2023/35", 
"2023/36", "2023/37", "2023/38", "2023/39", "2023/40", "2023/41", 
"2023/42", "2023/43", "2023/44", "2023/45"), Deal = c(0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 10, 3, 22, 26, 
14, 17, 17, 29, 19, 21, 20, 32, 29, 26, 32, 21, 28), Profit = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Comission = c(0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 61, 61, 57, 
57, 57, 57, 68, 68, 68, 68, 81, 81, 81, 81, 81, 96, 96), `Project Commission` = c(0, 
33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 
33, 0, 718, 715, 693, 667, 653, 636, 619, 590, 571, 550, 530, 
498, 469, 443, 411, 390, 362)), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -36L))

2. 当前使用的R代码 #

library(readxl)
library(tidytable)

Book7 <- read_excel("C:/X/X/X- X/X/Book7.xlsx",
                    sheet = "Sheet11")

Book <- Book7 %>% 
  pivot_wider(names_from = `Calendar Year/Week`,
              values_from = Deal:`Project Commission`)

3. 实际输出问题 #

转换后每行对应一个Material，列名为“周+指标”的组合，不符合预期格式。

4. 期望的目标数据框结构 #

structure(list(Material = c("XAB-01-02-03", "XAB-01-02-03", "XAB-01-02-03", 
"XAB-01-02-03", "XAB-03-05-10", "XAB-03-05-10", "XAB-03-05-10", 
"XAB-03-05-10"), `Calendar Year/Week` = c("Deal", "Profit", "Comission", 
"Project Commission", "Deal", "Profit", "Comission", "Project Commission"
), `2023/28` = c(0, 0, 0, 0, 0, 0, 0, 0), `2023/29` = c(0, 0, 
0, 33, 10, 0, 61, 718), `2023/30` = c(0, 0, 0, 33, 3, 0, 61, 
715), `2023/31` = c(0, 0, 0, 33, 22, 0, 57, 693), `2023/32` = c(0, 
0, 0, 33, 26, 0, 57, 667), `2023/33` = c(0, 0, 0, 33, 14, 0, 
57, 653), `2023/34` = c(0, 0, 0, 33, 17, 0, 57, 636), `2023/35` = c(0, 
0, 0, 33, 17, 0, 68, 619), `2023/36` = c(0, 0, 0, 33, 29, 0, 
68, 590), `2023/37` = c(0, 0, 0, 33, 19, 0, 68, 571), `2023/38` = c(0, 
0, 0, 33, 21, 0, 68, 550), `2023/39` = c(0, 0, 0, 33, 20, 0, 
81, 530), `2023/40` = c(0, 0, 0, 33, 32, 0, 81, 498), `2023/41` = c(0, 
0, 0, 33, 29, 0, 81, 469), `2023/42` = c(0, 0, 0, 33, 26, 0, 
81, 443), `2023/43` = c(0, 0, 0, 33, 32, 0, 81, 411), `2023/44` = c(0, 
0, 0, 33, 21, 0, 96, 390), `2023/45` = c(0, 0, 0, 33, 28, 0, 
96, 362)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-8L))

解决方案 #

你的需求是将Calendar Year/Week作为列，Material和指标名称作为行分组。要实现这个，需要先将数据转长，把Deal、Profit等指标列转换为行，再进行转宽操作，具体代码如下：

library(readxl)
library(tidytable)

Book7 <- read_excel("C:/X/X/X- X/X/Book7.xlsx", sheet = "Sheet11")

Book <- Book7 %>%
  # 先将指标列转成长格式，生成新的"指标"列和"值"列
  pivot_longer(cols = Deal:`Project Commission`,
               names_to = "Calendar Year/Week",
               values_to = "value") %>%
  # 再将周作为列名转宽
  pivot_wider(names_from = `Calendar Year/Week`,
              values_from = value)

这段代码先通过pivot_longer把原来的多指标列（Deal、Profit等）合并成两列：Calendar Year/Week（存储指标名称）和value（存储对应数值），然后再用pivot_wider把各个周作为列名，得到你期望的结构。

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