I have two datasets. One with multiple dates:
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
and one with sunrise and sunsets data:
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
I want to add a column to the first dataframe with either "Day" or "night", based on whether the date and time from the first dataframe is between the sunrise and sunset time and dates.
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
I tried copying and if_else functions, but the length of rows is different because for one year I have 365 sunrises and sunsets but I've also got multiple measurements for one day (total of 28000 rows).
Can anyone help me with my problem. Thanks in advance.
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