R语言按小时宽表转换：避免日期重复行，保留PM25与NULLCODE

云上老周

2026-6-27

问题

我有一份全年逐小时的空气质量数据集，用pivot_wider做宽表转换时遇到问题：当NULLCODE列有值时，同一日期会被拆分成多行（示例里2019-02-09拆成了3行），但我希望每个日期只保留一行。

需求是实现宽表转换，让每个小时对应两列：一列存PM25值，另一列存NULLCODE值，避免日期被拆分。

复现数据

library(tidyr)
data <- data.frame(DATE = c("2019-02-09"),
                   TIME = c("00:00", "01:00", "02:00","03:00", "04:00","05:00","06:00","07:00","08:00","09:00","10:00","11:00","12:00","13:00","14:00","15:00","16:00","17:00","18:00","19:00","20:00","21:00","22:00","23:00"),
                   PM25 = c("2","1","0","-1","-4","NA","NA","NA","-4","1","6","28","23","3","3","5","NA","9","24","24","17","16","9","15"),
                   NULLCODE = c("","","","","","DA","DA","DA","","","","","","","","","AM","","","","","","",""))

原始数据预览

> print(data)
         DATE  TIME PM25 NULLCODE
1  2019-02-09 00:00    2         
2  2019-02-09 01:00    1         
3  2019-02-09 02:00    0         
4  2019-02-09 03:00   -1         
5  2019-02-09 04:00   -4         
6  2019-02-09 05:00   NA       DA
7  2019-02-09 06:00   NA       DA
8  2019-02-09 07:00   NA       DA
9  2019-02-09 08:00   -4         
10 2019-02-09 09:00    1         
11 2019-02-09 10:00    6         
12 2019-02-09 11:00   28         
13 2019-02-09 12:00   23         
14 2019-02-09 13:00    3         
15 2019-02-09 14:00    3         
16 2019-02-09 15:00    5         
17 2019-02-09 16:00   NA       AM
18 2019-02-09 17:00    9         
19 2019-02-09 18:00   24         
20 2019-02-09 19:00   24         
21 2019-02-09 20:00   17         
22 2019-02-09 21:00   16         
23 2019-02-09 22:00    9         
24 2019-02-09 23:00   15

错误的转换代码及结果

之前用的代码会把同一日期拆成多行：

data2 <- data %>% pivot_wider( 
  names_from = TIME,
  names_prefix = "Time_",
  values_from = PM25
)

转换后结果：

> print(data2)
        DATE NULLCODE Time_00:00 Time_01:00 Time_02:00 Time_03:00 Time_04:00
1 2019-02-09                   2          1          0         -1         -4
2 2019-02-09       DA       <NA>       <NA>       <NA>       <NA>       <NA>
3 2019-02-09       AM       <NA>       <NA>       <NA>       <NA>       <NA>
  Time_05:00 Time_06:00 Time_07:00 Time_08:00 Time_09:00 Time_10:00 Time_11:00
1       <NA>       <NA>       <NA>         -4          1          6         28
2         NA         NA         NA       <NA>       <NA>       <NA>       <NA>
3       <NA>       <NA>       <NA>       <NA>       <NA>       <NA>       <NA>
  Time_12:00 Time_13:00 Time_14:00 Time_15:00 Time_16:00 Time_17:00 Time_18:00
1         23          3          3          5       <NA>          9         24
2       <NA>       <NA>       <NA>       <NA>       <NA>       <NA>       <NA>
3       <NA>       <NA>       <NA>       <NA>         NA       <NA>       <NA>
  Time_19:00 Time_20:00 Time_21:00 Time_22:00 Time_23:00
1         24         17         16          9         15
2       <NA>       <NA>       <NA>       <NA>       <NA>
3       <NA>       <NA>       <NA>       <NA>       <NA>

解决方案

问题根源是：pivot_wider会把除names_from和values_from之外的所有列作为分组依据，所以NULLCODE的不同值（空字符串、DA、AM）会把同一日期拆分成不同行。

要实现每个日期一行，同时每个小时对应PM25和NULLCODE两列，只需把PM25和NULLCODE都设为values_from的目标列，再用names_glue定义列名格式即可：

正确转换代码

data_wide <- data %>% 
  pivot_wider(
    id_cols = DATE,  # 只按DATE分组，确保每个日期一行
    names_from = TIME,
    values_from = c(PM25, NULLCODE),  # 同时转换两列
    names_glue = "{.value}_{TIME}"  # 列名格式：列名_时间，比如PM25_00:00、NULLCODE_00:00
  )

转换后结果预览

运行后每个日期仅保留一行，每个小时对应两列：

> print(data_wide)
        DATE PM25_00:00 PM25_01:00 PM25_02:00 PM25_03:00 PM25_04:00 PM25_05:00 PM25_06:00 PM25_07:00 PM25_08:00 PM25_09:00 PM25_10:00
1 2019-02-09          2          1          0         -1         -4         NA         NA         NA         -4          1          6
  PM25_11:00 PM25_12:00 PM25_13:00 PM25_14:00 PM25_15:00 PM25_16:00 PM25_17:00 PM25_18:00 PM25_19:00 PM25_20:00 PM25_21:00 PM25_22:00
1         28         23          3          3          5         NA          9         24         24         17         16          9
  PM25_23:00 NULLCODE_00:00 NULLCODE_01:00 NULLCODE_02:00 NULLCODE_03:00 NULLCODE_04:00 NULLCODE_05:00 NULLCODE_06:00 NULLCODE_07:00
1         15                                                                             DA             DA             DA
  NULLCODE_08:00 NULLCODE_09:00 NULLCODE_10:00 NULLCODE_11:00 NULLCODE_12:00 NULLCODE_13:00 NULLCODE_14:00 NULLCODE_15:00
1                                                                                                                              
  NULLCODE_16:00 NULLCODE_17:00 NULLCODE_18:00 NULLCODE_19:00 NULLCODE_20:00 NULLCODE_21:00 NULLCODE_22:00 NULLCODE_23:00
1             AM

补充说明

id_cols = DATE明确指定仅按DATE分组，避免其他列干扰；
values_from = c(PM25, NULLCODE)告诉pivot_wider要同时转换这两列的值；
names_glue自定义列名规则，让列名清晰对应“指标_时间”，方便后续分析。

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

R语言按小时宽表转换：避免日期重复行，保留PM25与NULLCODE

问题 #

复现数据 #

原始数据预览 #

错误的转换代码及结果 #

解决方案 #

正确转换代码 #

转换后结果预览 #

补充说明 #

问题