在R语言中将数据框Geber_Nr列值转为列名的方法求助

现有如下数据框：

structure(list(Geber_Nr = c("0101", "0101", "0101", 
"0101", "0101", "0101", "0101", "0101", "0103", "0103", "0103", 
"0103", "0103", "0103", "0103", "0103", "0104", "0104", "0104", 
"0104", "0104", "0104", "0104", "0104", "0106", "0106", "0106", 
"0106", "0106", "0106"), Date = structure(c(19069, 19069, 19069, 
19069, 19069, 19069, 19070, 19070, 19069, 19069, 19069, 19069, 
19069, 19069, 19070, 19070, 19069, 19069, 19069, 19069, 19069, 
19069, 19070, 19070, 19069, 19069, 19069, 19069, 19069, 19069
), class = "Date"), Time = structure(c(49500, 50940, 51600, 52260, 
52860, 53160, 0, 60, 49500, 50940, 51600, 52260, 52860, 53160, 
0, 60, 49500, 50940, 51600, 52260, 52860, 53160, 0, 60, 49800, 
50400, 51000, 51600, 52200, 52800), class = c("hms", "difftime"
), units = "secs"), Wert = c(0.1, 0, 0.5, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 185.57, 185.57, 185.57, 
NA, NA, NA), row.names = c(NA, -30L), class = c("tbl_df", "tbl", "data.frame"
))

需要将Geber_Nr列的不同值转为列名，对应Wert列的数值，其余位置填充NA，最终格式如下：

Date              Time              0101     0103    0104    0106
2022-03-18        13:45:00          0.10      NA      NA      NA
2022-03-18        14:09:00          0.00      NA      NA      NA
2022-03-18        14:20:00          0.50      NA      NA      NA
2022-03-18        14:31:00          0.00      NA      NA      NA
2022-03-18        14:41:00          0.00      NA      NA      NA
2022-03-18        14:46:00          0.00      NA      NA      NA
2022-03-19        00:00:00          0.00      NA      NA      NA
2022-03-19        00:01:00          0.00      NA      NA      NA
2022-03-18        13:45:00           NA      0.00     NA      NA
2022-03-18        14:09:00           NA      0.00     NA      NA
...

尝试使用dcast函数：

dcast(data, Date+Time~Geber_Nr, value.var="Wert")

但该方法会合并相同Date+Time的行，只保留有值的单元格，不符合需求。

方法1：使用dplyr逐行生成列 #

通过获取所有唯一的Geber_Nr，逐行判断并填充对应列的值，其余列设为NA：

library(dplyr)

# 获取所有唯一的传感器编号
geber_unique <- unique(data$Geber_Nr)

# 生成目标数据框
result <- data %>%
  rowwise() %>%
  mutate(across(all_of(geber_unique), ~ ifelse(cur_column() == Geber_Nr, Wert, NA))) %>%
  ungroup() %>%
  select(Date, Time, all_of(geber_unique))

方法2：使用tidyr的pivot_wider配合行号 #

先为每行添加唯一行号，避免pivot_wider合并相同Date+Time的行，再去掉行号：

library(tidyr)
library(dplyr)

result <- data %>%
  mutate(row_id = row_number()) %>%
  pivot_wider(
    id_cols = c(row_id, Date, Time),
    names_from = Geber_Nr,
    values_from = Wert,
    values_fill = NA
  ) %>%
  select(-row_id)

两种方法都能保留原始数据的所有行，仅在对应Geber_Nr列填充Wert值，其余列填充NA。

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