使用R的pivot_longer函数时遇到列类型不兼容问题求助

云原生阿岚

2026-7-5

问题

我有一个记录研究助理每日工作时长的dataframe，结构如下：

structure(list(SurveyorId = c("Zineb", "Elisa", "AudreyB", "CamilleP", 
"CamilleV", "AudreyG", "CharlesE", "Lou", "Ludivine", "Elise", 
"Jason", "Mathilde", "Hassina", "Nihel", "Dauphine"), `16/10/2023` = c(8, 
7, 3, 7, 6, 4, NA, 2.5, NA, 7, NA, NA, 4, NA, NA), `17/10/2023` = c(8, 
7, 5.5, 4, 2.5, 4.5, NA, 2, NA, 4, NA, 5.5, 7.5, NA, NA), `18/10/2023` = c(8, 
5, 7, NA, NA, 2.5, NA, NA, 2, NA, NA, 8, 7.5, NA, NA), `19/10/2023` = c(8, 
7, NA, NA, NA, NA, NA, NA, 1.5, NA, NA, 2.5, 9, NA, NA), `20/10/2023` = c(8, 
NA, 7.5, NA, NA, 2, NA, 2, NA, NA, 2.2, NA, 3.5, NA, NA), `21/10/2023` = c(NA, 
NA, NA, 6, 6, NA, 7.5, 5, 4.5, 4, 4.5, NA, NA, NA, NA), `23/10/2023` = c(8, 
9, NA, 7.5, 7, 4, NA, 1, NA, 7.5, NA, 2.5, 4, NA, NA), `24/10/2023` = c(8, 
NA, NA, 4.5, 2, 4.5, 8, 3.75, NA, NA, NA, 5.5, 7.5, NA, NA), 
    `25/10/2023` = c(8, 5, NA, NA, NA, 2, 1.5, NA, 1.5, NA, NA, 
    4, 8, NA, NA), `26/10/2023` = c(8, 8, 8, NA, NA, NA, NA, 
    1, 2, 7, 4, NA, 8, NA, NA), `27/10/2023` = c(8, NA, 4, NA, 
    NA, 2, NA, NA, 2, NA, NA, NA, 2.75, NA, NA), `28/10/2023` = c(NA, 
    NA, NA, 7.5, 6, NA, 8, 6, 6.5, 4, NA, 3, NA, NA, NA), `30/10/2023` = c(8, 
    9, 6, 2, NA, 4, NA, 1, 1.5, 7, NA, NA, 4.75, 3, NA), `31/10/2023` = c(8, 
    NA, 4, 2, NA, 4, 8, 2.5, 0.5, 0, NA, 3, 8.5, 3.5, NA), `01/11/2023` = c(NA, 
    5, NA, NA, NA, 2, NA, NA, NA, 7, NA, 3, NA, 3, NA), `02/11/2023` = c(3, 
    6.5, 4, NA, NA, NA, NA, 2, 2, NA, 3.5, 3, 8, 5.4, NA), `03/11/2023` = c(8, 
    NA, NA, NA, NA, NA, NA, NA, 2, NA, 2, 4.5, 3, 3.5, NA), `04/11/2023` = c(1, 
    NA, NA, NA, 7, NA, NA, 4.5, NA, NA, 3.75, NA, NA, 2.4, 7), 
    `06/11/2023` = c(8, 7, 2, 7, 6, 4, NA, 2, NA, 5, NA, 5, 4.6, 
    NA, 7), `07/11/2023` = c(7.5, NA, 0.5, 4, 3.5, 4, 8, 4, NA, 
    NA, NA, 4, 8, NA, 7), `08/11/2023` = c(7.5, 6.5, 2, NA, NA, 
    NA, 2, NA, 3, NA, NA, NA, 8.15, NA, 7), `09/11/2023` = c("7.5", 
    "5.5", NA, NA, NA, NA, NA, "2", "2", "7", "3.2", "5", "9", 
    NA, "5.5"), `10/11/2023` = c(9, NA, 1.5, NA, NA, NA, NA, 
    NA, 2.5, NA, 2, NA, 3.5, NA, NA), `11/11/2023` = c(NA, NA, 
    NA, 7, 7, NA, 7, NA, NA, 4, 3.2, NA, NA, 5, 6), `13/11/2023` = c("7.5", 
    NA, "2", NA, "7", "3", NA, NA, NA, "5.5", NA, "4.5", "4.25", 
    NA, NA), `14/11/2023` = c(8, NA, NA, NA, 2, NA, 6.5, 4.5, 
    NA, NA, NA, 3.5, 8.5, NA, NA), `15/11/2023` = c(NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `16/11/2023` = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    `17/11/2023` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -15L))

我想用pivot_longer将其转为长表，让姓名、日期、工作时长各占一列，代码如下：

transposed_data <- Recensement_volume_horaire %>%
  pivot_longer(cols = -"SurveyorId", names_to = "Date", values_to = "Hours_Worked")

运行时出现错误：

Error in pivot_longer():
! Can't combine `16/10/2023` <double> and `09/11/2023` <character>.
Backtrace:
 1. Recensement_volume_horaire %>% ...
 3. tidyr:::pivot_longer.data.frame(., cols = -"SurveyorId", names_to = "Date", values_to = "Hours_Worked")

我尝试修改列名格式（如colnames(Recensement_volume_horaire) <- as.character(colnames(Recensement_volume_horaire))），但无法解决问题，恳请帮助！

解决方案

错误根源是部分日期列（比如09/11/2023、13/11/2023）为字符型，其余日期列为数值型，pivot_longer无法将不同类型的列合并到同一字段中。需要先统一所有日期列的类型，再执行宽表转长表操作。

可以用dplyr::mutate()结合across()批量处理：

library(dplyr)
library(tidyr)

# 第一步：将所有日期列转为数值型
cleaned_data <- Recensement_volume_horaire %>%
  mutate(across(-SurveyorId, ~ as.numeric(.x)))

# 第二步：执行pivot_longer转长表
transposed_data <- cleaned_data %>%
  pivot_longer(cols = -SurveyorId, names_to = "Date", values_to = "Hours_Worked")

说明

across(-SurveyorId, ~ as.numeric(.x))：对除SurveyorId外的所有列应用as.numeric()转换，字符格式的数字会自动转为数值，NA值保持不变。
类型统一后，pivot_longer就能正常合并所有工作时长列，生成包含SurveyorId、Date、Hours_Worked三列的长表。

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

使用R的pivot_longer函数时遇到列类型不兼容问题求助

说明 #

说明