如何在R中将多个气象数据Excel文件合并为单个数据框

核心问题修正点 #

• 原代码未将时间列转换为可排序的日期时间格式，导致合并后数据顺序混乱
• 读取CSV时未处理逗号作为小数分隔符的问题，会导致温度/湿度数值被识别为字符型
• Excel路径中的反斜杠需要转义（用\\）或改用正斜杠，否则会报错
• 合并后需按时间列排序，同时保留重复时间的所有记录

完整修正代码 #

# 安装并加载所需包
# install.packages(c("openxlsx", "tidyverse"))
library(openxlsx)
library(tidyverse)

# 读取数据：设置dec=","处理逗号小数，header=FALSE匹配无表头的数据源
C_1_Maio <- read.csv(
  "D:/Carreira Acadêmica/Doutorado - UB/Projetos/HoliSoils/Areas de Estudo/Cadiretes/Dados/Sensores/2022-05-11/C-1.csv",
  header = FALSE, sep = ";", dec = ","
)

C_1_Junho <- read.csv(
  "D:/Carreira Acadêmica/Doutorado - UB/Projetos/HoliSoils/Areas de Estudo/Cadiretes/Dados/Sensores/2022-06-29/C-1.csv",
  header = FALSE, sep = ";", dec = ","
)

# 合并数据并处理时间列
C1_cad <- bind_rows(C_1_Maio, C_1_Junho) %>%
  # 将V2列的字符串转换为日期时间格式（适配"2021.07.02 11:00"格式）
  mutate(datetime = dmy_hm(V2, locale = locale(date_names = "pt"))) %>%
  # 按时间排序，保留所有重复时间的记录
  arrange(datetime)

# 拆分各变量数据集（重命名列让导出结果更直观）
cad_T1_C1 <- C1_cad %>% select(datetime, T1 = V4)
cad_T2_C1 <- C1_cad %>% select(datetime, T2 = V5)
cad_T3_C1 <- C1_cad %>% select(datetime, T3 = V6)
cad_H_C1 <- C1_cad %>% select(datetime, Moisture = V7)

# 导出到Excel的不同工作表
output_path <- "C:/Users/Lenovo/Desktop/Holisoils data/C1_TM_Cadiretes.xlsx"
write.xlsx(
  list(T1 = cad_T1_C1, T2 = cad_T2_C1, T3 = cad_T3_C1, Moisture = cad_H_C1),
  file = output_path,
  overwrite = TRUE
)

扩展优化：批量读取C1的所有月份文件 #

如果后续要处理更多月份（比如C1_Julho等），可以用批量读取简化代码：

# 列出C1所有数据文件的路径
c1_file_paths <- c(
  "D:/Carreira Acadêmica/Doutorado - UB/Projetos/HoliSoils/Areas de Estudo/Cadiretes/Dados/Sensores/2022-05-11/C-1.csv",
  "D:/Carreira Acadêmica/Doutorado - UB/Projetos/HoliSoils/Areas de Estudo/Cadiretes/Dados/Sensores/2022-06-29/C-1.csv",
  "D:/Carreira Acadêmica/Doutorado - UB/Projetos/HoliSoils/Areas de Estudo/Cadiretes/Dados/Sensores/2022-07-XX/C-1.csv" # 补充其他月份文件路径
)

# 批量读取并合并所有文件
C1_cad <- map_dfr(c1_file_paths, ~read.csv(.x, header=FALSE, sep=";", dec=",")) %>%
  mutate(datetime = dmy_hm(V2, locale = locale(date_names = "pt"))) %>%
  arrange(datetime)

关键说明 #

• dmy_hm函数来自lubridate（tidyverse包含该包），用于将字符串转换为日期时间，locale="pt"适配葡萄牙语日期格式
• bind_rows比rbind更灵活，支持不同列数的数据集合并
• select重命名列（比如V4改为T1），让导出的Excel内容更易读
• 保留重复时间记录：arrange仅做排序操作，不会删除任何重复时间的行

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