CODEWITHSUNDEEP
r
Alexis
Alexis

Reputation: 2294

Convert time to fraction of a day, fraction of a week and fraction of a month in R

I have this dataset with times:

id    time
1     4/01/2019 08:00:00
2     4/02/2019 12:00:00
3     4/03/2019 18:00:00

And I want the fraction of a day, fraction of the week and fraction of the month. For example for the first row 08:00:00 is one third of a day, so first column should be 0.333. And it was Monday so it should be 0.047 (a complete day is 1/7 = 0.143 of a week, but since it's a third then 0.143 * 0.333 = 0.047). And it was the start of the month so it should be 0.011 (a complete day is 1/30 = 0.033 of a month, but it is only 8:am so it is 0.033 * 0.333 = 0.011.

The expected result should be:

id    time                 frac_day    frac_week    frac_month
1     4/01/2019 08:00:00   0.333       0.047        0.011
2     4/02/2019 12:00:00   0.5         0.214        0.050
3     4/03/2019 18:00:00   0.75        0.393        0.092

Please, could you help me with this question? Any help will be greatly appreciated.

Upvotes: 0

Views: 917

Answers (2)

kwes
kwes

Reputation: 433

Just for kicks here is a solution in base R

# numDays from https://stackoverflow.com/a/12977662/5598560
numDays <- function(month,year){
  as.numeric(strftime(as.Date(paste(year+month%/%12,month%%12+1,"01",sep="-"))-1,"%d"))
}
dat$t = as.POSIXlt(dat$time,  format="%m/%d/%Y %H:%M:%S")
dat$frac_day <- with(dat, (t$sec + t$min*60 + t$hour*3600) / (24*3600))
dat$frac_week <- with(dat, (t$sec + t$min*60 + t$hour*3600 + (t$wday-1)*24*3600) / (24*3600*7))
dat$frac_month <- with(dat, (t$sec + t$min*60 + t$hour*3600 + (t$mday-1)*24*3600) / (numDays(t$mon+1,t$year)*24*3600))

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 388962

lubridate functions will be helpful here -

library(dplyr)
library(lubridate)

df %>%
  mutate(time = mdy_hms(time), 
         frac_day = hour(time)/24,
         frac_week = (lubridate::wday(time, week_start = 1) - 1 + frac_day)/7, 
         frac_month = (day(time) - 1 + frac_day)/days_in_month(time))

#  id                time  frac_day  frac_week frac_month
#1  1 2019-04-01 08:00:00 0.3333333 0.04761905 0.01111111
#2  2 2019-04-02 12:00:00 0.5000000 0.21428571 0.05000000
#3  3 2019-04-03 18:00:00 0.7500000 0.39285714 0.09166667

Upvotes: 1

Related Questions