如何在R中将气象数据的item列转置为表头并整理时序数据

需求说明 #

导入的气象站数据格式不符合分析需求，需完成以下整理：

• 将item列中的变量名转为表头
• 将以小时为列名（00-23）的时序数据对应到各变量列下方
• 后续再处理时间格式，当前优先完成变量与数据的透视转换

初始数据 #

# 示例数据结构
weatherstation <- structure(list(date = structure(c(16436, 16436, 16436, 16436, 
16436, 16436), class = "Date"), station = c("Cailiao", "Cailiao", 
"Cailiao", "Cailiao", "Cailiao", "Cailiao"), item =  c("AMB_TEMP", 
"CO", "NO", "NO2", "NOx", "O3"), `00` = c(16, 0.74, 1, 15, 16, 
35), `01` = c(16, 0.7, 0.8, 13, 14, 36), `02` = c(15, 0.66, 1.1, 
13, 14, 35), `03` = c(15, 0.61, 1.7, 12, 13, 34), `04` = c("15", 
"0.51", "2", "11", "13", "34"), `05` = c(14, 0.51, 1.7, 13, 15, 
32), `06` = c(14, 0.51, 1.9, 13, 15, 30), `07` = c(14, 0.6, 2.4, 
16, 18, 26), `08` = c("14", "0.62", "3.4", "16", "19", "26"), 
`09` = c("15", "0.58", "3.7", "14", "18", "29"), `10` = c("14", 
"0.53", "3.5", "12", "15", "33"), `11` = c("15", "0.49", 
"3.4", "11", "15", "38"), `12` = c("15", "0.45", "3.3", "11", 
"14", "38"), `13` = c("15", "0.4", "3.1", "9.8", "13", "40"
 ), `14` = c("14", "0.4", "3.2", "11", "14", "39"), `15` =  c("13", 
"0.41", "2.5", "11", "14", "35"), `16` = c("13", "0.44", 
"2.9", "14", "17", "31"), `17` = c("13", "0.45", "2.2", "15", 
"17", "30"), `18` = c("12", "0.41", "2.3", "16", "18", "30"
), `19` = c(13, 0.42, 2.3, 18, 20, 27), `20` = c("13", "0.31", 
"1.8", "13", "15", "30"), `21` = c(13, 0.3, 1.9, 13, 15, 
28), `22` = c(13, 0.32, 2.1, 14, 16, 27), `23` = c(13, 0.33, 
1.8, 16, 17, 25)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

# 数据预览
weatherstation

预览结果：

# A tibble: 6 × 10
  date       station item      `00`  `01`  `02`  `03` `04`   `05`  `06`
  <date>     <chr>   <chr>    <dbl> <dbl> <dbl> <dbl> <chr> <dbl> <dbl>
1 2015-01-01 Cailiao AMB_TEMP 16     16   15    15    15    14    14   
2 2015-01-01 Cailiao CO        0.74   0.7  0.66  0.61 0.51   0.51  0.51
3 2015-01-01 Cailiao NO        1      0.8  1.1   1.7  2      1.7   1.9 
4 2015-01-01 Cailiao NO2      15     13   13    12    11    13    13   
5 2015-01-01 Cailiao NOx      16     14   14    13    13    15    15   
6 2015-01-01 Cailiao O3       35     36   35    34    34    32    30

目标格式 #

# A tibble: 6 × 10
  date       station hour   AMB_TEMP    CO    NO   NO2   NOx    O3  PM10
  <date>     <chr>   <time>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2015-01-01 Cailiao 00:00        16  0.74   1      15    16    35   171
2 2015-01-01 Cailiao 01:00        16  0.7    0.8    13    14    36   174
3 2015-01-01 Cailiao 02:00        15  0.66   1.1    13    14    35   160
4 2015-01-01 Cailiao 03:00        15  0.61   1.7    12    13    34   142
5 2015-01-01 Cailiao 04:00        15  0.51   2      11    13    34   123
6 2015-01-01 Cailiao 05:00        14  0.51   1.7    13    15    32   110

尝试过的方法 #

# 尝试1：错误指定values_from
weather2 <- weatherstation %>% pivot_wider(names_from = item, values_from = value)

# 尝试2：语法错误的列范围引用
weather2 <- weatherstation %>% pivot_wider(names_from = item, values_from = 00:23)

# 尝试3：直接转置导致格式混乱
t_weatherstation <- t(weatherstation)  

正确解决方案 #

使用tidyr的pivot_longer和pivot_wider组合完成转换，同时修正数据类型：

library(tidyverse)

clean_weather <- weatherstation %>%
  # 第一步：将所有小时列转成长格式，保留date、station、item作为标识列
  pivot_longer(cols = `00`:`23`, 
               names_to = "hour", 
               values_to = "reading") %>%
  # 第二步：将item列的变量名转为表头，对应reading值
  pivot_wider(names_from = item, 
              values_from = reading) %>%
  # 第三步：将字符型的数值列转成数值型（部分列因导入问题是字符）
  mutate(across(c(AMB_TEMP, CO, NO, NO2, NOx, O3), as.numeric)) %>%
  # 可选：将hour格式转为HH:00形式
  mutate(hour = str_c(hour, ":00"))

# 查看结果
clean_weather

执行后得到的结果会和目标格式一致，后续可根据需求将hour列转为时间类型。

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