如何正确使用pivot_wider()对齐双变量值并批量转置多列？

问题描述 #

现有农业试验数据集，包含品种（variety）、灌溉（irrigation）、肥料类型（fertilizer）、重复（reps）、产量（yield）和养分含量（nutrients）字段，其中存在缺失值。需要将Organic和Urea两种肥料对应的yield、nutrients值按variety、irrigation、reps对齐到同一行，用于后续绘制回归图。

原数据集构造代码：

library(dplyr)
library(tidyr)

df= tibble::tibble(
    variety=rep(c("CV1", "CV2", "CV3"), each=16L),
    irrigation=rep(rep(c("yes", "no"), 3), each=8L),
    fertilizer=rep(rep(c("Organic", "Urea"), 6), each=4L),
    reps=c(1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 3, 4, 3, 1, 3, 4, 3, 2, 1, 4,
    2, 3, 1, 4, 1, 3, 1, 2, 2, 4, 1, 2, 1, 4, 2, 3, 1, 4, 2, 3, 2, 4),
    yield=c(8.379842, 8.058658, 9.73285, 9.224371999999999, NA, 6.996108000000001,
    9.865782, 7.112071666666666, 5.968758, 8.976471666666667, 7.980724, 9.35065,
    5.5111574999999995, 6.998728, 6.164252, 5.118412857142857, 7.748125, 8.58071,
    NA, NA, 7.673354999999999, 7.91948, NA, NA, 11.190445, 8.463484999999999,
    9.61818, 10.89841, 7.83943, 8.44905, 9.844165, 9.98026, 10.130675, 9.59432,
    NA, NA, 9.502525, 9.216965, NA, NA, 7.807259999999999, 9.94434, 7.92808,
    11.88664, 10.700185000000001, 10.723835000000001, 11.363140000000001,
    11.846934999999998),
    nutrients=c(0.42549600000000004, 0.417924, 0.47264, 0.45002, NA, 0.381154, 0.484084,
    0.3597316666666666, 0.32555, 0.45681666666666665, 0.38164600000000004,
    0.456822, 0.30655, 0.363892, 0.350876, 0.30200857142857146, 0.26754,
    0.30954499999999996, NA, NA, 0.328395, 0.30893, NA, NA, 0.37877, 0.33532,
    0.40417000000000003, 0.4581, 0.32077500000000003, 0.33331500000000003,
    0.39925, 0.40179000000000004, 0.40585499999999997, 0.339465, NA, NA, 0.339545,
    0.34077500000000005, NA, NA, 0.3227, 0.37770000000000004, 0.34663, 0.48564,
    0.43601500000000004, 0.38200500000000004, 0.47248500000000004, 0.506255),
)

最初尝试使用pivot_wider()仅处理nutrients列，且错误地添加了group_by()，导致Organic和Urea的yield值无法对齐：

df2= data.frame(df %>%
                group_by(variety, irrigation) %>%
                pivot_wider(names_from=fertilizer, values_from=nutrients))

期望输出格式（按reps对齐同一行）：

variety irrigation  reps     nutrients_Organic  yield_Organic  yield_Urea nutrients_Urea
1   CV1     yes         1    0.4254960          8.379842       NA         NA
2   CV1     yes         2    0.4179240          8.058658       6.996108   0.3811540
3   CV1     yes         3    0.4726400          9.732850       9.865782   0.4840840
4   CV1     yes         4    0.4500200          9.224372       7.112072   0.3597317

解决方案 #

核心修正点 #

1. 无需提前group_by()：pivot_wider()会自动根据非names_from/values_from的列进行分组，指定id_cols可以明确控制分组对齐的依据。
2. 同时转置多列：在values_from中传入包含yield和nutrients的向量，一次性完成两列的转置对齐。

完整代码 #

# 同时转置yield和nutrients，按variety、irrigation、reps对齐
df_clean <- df %>%
  pivot_wider(
    id_cols = c(variety, irrigation, reps),  # 确定每一行的唯一标识
    names_from = fertilizer,                 # 列名来自肥料类型
    values_from = c(yield, nutrients),       # 需要转置的目标列
    names_glue = "{.value}_{fertilizer}"     # 自定义列名格式：指标_肥料类型
  )

# 查看前4行结果
head(df_clean, 4)

输出结果示例 #

# A tibble: 4 × 7
  variety irrigation  reps yield_Organic yield_Urea nutrients_Organic nutrients_Urea
  <chr>   <chr>      <dbl>         <dbl>      <dbl>             <dbl>          <dbl>
1 CV1     yes            1          8.38       NA                0.425         NA   
2 CV1     yes            2          8.06        7.00              0.418          0.381
3 CV1     yes            3          9.73        9.87              0.473          0.484
4 CV1     yes            4          9.22        7.11              0.450          0.360

自定义列名（可选） #

如果希望列名格式为肥料类型_指标（如Organic_yield），只需修改names_glue参数：

df_clean <- df %>%
  pivot_wider(
    id_cols = c(variety, irrigation, reps),
    names_from = fertilizer,
    values_from = c(yield, nutrients),
    names_glue = "{fertilizer}_{.value}"
  )

内容的提问来源于stack exchange，提问作者J.K Kim