George George - 4 days ago 6
R Question

R - Calculating a Running Total of Team Size by Location for each Month

I am currently working on a project looking at employee turnover rates. So far I have created a table that looks like the following sample:

library(tidyverse)

Data <- data.frame(Month = c("Jan", "Feb", "March", "Jan", "Feb", "March"),
Location = c("Sheffield", "Sheffield", "Sheffield","London", "London", "London"),
Joiners = c(7,3,8,4,9,1),
Leavers = c(1,5,9,3,2,5)) %>%
mutate(Net_Change = Joiners - Leavers)


I am wanting to calculate the team size by taking the sum of the Net_Change column based on Location and month (in order of sequence). For example, the February team size for London should equal 8 (1+7) and the March team size should equal 4 (1+7-4).

I have tried doing this using the dplyr 'summarise' function but was unsuccessful. If a 'tidyverse' method is applicable that would be great.

Thanks a lot for your help on this!

Answer
Data %>% group_by(Location) %>% mutate(Team_size = cumsum(Net_Change))

#    Month  Location Joiners Leavers Net_Change Team_size
#   (fctr)    (fctr)   (dbl)   (dbl)      (dbl)     (dbl)
#1    Jan Sheffield       7       1          6         6
#2    Feb Sheffield       3       5         -2         4
#3  March Sheffield       8       9         -1         3
#4    Jan    London       4       3          1         1
#5    Feb    London       9       2          7         8
#6  March    London       1       5         -4         4
Comments