#1

Hi, I have difficulty to find total hours of a particular role type. Explanation is below:

Shift# Role type Working hours
1 Nurse 3
1 Nurse 2
1 Nurse 5
1 MD 2
1 PA 7
2 Nurse 3
2 Nurse 7
2 MD 4
2 MD 2
3 Nurse 6
3 Nurse 3
3 MD 2
3 PA 1
.
.
.
250

Shift # represent number of shifts (9 am to 5 pm) for one year, role type represents the type of a staff (e.g. a nurse, MD, etc) that comes during a shift and I want my data frame to be like the following for 250 shift:

Shift # Nurse MD PA
1 10 2 7
2 10 6 0
3 9 2 1
.
.
.
250
in the above examples, I sum number of hours for each role type for each shift.

#3

Hi Alrezq,

The below code should help you. Let me know if you face any issues. Install dplyr and tidyr packages without fail before executing this code.

  1. In this code, I have also put up a sample data set similar to what you have shared. This section of the code will not be required during the actual implementation. I have enclosed these lines between two comments #Start and #End. But as requested by @Yarnabrina you need to share a reproducible example for faster turn-around from the experts
  2. In order to achieve the desired output, we need to first group the data by shift and then by role and then add the working hours
  3. Next, to achieve the tabular format we use the spread function from tidyr package and render the output as a data frame using the data.frame() function
  4. I have also added a line to export the data to your system (using write.csv()) assuming you might have to further the output to another team/vertical within the organization for processing
library(dplyr)
library(tidyr)

#Start
shift<-c(1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3)
role<-c("Nurse","Nurse","Nurse","Nurse","Nurse","MD","MD","PA","Nurse","Nurse","MD","MD","PA","PA","Nurse","Nurse")
working_hours<-c(3,2,5,7,3,7,2,5,7,3,2,5,7,6,8,3)
DF<-
  do.call("cbind",list(shift,role,working_hours)) %>% 
  data.frame() %>%
  rename("shift"="X1","role"="X2","working_hours"="X3") %>%
  mutate("working_hours"=as.numeric(working_hours)) %>%
#End
  group_by(shift,role) %>%
  summarize("total_working_hours"=sum(working_hours,na.rm=TRUE)) %>%
  spread(key=role,value=total_working_hours) %>%
  ungroup() %>%
  data.frame()

print(DF)

write.csv(DF,"working_hours_by_role.csv")
print(paste0("You file was Saved at :: ",getwd()))

Below is how the output of this code looks like:

  shift MD Nurse PA
1     1  5    13 NA
2     2  2     7  3
3     3  3     8  9

Warm Regards,
Pritish

#4

Hi @alrezq,

You can use the group_by - summarise workflow:

# Load libraries
library("tidyverse")

# Define example data
my_data = tribble(
  ~Shift, ~Role_type, ~Working_hours,
  1, "Nurse", 3,
  1, "Nurse", 2,
  1, "Nurse", 5,
  1, "MD", 2,
  1, "PA", 7,
  2, "Nurse", 3,
  2, "Nurse", 7,
  2, "MD", 4,
  2, "MD", 2,
  3, "Nurse", 6,
  3, "Nurse", 3,
  3, "MD", 2,
  3, "PA", 1
)

# Count hours worked per shift, per role
my_data %>%
  group_by(Shift, Role_type) %>%
  summarise(s = sum(Working_hours)) %>%
  ungroup

Yielding:

# A tibble: 8 x 3
  Shift Role_type     s
  <dbl> <chr>     <dbl>
1     1 MD            2
2     1 Nurse        10
3     1 PA            7
4     2 MD            6
5     2 Nurse        10
6     3 MD            2
7     3 Nurse         9
8     3 PA            1

Hope it helps :slightly_smiling_face:

#6

Thank you all for the replies, I found the way.

#7

@alrezq, which ever one was the solution, please mark it as such.

#8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.