Create a new column and fill with if() function - time intervals

tidyverse
, ,
1

Hello all,
Sorry for the confused topic title
So the better is to show you a sample of my df :

Station  sampling_time   sunrise_time  sunset_time
A1         11:18:32       00:44:15       16:25:37
A2         05:01:26       00:44:15       16:25:37
A3         20:50:11       00:44:15       16:25:37
A4         08:10:42       00:44:15       16:25:37

I had a df with my station and the samling time. With the package suncalc, I was able to have the sunrise/sunset data
So now, what I want is to create a new column "Period" where we can have "day" if the sampling time is not the sunrise-sunset interval time and "night" if it's inside.

How can I do it ?

Thanks a lot
Hersh

2

Try lubridate::interval(). You might need a date_time rather than just time though. Also next time provide a reproducible example.

2 Likes
3

Thanks for your response !
Of course, here is a reproducible example of ma df :

df <- tribble(
    ~station, ~sampling_date, ~sampling_time, ~sunrise_date, ~sunrise_time, ~sunset_date, ~sunset_time,
   A1, 2021-07-21, 11:18:32, 2021-07-21, 00:44:15, 2021-07-21, 16:25:37
   A2, 2021-10-17, 12:08:14, 2021-10-17, 06:47:01, 2021-10-17, 12:40:50
   A3, 2021-09-05, 20:23:45, 2021-09-05, 01:22:14, 2021-09-05, 18:46:53
   A4, 2021-10-03, 06:31:24, 2021-10-03, 07:44:39, 2021-10-03, 18:06:52
4

I did this :

#Get the sunrise/sunset date_time data
start.date = "20210720"; end.date = "20211025"
Dates <- seq(ymd(start.date),ymd(end.date), by = "days")

sun_df <- expand.grid(Dates = Dates, Station = CTD_max_depth$Station) %>% 
  left_join(CTD_max_depth) %>%
  group_by(Dates, Station, lat, lon) %>% 
  mutate(sunrise = getSunlightTimes(Dates,lat,lon,tz = "MST")$sunrise,
         sunset = getSunlightTimes(Dates,lat,lon,tz = "MST")$sunset)

sun_df$sunrise <- as.POSIXct(sun_df$sunrise)
sun_df$sunset <- as.POSIXct(sun_df$sunset)

#Merge the date and the time of my station to have the same format than sunrise/sunset
sun_df$date_time <- paste(sun_df$date,sun_df$time,sep=" ")
sun_df$date_time <- as.POSIXct(sun_df$date_time)

#Create the new column with day/night 
sun_df$Civil_Twilight_Period <- rep(" ", length.out=nrow(sun_df))
sun_df$Civil_Twilight_Period[which(sun_df$date_time>sun_df$sunrise & sun_df$date_time<sun_df$sunset)]<-"day"
sun_df$Civil_Twilight_Period[which(sun_df$Civil_Twilight_Period==" ")]<-"night"

But I have some mistakes when I check the new df..
Like some one station I have
sunrise : 2021-07-26 00:29:42
sunset : 2021-07-26 17:54:58
station : 2021-07-26 01:30:55
period : night
But it's suppose to be day right ?

Thanks

5

Something like this?

library(tidyverse)
library(lubridate)

# fixed up the format here
df <- tribble(
  ~station, ~sampling_date, ~sampling_time, ~sunrise_date, ~sunrise_time, ~sunset_date, ~sunset_time,
  "A1", "2021-07-21", "11:18:32", "2021-07-21", "00:44:15", "2021-07-21", "16:25:37",
  "A2", "2021-10-17", "12:08:14", "2021-10-17", "06:47:01", "2021-10-17", "12:40:50",
  "A3", "2021-09-05", "20:23:45", "2021-09-05", "01:22:14", "2021-09-05", "18:46:53",
  "A4", "2021-10-03", "06:31:24", "2021-10-03", "07:44:39", "2021-10-03", "18:06:52")


df %>% 
  mutate(sampling_date_time = ymd_hms(paste(sampling_date, sampling_time)),
         sunrise_date_time = ymd_hms(paste(sunrise_date, sunrise_time)),
         sunset_date_time = ymd_hms(paste(sunset_date, sunset_time))) %>% 
  select(station, sampling_date_time, sunrise_date_time, sunset_date_time) %>% 
  mutate(period = if_else(sampling_date_time %within% interval(sunrise_date_time, sunset_date_time), "day", "night"))

# A tibble: 4 x 5
  station sampling_date_time  sunrise_date_time   sunset_date_time    period
  <chr>   <dttm>              <dttm>              <dttm>              <chr> 
1 A1      2021-07-21 11:18:32 2021-07-21 00:44:15 2021-07-21 16:25:37 day   
2 A2      2021-10-17 12:08:14 2021-10-17 06:47:01 2021-10-17 12:40:50 day   
3 A3      2021-09-05 20:23:45 2021-09-05 01:22:14 2021-09-05 18:46:53 night 
4 A4      2021-10-03 06:31:24 2021-10-03 07:44:39 2021-10-03 18:06:52 night
1 Like
6

It works for some station like my last code but I don't why sometime it's just false..
Like I have

sampling_date_time   sunrise             sunset             period
2021-07-26 01:30:55  2021-07-26 00:29:42 2021-07-26 17:54:58 night

But if we have the sunrise at 00:29:49 and the sunset at 17:54:58, it's suppose to be the day at 01:30:55.. But it's written night and it's like that some some others station and I just don't know why..

Maybe I made a error at the beginning to get my sunrise/sunset data from "suncalc"..

7

It is in A5 below. It comes out as 'day' for me.

library(tidyverse)
library(lubridate)

# fixed up the format here
df <- tribble(
  ~station, ~sampling_date, ~sampling_time, ~sunrise_date, ~sunrise_time, ~sunset_date, ~sunset_time,
  "A1", "2021-07-21", "11:18:32", "2021-07-21", "00:44:15", "2021-07-21", "16:25:37",
  "A2", "2021-10-17", "12:08:14", "2021-10-17", "06:47:01", "2021-10-17", "12:40:50",
  "A3", "2021-09-05", "20:23:45", "2021-09-05", "01:22:14", "2021-09-05", "18:46:53",
  "A4", "2021-10-03", "06:31:24", "2021-10-03", "07:44:39", "2021-10-03", "18:06:52",
  "A5", "2021-07-26", "01:30:55",  "2021-07-26", "00:29:42", "2021-07-26", "17:54:58")


df %>% 
  mutate(sampling_date_time = ymd_hms(paste(sampling_date, sampling_time)),
         sunrise_date_time = ymd_hms(paste(sunrise_date, sunrise_time)),
         sunset_date_time = ymd_hms(paste(sunset_date, sunset_time))) %>% 
  select(station, sampling_date_time, sunrise_date_time, sunset_date_time) %>% 
  mutate(period = if_else(sampling_date_time %within% interval(sunrise_date_time, sunset_date_time), "day", "night"))

# A tibble: 5 x 5
  station sampling_date_time  sunrise_date_time   sunset_date_time    period
  <chr>   <dttm>              <dttm>              <dttm>              <chr> 
1 A1      2021-07-21 11:18:32 2021-07-21 00:44:15 2021-07-21 16:25:37 day   
2 A2      2021-10-17 12:08:14 2021-10-17 06:47:01 2021-10-17 12:40:50 day   
3 A3      2021-09-05 20:23:45 2021-09-05 01:22:14 2021-09-05 18:46:53 night 
4 A4      2021-10-03 06:31:24 2021-10-03 07:44:39 2021-10-03 18:06:52 night 
5 A5      2021-07-26 01:30:55 2021-07-26 00:29:42 2021-07-26 17:54:58 day
8

This is not reproducible. What is CTD_max_depth$Station? What are the latitude and longitude values? Same with your date samples. Please make sure you've got the details in there as you'll more likely get an answer and it will save the person trying to answer your question some time.

1 Like
9

Sorry about that..
At the begginning I just had my station with the sampling date and time

df <- tribble(
  ~station, ~sampling_date, ~sampling_time, ~lat, ~lon
  "A1", "2021-07-21", "11:18:32", 55.5, -58.9
  "A2", "2021-10-17", "12:08:14", 60.5, -61.3
  "A3", "2021-09-05", "20:23:45", 66.8, -58.8
  "A4", "2021-10-03", "06:31:24", 67.5, -63.7 
  "A5", "2021-07-26", "01:30:55", 66.8, -58.8)

So to have my sunrise/sunset I did this :

start.date = "20210721"; end.date = "20211017"
Dates <- seq(ymd(start.date),ymd(end.date), by = "days")
sun_df <- expand.grid(Dates = Dates, Station =df$Station) %>% 
  left_join(df) %>%
  group_by(Station, Dates, lat, lon) %>% 
  mutate(sunrise = getSunlightTimes(Dates,lat,lon,tz = "MST")$sunrise,
         sunset = getSunlightTimes(Dates,lat,lon,tz = "MST")$sunset)

With this code, I have a big df with the sunrise/sunset data since start.date from end.date
With a filter I just select my dates according to my station from my df to have this :

sun_df <- tribble(
  ~station, ~sampling_date, ~sampling_time, ~lat, ~lon, ~sunrise, ~sunset
  "A1", "2021-07-21", "11:18:32", 55.5, -58.9, 2021-07-21 00:44:31, 2021-07-21 17:22:04
  "A2", "2021-10-17", "12:08:14", 60.5, -61.3, 2021-10-17 03:31:38, 2021-10-17 13:52:47
  "A3", "2021-09-05", "20:23:45", 66.8, -58.8, 2021-09-05 02:10:29, 2021-09-05 15:41:20
  "A4", "2021-10-03", "06:31:24", 67.5, -63.7, 2021-10-03 03:03:50, 2021-10-03 14:28:02
  "A5", "2021-07-26", "01:30:55", 66.8, -58.8, 2021-07-26 00:52:58, 2021-07-26 17:13:54)

After I just applied your code but I have the mistake I shown you..

10

Ok I think I figured it out..
I just did :

sun_df <- sun_df %>% separate(sunrise, c("sunrise_date","sunrise_time"), " ") 
sun_df <- sun_df %>% separate(sunset, c("sunset_date","sunset_time"), " ")

To after be able to use the mutate part of your code :

mutate(sampling_date_time = ymd_hms(paste(date, time)),
         sunrise_date_time = ymd_hms(paste(sunrise_date, sunrise_time)),
         sunset_date_time = ymd_hms(paste(sunset_date, sunset_time)))

Thank you very much for your time and your patience !

1 Like
11

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.