解决pivot_longer多类型多列合并错误及拆分列名问题

尝试用pivot_longer将带有共同前缀的多列（包含datetime和double类型）转长表，希望将列名下划线前的部分作为timepoint，下划线后的部分作为新变量名，但报错：Can't combine <datetime<UTC>> and <double>。

示例数据 #

dat <- structure(list(id = c(230.1234, 235.1234, 236.1234, 237.1234, 
262.1234, 263.1234, 290.1234, 291.1234, 301.1234, 322.1234, 323.1234, 
342.1234, 356.1234, 363.1234, 364.1234, 430.1234), a1_date = structure(c(1694995200, 
1695254400, 1695081600, 1695081600, 1699488000, 1700611200, 1701043200, 
1701216000, 1701216000, 1710201600, 1710201600, 1710892800, 1710892800, 
1712707200, 1712707200, 1719187200), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), a2_date = structure(c(NA, 1696896000, 1698624000, 
1698796800, 1702339200, 1704240000, 1704412800, 1704672000, 1705363200, 
1713830400, 1713916800, 1714435200, 1714953600, 1716336000, 1717372800, 
1722384000), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    a1_x = c(142.4434, 147.0534, 142.2234, 141.2934, 148.7234, 
    150.9034, 154.7934, 153.5634, 153.5534, 148.3134, 160.5934, 
    162.8734, 157.3034, 151.9134, 154.9334, 157.6334), a2_x = c(145.6834, 
    150.3334, 137.3134, 152.0934, 163.5034, 154.5134, 158.6834, 
    160.7234, 159.0834, 155.0434, 167.2834, 163.5534, 163.2234, 
    158.0634, 161.6634, 160.0734), a1_z = c(114.9234, 118.9834, 
    115.4534, 115.8534, 114.8634, 117.3234, 116.9834, 117.0934, 
    117.1234, 116.2734, 116.9434, 117.7334, 116.1734, 117.0834, 
    117.0934, 117.0034), a2_z = c(116.6334, 117.9334, 116.8034, 
    117.4434, 115.5534, 117.3434, 117.8734, 118.2134, 118.2534, 
    118.0134, 118.6234, 118.6034, 119.2334, 119.2734, 118.7134, 
    116.4534)), row.names = c(NA, -16L), class = "data.frame")

原数据结构 #

> head(dat)
        id    a1_date    a2_date     a1_x     a2_x     a1_z     a2_z
1 230.1234 2023-09-18       <NA> 142.4434 145.6834 114.9234 116.6334
2 235.1234 2023-09-21 2023-10-10 147.0534 150.3334 118.9834 117.9334
3 236.1234 2023-09-19 2023-10-30 142.2234 137.3134 115.4534 116.8034
4 237.1234 2023-09-19 2023-11-01 141.2934 152.0934 115.8534 117.4434
5 262.1234 2023-11-09 2023-12-12 148.7234 163.5034 114.8634 115.5534
6 263.1234 2023-11-22 2024-01-03 150.9034 154.5134 117.3234 117.3434

解决方案 #

报错原因是默认情况下pivot_longer会尝试将所有值合并到同一列，但datetime和double类型无法兼容。要实现需求，需利用names_to参数的特殊值.value，让下划线后的部分作为新的列名，同时保留各自的数据类型：

library(tidyr)

# 转长表
dat_long <- pivot_longer(
  dat,
  cols = -id,  # 保留id列，转换其他所有列
  names_sep = "_",  # 以下划线为分隔符拆分列名
  names_to = c("timepoint", ".value")  # 第一部分作为timepoint，第二部分作为值列的列名
)

# 查看结果
head(dat_long)

执行后得到的长表结构：

> head(dat_long)
# A tibble: 6 × 5
        id timepoint date                x        z
     <dbl> <chr>     <dttm>              <dbl>  <dbl>
1 230.1234 a1        2023-09-18 00:00:00 142.4434 114.9234
2 230.1234 a2        NA                  145.6834 116.6334
3 235.1234 a1        2023-09-21 00:00:00 147.0534 118.9834
4 235.1234 a2        2023-10-10 00:00:00 150.3334 117.9334
5 236.1234 a1        2023-09-19 00:00:00 142.2234 115.4534
6 236.1234 a2        2023-10-30 00:00:00 137.3134 116.8034

如果需要把timepoint中的字母去掉只保留数字，可再用dplyr处理：

library(dplyr)

dat_long <- dat_long %>%
  mutate(timepoint = sub("a", "", timepoint))

head(dat_long)

处理后的结果：

> head(dat_long)
# A tibble: 6 × 5
        id timepoint date                x        z
     <dbl> <chr>     <dttm>              <dbl>  <dbl>
1 230.1234 1         2023-09-18 00:00:00 142.4434 114.9234
2 230.1234 2         NA                  145.6834 116.6334
3 235.1234 1         2023-09-21 00:00:00 147.0534 118.9834
4 235.1234 2         2023-10-10 00:00:00 150.3334 117.9334
5 236.1234 1         2023-09-19 00:00:00 142.2234 115.4534
6 236.1234 2         2023-10-30 00:00:00 137.3134 116.8034

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