使用dplyr管道函数计算数据框指定列行内最值出错求助

问题描述 #

需要用dplyr管道函数计算数据框中最近5周的列每行的最小值和最大值，但当前代码输出的所有行的Max、Min均为全局最值，而非每行对应列的最值，Sum的列选择也不符合“最近5周”的要求。

数据示例 #

#       Material Calendar Year/Week 2023/39  ...  2023/40 2023/41 2023/42 2023/43 2023/44 2023/45
# 1 XAB-01-02-03               Deal      85  ...       60      51      65      74      12       1
# 2 XAB-01-02-03             Profit      18  ...       38      61      94      65      20      65
# 3 XAB-01-02-03          Comission      32  ...       32      42      49      77      53      58
# 4 XAB-01-02-03 Project Commission       8  ...        7      93      41       3      89      50
# 5 XAB-03-05-10               Deal      86  ...       96      41      11       2      32      88
# 6 XAB-03-05-10             Profit      16  ...      100      26       1      11      50      82
# 7 XAB-03-05-10          Comission      32  ...       92      34      97      19      93      47
# 8 XAB-03-05-10 Project Commission      27  ...       46      12      23      96      14      22

错误代码 #

library(readxl)
library(dplyr)

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

First_Date <- head(tail(colnames(Book7)),1) 
Last_Date <- tail(colnames(Book7),1)

Book7 <- Book7 %>% 
  mutate(Sum = rowSums(pick(any_of(First_Date):any_of(Last_Date)))) %>% 
  mutate(Max = max(pick(any_of(First_Date):any_of(Last_Date)))) %>% 
  mutate(Min = min(pick(any_of(First_Date):any_of(Last_Date))))

错误输出 #

#       Material Calendar Year/Week   ...  2023/42 2023/43 2023/44 2023/45 Sum Max Min
# 1 XAB-01-02-03               Deal   ...       65      74      12       1 263 100   1
# 2 XAB-01-02-03             Profit   ...       94      65      20      65 343 100   1
# 3 XAB-01-02-03          Comission   ...       49      77      53      58 311 100   1
# 4 XAB-01-02-03 Project Commission   ...       41       3      89      50 283 100   1
# 5 XAB-03-05-10               Deal   ...       11       2      32      88 270 100   1
# 6 XAB-03-05-10             Profit   ...        1      11      50      82 270 100   1
# 7 XAB-03-05-10          Comission   ...       97      19      93      47 382 100   1
# 8 XAB-03-05-10 Project Commission   ...       23      96      14      22 213 100   1

数据dput #

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(6, 4, 40, 21, 18, 37, 65, 61), `2023/29` = c(92, 
92, 98, 33, 54, 43, 5, 51), `2023/30` = c(81, 56, 77, 54, 56, 
53, 21, 92), `2023/31` = c(5, 0, 28, 81, 88, 67, 94, 58), `2023/32` = c(54, 
41, 91, 10, 47, 67, 40, 17), `2023/33` = c(82, 73, 70, 67, 45, 
88, 79, 25), `2023/34` = c(30, 40, 12, 65, 97, 92, 64, 31), `2023/35` = c(46, 
1, 17, 36, 61, 75, 78, 10), `2023/36` = c(4, 43, 88, 100, 65, 
15, 82, 88), `2023/37` = c(83, 73, 17, 96, 53, 9, 94, 10), `2023/38` = c(78, 
75, 44, 30, 89, 51, 45, 30), `2023/39` = c(85, 18, 32, 8, 86, 
16, 32, 27), `2023/40` = c(60, 38, 32, 7, 96, 100, 92, 46), `2023/41` = c(51, 
61, 42, 93, 41, 26, 34, 12), `2023/42` = c(65, 94, 49, 41, 11, 
1, 97, 23), `2023/43` = c(74, 65, 77, 3, 2, 11, 19, 96), `2023/44` = c(12, 
20, 53, 89, 32, 50, 93, 14), `2023/45` = c(1, 65, 58, 50, 88, 
82, 47, 22)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-8L))

期望输出 #

错误原因与修正方案 #

错误原因 #

1. 列选择错误：原代码中First_Date的计算逻辑错误，未正确选中“最近5周”的列，导致Sum计算的是6列的和而非5列。
2. 最值计算逻辑错误：直接使用max(pick(...))和min(pick(...))会计算整个选中列的全局最值，而非每行的最值。

修正代码 #

library(readxl)
library(dplyr)

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

# 正确计算最近5周的每行Sum、Max、Min
Book7 <- Book7 %>%
  rowwise() %>%
  mutate(
    # 选择最后5个周列：从倒数第5列到最后一列
    Sum = rowSums(pick(last_col(offset = 4):last_col()), na.rm = TRUE),
    Max = max(c_across(last_col(offset = 4):last_col()), na.rm = TRUE),
    Min = min(c_across(last_col(offset = 4):last_col()), na.rm = TRUE)
  ) %>%
  ungroup()

代码说明 #

• rowwise()：将数据框按行分组，确保后续计算是基于每行进行的。
• last_col(offset = 4):last_col()：精准选择最后5个列（offset=4表示从倒数第5列开始，到最后一列结束）。
• c_across()：在rowwise分组下，将每行的指定列转换为向量，供max()/min()计算每行的最值。
• na.rm = TRUE：处理可能存在的缺失值，避免计算结果为NA。

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