如何解决R语言中pivot_longer()运行Sumpk变量时的特定报错

问题原因 #

你的Sumpk计算代码存在两个匹配逻辑错误：

• 对med表做宽转长操作时，没有保留分组维度Code，导致后续连接无法按Code维度匹配对应中位数
• left_join未显式指定连接键，多字段匹配时逻辑混乱，抛出匹配错误

修正后代码 #

# 注：tidyverse已内置dplyr、lubridate，无需重复加载
library(tidyverse)

df1 <- structure(
  list(date1 = c("2021-06-28","2021-06-28","2021-06-28","2021-06-28"),
       date2 = c("2021-04-02","2021-04-02","2021-04-08","2021-04-08"),
       Code = c("ABC","CDE","ABC","CDE"),
       Week= c("Friday","Friday","Thursday","Thursday"),
       DR1 = c(11,17,14,13),
       DR01 = c(14,11,14,13), DR02= c(14,12,16,17),DR03= c(19,15,14,13),
       DR04 = c(15,14,13,13)),
  class = "data.frame", row.names = c(NA, -4L))

x<-df1 %>% select(starts_with("DR"))
x<-cbind(df1, setNames(df1$DR1 - x, paste0(names(x), "_PV")))
pk<-select(x, date2,Code, Week, DR1, ends_with("PV"))

# 新增.groups = "drop"避免分组警告
med<-pk %>%
  group_by(Code, Week) %>%
  summarize(across(ends_with("PV"), median), .groups = "drop")

# 修正后的Sumpk计算逻辑
Sumpk<-df1%>%
  pivot_longer(-c(date1:Week), names_to = "name", values_to = "value") %>%
  left_join(med %>% 
              rename_with(~str_remove(., "_PV")) %>% 
              pivot_longer(-c(Code, Week), names_to = "name", values_to = "med"),
            by = c("Code", "Week", "name")) %>%
  mutate(new_value = value + med) %>%
  select(-c(value, med)) %>%
  pivot_wider(names_from = name, values_from = new_value, 
              names_glue = '{name}_{name}_PV')

输出说明 #

运行修正后代码得到的Sumpk表会保留原df1的date1、date2、Code、Week4个公共字段，后续每列对应调整后的DR指标计算值，列名按{原字段名}_{原字段名}_PV规则生成。

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