Add a column in dataframe by datetime conditions of another dataframe

Question

I have the following data example.

first data:

structure(list(cycle_rounded = structure(c(1604188800, 1604190600, 
1604192400, 1604194200, 1604196000, 1604197800, 1604199600, 1604201400, 
1604203200, 1604205000, 1604206800, 1604208600, 1604210400, 1604212200, 
1604214000, 1604215800, 1604217600, 1604219400, 1604221200, 1604223000, 
1604224800, 1604226600, 1604228400, 1604230200, 1604232000, 1604233800, 
1604235600, 1604237400, 1604239200, 1604241000, 1604242800, 1604244600, 
1604246400, 1604248200, 1604250000, 1604251800, 1604253600, 1604255400, 
1604257200, 1604259000, 1604260800, 1604262600, 1604264400, 1604266200, 
1604268000, 1604269800, 1604271600, 1604273400, 1604275200, 1604277000, 
1604278800, 1604280600, 1604282400, 1604284200, 1604286000, 1604287800, 
1604289600, 1604291400, 1604293200, 1604295000, 1604296800, 1604298600, 
1604300400, 1604302200, 1604304000, 1604305800, 1604307600, 1604309400, 
1604311200, 1604313000, 1604314800, 1604316600, 1604318400, 1604320200, 
1604322000, 1604323800, 1604325600, 1604327400, 1604329200, 1604331000, 
1604332800, 1604334600, 1604336400, 1604338200, 1604340000, 1604341800, 
1604343600, 1604345400, 1604347200, 1604349000, 1604350800, 1604352600, 
1604354400, 1604356200, 1604358000, 1604359800, 1604361600, 1604363400, 
1604365200, 1604367000), tzone = "UTC", class = c("POSIXct", 
"POSIXt"))), row.names = c(NA, -100L), class = c("tbl_df", "tbl", 
"data.frame"))

second data:

structure(list(data_inicio_dia = structure(c(1604206740, 1604293080, 
1604379480), tzone = "UTC", class = c("POSIXct", "POSIXt")), 
    data_fim_dia = structure(c(1604252160, 1604338560, 1604424960
    ), tzone = "UTC", class = c("POSIXct", "POSIXt"))), row.names = c(NA, 
-3L), class = c("tbl_df", "tbl", "data.frame"))

I would to add a column daynight in the first data, where the categories will day and night. To be day the value in cycle_rounded column has to be between the date_start_day and date_end_day present in the second data otherwise it will be night.

Thanks

score 1 · Answer 1 · answered Sep 23 '22 at 20:55

This should work. Although, there might be a more elegant way.

tab1 %>% 
  dplyr::group_by(cycle_rounded) %>% 
  dplyr::summarise(
      daynight = case_when(
        cycle_rounded >= (tab2 %>% dplyr::filter(lubridate::as_date(tab2$data_inicio_dia) == lubridate::as_date(cycle_rounded)) %>% dplyr::pull(data_inicio_dia)) & 
        cycle_rounded <= (tab2 %>% dplyr::filter(lubridate::as_date(tab2$data_inicio_dia) == lubridate::as_date(cycle_rounded)) %>% dplyr::pull(data_fim_dia)) 
         ~ "Day",
         TRUE ~ "Night"
         )
    )

score 1 · Accepted Answer · answered Sep 23 '22 at 22:00

library(dplyr)
left_join(
  df1 %>% mutate(date = lubridate::as_date(cycle_rounded)),
  df2 %>% mutate(date = lubridate::as_date(data_inicio_dia))) %>%
  mutate(daynight = if_else(
    cycle_rounded %>% between(data_inicio_dia, data_fim_dia), "day", "night"))

Add a column in dataframe by datetime conditions of another dataframe

2 Answers2