[R] How to average minutes per hour per month in the form of '# hours #minutes'
Jim Lemon
drj|m|emon @end|ng |rom gm@||@com
Fri Mar 26 12:10:15 CET 2021
Hi,
As you still seem to be asking for an answer, the following code may help.
# begin with a minimal data frame
patdb<-data.frame(patno=paste0("p",sample(100:300,200,TRUE)),
date=c(paste(2020,11,sort(sample(1:31,66,TRUE)),sep="-"),
paste(2020,12,sort(sample(1:31,67,TRUE)),sep="-"),
paste(2021,01,sort(sample(1:31,67,TRUE)),sep="-")),
consdur=sample(15:40,200,TRUE))
patdb$date<-as.Date(patdb$date,"%Y-%m-%d")
patdb$year<-format(patdb$date,"%Y")
patdb$month<-as.numeric(format(patdb$date,"%m"))
patdb$weekday<-as.numeric(format(patdb$date,"%u"))
patdb$week<-as.numeric(format(patdb$date,"%W"))
# first do the easy one
minperday<-by(patdb$consdur,patdb$date,sum)
hrperday<-minperday%/%60
minperday<-minperday%%60
# now the hard one - first get the number of days per week
daysperweek<-by(patdb$consdur,patdb$week,length)
# correct for weeks less than seven days
minperweek<-by(patdb$consdur,patdb$week,sum)*7/daysperweek
hrperweek<-minperweek%/%60
minperweek<-minperweek%%60
minpermonth<-by(patdb$consdur,patdb$month,sum)
hrpermonth<-minpermonth%/%60
minpermonth<-minpermonth%%60
daystr<-paste(names(minperday),"#",hrperday,"#",minperday)
weekstr<-paste(names(minperweek),"#",hrperweek,"#",minperweek)
monthstr<-
paste(month.name[as.numeric(names(minpermonth))],"#",
hrpermonth,"#",minpermonth)
Further enhancements to the three vectors of output strings are
possible as are various summary measures.
Jim
On Fri, Mar 26, 2021 at 6:22 PM Dr Eberhard W Lisse <nospam using lisse.na> wrote:
>
> Jeff,
>
> thank you. However, if I knew how to do this, I would probably not
> have asked :-)-O
>
> I think I have been reasonably comprehensive in describing my issue, but
> let me do it now with the real life problem:
>
> My malpractice insurance gives me a discount if I consult up to 22
> hours per week in a 3 months period.
>
> I add every patient, date and minutes whenever I see her into a MySQL
> database. I want to file the report of my hours worked with them for
> the first 3 month period (November to January and not properly quarterly
> unfortunately :-)-0), and while I can generate this with LyX/LateX and
> knitR producing a (super)tabular table containing the full list, and
> tables for time per week and time per month I really can't figure out is
> how to average the hours worked per week for each month (even if weeks
> don't align with months properly :-)-O)
>
> While I am at it how would I get this to sort properly (year, month) if
> I used the proper names of the months, ie '%Y %B' or '%B %Y'?
>
> CONSMINUTES %>%
> select(datum, dauer) %>%
> group_by(month = format(datum, '%Y %m'),
> week = format(datum, '%V')) %>%
> summarise_if(is.numeric, sum) %>%
> mutate(hm=sprintf("%d Hour%s %d Minutes", dauer %/% 60,
> ifelse((dauer %/% 60) == 1, " ", "s"), dauer %% 60)) %>%
> select(-dauer)
>
>
> Any help, or just pointers to where I can read this up, are highly
> appreciated.
>
> greetings, el
>
>
> On 2021-03-25 22:37 , Jeff Newmiller wrote:
> > This is a very unclear question. Weeks don't line up with months..
> > so you need to clarify how you would do this or at least give an
> > explicit example of input data and result data.
> >
> > On March 25, 2021 11:34:15 AM PDT, Dr Eberhard W Lisse
> <nospam using lisse.NA> wrote:
> >> Thanks, that is helpful.
> >>
> >> But, how do I group it to produce hours worked per week per month?
> >>
> >> el
> >>
> >>
> >> On 2021-03-25 19:03 , Greg Snow wrote:
> >>> Here is one approach:
> >>>
> >>> tmp <- data.frame(min=seq(0,150, by=15))
> >>>
> >>> tmp %>%
> >>> mutate(hm=sprintf("%2d Hour%s %2d Minutes",
> >>> min %/% 60, ifelse((min %/% 60) == 1, " ", "s"),
> >>> min %% 60))
> >>>
> >>> You could replace `sprintf` with `str_glue` (and update the syntax
> >>> as well) if you realy need tidyverse, but you would also loose some
> >>> formatting capability.
> >>>
> >>> I don't know of tidyverse versions of `%/%` or `%%`. If you need
> >>> the numeric values instead of a string then just remove the
> >>> `sprintf` and use mutate directly with `min %/% 60` and `min %% 60`.
> >>>
> >>> This of course assumes all of your data is in minutes (by the time
> >>> you pipe to this code) and that all hours have 60 minutes (I don't
> >>> know of any leap hours.
> >>>
> >>> On Sun, Mar 21, 2021 at 8:31 AM Dr Eberhard W Lisse <nospam using lisse.na>
> >> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> I have minutes worked by day (with some more information)
> >>>>
> >>>> which when using
> >>>>
> >>>> library(tidyverse)
> >>>> library(lubridate)
> >>>>
> >>>> run through
> >>>>
> >>>> CONSMINUTES %>%
> >>>> select(datum, dauer) %>%
> >>>> arrange(desc(datum))
> >>>>
> >>>> look somewhat like
> >>>>
> >>>> # A tibble: 142 x 2
> >>>> datum dauer
> >>>> <date> <int>
> >>>> 1 2021-03-18 30
> >>>> 2 2021-03-17 30
> >>>> 3 2021-03-16 30
> >>>> 4 2021-03-16 30
> >>>> 5 2021-03-16 30
> >>>> 6 2021-03-16 30
> >>>> 7 2021-03-11 30
> >>>> 8 2021-03-11 30
> >>>> 9 2021-03-11 30
> >>>> 10 2021-03-11 30
> >>>> # … with 132 more rows
> >>>>
> >>>> I can extract minutes per hour
> >>>>
> >>>> CONSMINUTES %>%
> >>>> select(datum, dauer) %>%
> >>>> group_by(week = format(datum, '%Y %V'))%>%
> >>>> summarise_if(is.numeric, sum)
> >>>>
> >>>> and minutes per month
> >>>>
> >>>> CONSMINUTES %>%
> >>>> select(datum, dauer) %>%
> >>>> group_by(month = format(datum, '%Y %m'))%>%
> >>>> summarise_if(is.numeric, sum)
> >>>>
> >>>> I need to show the time worked per week per month in the format of
> >>>>
> >>>> '# hours # minutes'
> >>>>
> >>>> and would like to also be able to show the average time per week
> >>>> per month.
> >>>>
> >>>> How can I do that (preferably with tidyverse :-)-O)?
> >>>>
> >>>> greetings, el
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
More information about the R-help
mailing list