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 <nos...@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 > <nos...@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 <nos...@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@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. ______________________________________________ R-help@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.