0

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.

Alexis
  • 2,104
  • 2
  • 19
  • 40
  • Hello @RonakShah, for second row it's 4/02/2019, only one complete day has passed, but the second day is incomplete (only half) so I multiply 1.5 * 1/7 = 0.214 for frac_week, and 1.5 * 1/30 = 0.05 – Alexis May 20 '21 at 03:24

2 Answers2

2

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))
kwes
  • 434
  • 2
  • 7
  • Thanks @kwes for the answer, just a question, what is the function of '%%' operator? Is it like the remainder? – Alexis May 20 '21 at 04:14
  • 1
    Yes, `%%` is the modulus. In the `numDays` usage it returns the next month and wraps to 1 when the supplied month is 12. – kwes May 20 '21 at 04:53
1

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
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Hello @RonakShah, thank you for your time and answer but in the case of frac_week and frac_month I count the days and its fractions. For example for second row it's 4/02/2019, only one complete day has passed, but the second day is incomplete (only half) so I multiply 1.5 * 1/7 = 0.214 for frac_week, and 1.5 * 1/30 = 0.05 – Alexis May 20 '21 at 03:26
  • 1
    Corrected my answer accordingly @Alexis – Ronak Shah May 20 '21 at 03:33
  • Lubridate package is really helpful!, now I understand the hour function for example. Thank you very much for your answer @Ronak Shah. Have a great day! – Alexis May 20 '21 at 04:12