基于Datetime合并R数据框时时间偏移问题求助

问题概述 #

在R中基于datetime字段合并chipRTmerge和CT_BallChase两个数据框时，chipRTmerge的时间出现12小时偏移（如2023-01-30 17:28:55显示为2023-01-30 07:28:55），导致数据无法按预期匹配整合。

数据结构 #

chipRTmerge数据框示例及结构 #

# 数据示例
              Datetime RecTemp IMChipTemp IMChip_Date IMChip_Time
1  2023-01-30 17:28:55    38.5       39.7 2023-01-30    17:28:55
2  2023-01-30 17:35:37    41.2       42.9 2023-01-30    17:35:37
3  2023-01-30 17:37:06    41.2       42.5 2023-01-30    17:37:06
4  2023-01-30 17:38:27    40.9       42.2 2023-01-30    17:38:27
5  2023-01-30 17:39:23    41.8       42.0 2023-01-30    17:39:23
6  2023-01-30 17:40:07    41.1       41.9 2023-01-30    17:40:07
7  2023-01-30 17:40:46    41.4       41.8 2023-01-30    17:40:46
8  2023-01-30 17:41:38    41.1       41.7 2023-01-30    17:41:38
9  2023-01-30 17:43:24    41.1       41.6 2023-01-30    17:43:24
10 2023-01-30 17:43:25    41.4       41.6 2023-01-30    17:43:25

# 结构信息
> str(chipRTmerge)
'data.frame':     34 obs. of  5 variables:
 $ datetime   : POSIXct, format: "2023-01-30 17:28:55" "2023-01-30 17:35:37" ...
 $ RecTemp    : num  38.5 41.2 41.2 40.9 41.8 41.1 41.4 41.1 41.1 41.4 ...
 $ IMChip_Temp: num  39.7 42.9 42.5 42.2 42 41.9 41.8 41.7 41.6 41.6 ...
 $ IMChip_Date: POSIXct, format: "2023-01-30" "2023-01-30" ...
 $ IMChip_Time: chr  "17:28:55" "17:35:37" "17:37:06" "17:38:27" ...

CT_BallChase数据框示例及结构 #

# 数据示例
              Datetime CorTemp
149 2023-01-30 17:35:09   40.39
150 2023-01-30 17:35:19   40.44
151 2023-01-30 17:35:29   40.51
152 2023-01-30 17:35:39   40.57
153 2023-01-30 17:35:49   40.63
154 2023-01-30 17:35:59   40.69
155 2023-01-30 17:36:09   40.74
156 2023-01-30 17:36:19   40.81
157 2023-01-30 17:36:29   40.86
158 2023-01-30 17:36:39   40.91
159 2023-01-30 17:36:49   40.95
160 2023-01-30 17:36:59   40.99
161 2023-01-30 17:37:09   41.04
162 2023-01-30 17:37:19   41.07
163 2023-01-30 17:37:29   41.10
164 2023-01-30 17:37:49   41.16
165 2023-01-30 17:37:59   41.19
166 2023-01-30 17:38:09   41.20

# 结构信息
tibble [429 × 2] (S3: tbl_df/tbl/data.frame)
 $ datetime: POSIXct[1:429], format: "2023-01-30 17:10:19" "2023-01-30 17:10:29" ...
 $ CorTemp : num [1:429] 38.3 38.3 38.3 38.3 38.3 ...

合并代码 #

mergefile_CTIMCHIPRT_BC <- merge(CT_BallChase, chipRTmerge, by="datetime", all=T)

当前错误输出 #

datetime CorTemp RecTemp IMChip_Temp
1 2023-01-30 07:28:55      NA    38.5       39.7
2 2023-01-30 07:35:37      NA    41.2       42.9
3 2023-01-30 07:37:06      NA    41.2       42.5
4 2023-01-30 07:38:27      NA    40.9       42.2
5 2023-01-30 07:39:23      NA    41.8       42.0
6 2023-01-30 07:40:07      NA    41.1       41.9
7 2023-01-30 07:40:46      NA    41.4       41.8
8 2023-01-30 07:41:38      NA    41.1       41.7

期望输出 #

Datetime CorTemp RecTemp IMChip_Temp
1  2023-01-30 17:35:09   40.39      NA          NA
2  2023-01-30 17:35:19   40.44      NA          NA
3  2023-01-30 17:35:29   40.51      NA          NA
4  2023-01-30 17:35:37      NA    41.2       42.9
5  2023-01-30 17:35:39   40.57      NA          NA
6  2023-01-30 17:35:49   40.63      NA          NA
7  2023-01-30 17:35:59   40.69      NA          NA
8  2023-01-30 17:36:09   40.74      NA          NA
9  2023-01-30 17:36:19   40.81      NA          NA
10 2023-01-30 17:36:29   40.86      NA          NA
11 2023-01-30 17:36:39   40.91      NA          NA
12 2023-01-30 17:36:49   40.95      NA          NA
13 2023-01-30 17:36:59   40.99      NA          NA
14 2023-01-30 17:37:06      NA    41.2       42.5
15 2023-01-30 17:37:09   41.04      NA          NA
16 2023-01-30 17:37:19   41.07      NA          NA
17 2023-01-30 17:37:29   41.10      NA          NA
18 2023-01-30 17:37:49   41.16      NA          NA
19 2023-01-30 17:37:59   41.19      NA          NA
20 2023-01-30 17:38:09   41.20      NA          NA
21 2023-01-30 17:38:27      NA    40.9       42.2

解决方案 #

问题原因 #

两个数据框的datetime字段均为POSIXct类型，但时区不一致。R在处理不同时区的POSIXct时间时，会自动转换为当前会话时区显示，导致时间看似偏移12小时，实际是时区转换后的显示差异，合并时底层时间戳不匹配，因此无法正确整合。

解决步骤 #

1. 检查时区：确认两个数据框datetime字段的时区

# 查看chipRTmerge的datetime时区
attr(chipRTmerge$datetime, "tzone")
# 查看CT_BallChase的datetime时区
attr(CT_BallChase$datetime, "tzone")

2. 统一时区：将两个数据框的datetime字段设置为相同时区（比如本地时区或UTC）

# 示例：统一为上海时区，可根据实际调整为你的本地时区
chipRTmerge$datetime <- as.POSIXct(chipRTmerge$datetime, tz = "Asia/Shanghai")
CT_BallChase$datetime <- as.POSIXct(CT_BallChase$datetime, tz = "Asia/Shanghai")

# 或者统一为UTC时区
# chipRTmerge$datetime <- as.POSIXct(chipRTmerge$datetime, tz = "UTC")
# CT_BallChase$datetime <- as.POSIXct(CT_BallChase$datetime, tz = "UTC")

3. 重新合并并排序：执行合并后按时间排序，匹配期望输出的顺序

# 合并数据框
mergefile_CTIMCHIPRT_BC <- merge(CT_BallChase, chipRTmerge, by="datetime", all=TRUE)
# 按时间排序
mergefile_CTIMCHIPRT_BC <- mergefile_CTIMCHIPRT_BC[order(mergefile_CTIMCHIPRT_BC$datetime), ]

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