How to average minutes per hour per month in the form of '# hours #minutes'
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 at 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 at 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 at 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 at 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.