0

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.

Koen
  • 3
  • 2
  • 3
    Welcome to Stack Overflow. Please don’t use images of data as they cannot be used without a lot of unnecessary effort. [For multiple reasons](//meta.stackoverflow.com/q/285551). You’re more likely to get a positive response if your question is reproducible. [See Stack Overflow question guidance](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Peter Feb 04 '22 at 11:19

1 Answers1

0
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
Merijn van Tilborg
  • 5,452
  • 1
  • 7
  • 22