R语言使用pivot_wider重塑数据出现大量NA值的问题求助

问题场景 #

我尝试用pivot_wider处理PARAMETER列提取唯一值，但执行后出现大量NA。所用代码：

pivot_wider(names_from = PARAMETER,
            values_from = Month_Average)

期望输出 #

希望将数据转换为每行对应一组Year、Month、LAT、LON，同时包含各类气象参数数值的格式，示例如下：

尝试过的方法 #

用na.omit()会直接删除所有行，无法解决问题。

相关数据 #

原始数据（head(dput())结果） #

structure(list(PARAMETER = c("PS", "PS", "PS", "PS", "PS", "PS"
), YEAR = c(1990L, 1990L, 1990L, 1990L, 1990L, 1990L), LAT = c(35.25, 
35.25, 35.25, 35.25, 35.25, 35.25), LON = c(-71.75, -71.75, -71.75, 
-71.75, -71.75, -71.75), ANN = c(101.91, 101.91, 101.91, 101.91, 
101.91, 101.91), MONTH = c("NOV", "JAN", "FEB", "MAR", "APR", 
"MAY"), Month_Average = c(101.9, 102.01, 102.22, 102.36, 101.87, 
101.63)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

pivot_wider执行后结果 #

structure(list(YEAR = c(1990L, 1990L, 1990L, 1990L, 1990L, 1990L
), LAT = c(35.25, 35.25, 35.25, 35.25, 35.25, 35.25), LON = c(-71.75, 
-71.75, -71.75, -71.75, -71.75, -71.75), ANN = c(101.91, 101.91, 
101.91, 101.91, 101.91, 101.91), MONTH = c("NOV", "JAN", "FEB", 
"MAR", "APR", "MAY"), PS = c(101.9, 102.01, 102.22, 102.36, 101.87, 
101.63), T2M = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), RH2M = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), WS10M = c(NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), WS50M = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_), PRECTOTCORR = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA, -6L
), class = c("tbl_df", "tbl", "data.frame"))

附加参数测试数据 #

structure(list(YEAR = c(1990L, 1990L, 1990L, 1990L, 1990L, 1990L
), MONTH = c("APR", "APR", "APR", "APR", "APR", "APR"), LAT = c(35.25, 
35.25, 35.25, 35.25, 35.25, 35.25), LON = c(-78.75, -78.75, -78.75, 
-78.75, -78.75, -78.75), ANN = c(2.93, 3.42, 5.39, 16.89, 75.28, 
101.13), number_of_parameters = c(1L, 1L, 1L, 1L, 1L, 1L)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -6L), groups = structure(list(
    YEAR = 1990L, MONTH = "APR", LAT = 35.25, LON = -78.75, .rows = structure(list(
        1:6), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -1L), .drop = TRUE))

解决方案 #

问题原因 #

出现大量NA的核心原因：一是原始数据中每组YEAR+MONTH+LAT+LON仅包含单一参数（比如示例里全是PS），pivot_wider会保留所有出现过的参数列，无对应值则填充NA；二是如果数据处于分组状态（比如附加测试数据是grouped_df），会导致pivot_wider无法正确合并同组的不同参数行。

解决步骤 #

1. 取消数据分组（如果存在）：

df <- ungroup(df)

2. 显式指定id_cols参数：明确标记作为每行唯一标识的列，让pivot_wider知道哪些列用来合并行：

df_wide <- df %>%
  pivot_wider(
    id_cols = c(YEAR, MONTH, LAT, LON),
    names_from = PARAMETER,
    values_from = Month_Average
  )

3. 保留固定值列（可选）：如果ANN列在同组中是固定值，可加入id_cols一起保留：

df_wide <- df %>%
  pivot_wider(
    id_cols = c(YEAR, MONTH, LAT, LON, ANN),
    names_from = PARAMETER,
    values_from = Month_Average
  )

效果验证 #

当完整数据包含同一YEAR+MONTH+LAT+LON下的多个参数时，上述代码会将它们合并到同一行，仅保留合理的缺失值NA。

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