Skip to content
Advertisement

Adding data based on date from another dataframe

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.

Advertisement

Answer

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
Advertisement