r-从另一个数据帧中添加基于日期的数据

Question

我有两个数据集。一个有多个日期：

date, time
1   2013-05-01 12:43:34
2   2013-05-02 05:04:23
3   2013-05-02 09:34:34
4   2013-05-02 12:32:23
5   2013-05-03 23:23:23
6   2013-05-04 15:34:17

一个有日出和日落数据：

Sunrise                Sunset 
2013-05-01 06:43:00    2013-05-01  21:02:12
2013-05-02 06:44:00    2013-05-02  21:03:13
2013-05-03 06:44:56    2013-05-03  21:04:02
2013-05-04 06:45:32    2013-05-04  21:05:00

我想在第一个数据帧中添加一列；"天"；或"；"夜晚"；，基于来自第一数据帧的日期和时间是否在日出和日落时间和日期之间。

date, time                 Day or night
1   2013-05-01 12:43:34    Day
2   2013-05-02 05:04:23    Night
3   2013-05-02 09:34:34    Day
4   2013-05-02 12:32:23    Day
5   2013-05-03 23:23:23    Night
6   2013-05-04 15:34:17    Day

我尝试过复制和if_else函数，但行的长度不同，因为一年中我有365次日出和日落，但一天也有多次测量(总共28000行(。

有人能帮我解决我的问题吗。提前谢谢。

Answer 1

df1 <- structure(list(date_time = c("2013-05-01 12:43:34", "2013-05-02 05:04:23", 
"2013-05-02 09:34:34", "2013-05-02 12:32:23", "2013-05-03 23:23:23", 
"2013-05-04 15:34:17")), row.names = c(NA, -6L), class = c("data.frame"))
df2 <- structure(list(Sunrise = c("2013-05-01 06:43:00", "2013-05-02 06:44:00", 
"2013-05-03 06:44:56", "2013-05-04 06:45:32"), Sunset = c("2013-05-01 21:02:12", 
"2013-05-02 21:03:13", "2013-05-03 21:04:02", "2013-05-04 21:05:00"
)), row.names = c(NA, -4L), class = c("data.frame"))
# prepare df1
df1 <- df1 %>%
mutate(date_time = as.POSIXct(date_time, tz = "UTC")) %>%
mutate(Date = as.Date(date_time))
# prepare df2
df2 <- df2 %>%
mutate(Sunrise = as.POSIXct(Sunrise, tz = "UTC")) %>%
mutate(Sunset = as.POSIXct(Sunset, tz = "UTC")) %>%
mutate(Date = as.Date(Sunrise))
library(lubridate) # for the use of interval
merge(df1, df2, by = "Date") %>%
mutate(DayOrNight = ifelse(date_time %within% interval(Sunrise, Sunset), "Day", "Night"))
#         Date           date_time             Sunrise              Sunset DayOrNight
# 1 2013-05-01 2013-05-01 12:43:34 2013-05-01 06:43:00 2013-05-01 21:02:12        Day
# 2 2013-05-02 2013-05-02 05:04:23 2013-05-02 06:44:00 2013-05-02 21:03:13      Night
# 3 2013-05-02 2013-05-02 09:34:34 2013-05-02 06:44:00 2013-05-02 21:03:13        Day
# 4 2013-05-02 2013-05-02 12:32:23 2013-05-02 06:44:00 2013-05-02 21:03:13        Day
# 5 2013-05-03 2013-05-03 23:23:23 2013-05-03 06:44:56 2013-05-03 21:04:02      Night
# 6 2013-05-04 2013-05-04 15:34:17 2013-05-04 06:45:32 2013-05-04 21:05:00        Day