How to sum using concurrent values in R

user_4578075 0
Viewed 57

Below is the sample data and the code that I am using.

  library(dplyr)
  library(data.table)

   firm <- c("firm1","firm2","firm3","firm4","firm5","firm6","firm7","firm8","firm9")
   employment <- c(1,50,90,249,499,115,145,261,210)
   small <- c(1,1,1,3,4,2,2,4,3)

   smbtest <- data.frame(firm,employment,small)

   smbsummary <- smbtest %>% 
   select (employment,small) %>%
   group_by(small) %>%
   summarise(employment = sum(employment), worksites = n())

The desired result is such

   smb     employment    worksites
    1          141           3
    2          401           5
    3          860           7
    4         1620           9

smb denotes small business. the values for smb follow the following schema

  smb1     >= 0 and <100
  smb2     >= 0 and <150
  smb3     >= 0 and <250
  smb4     >= 0 and <500

This is what I am getting

   smb     employment   worksites
    1          141          3
    2          260          2
    3          459          2 
    4          760          2

So the question is how would I set this up to where it does not separate out the unique elements. I want it to be cumulative. 0 to 100 being part of 1 to 150 if that makes any sense.

dplyr r sum
2 Answers

We just to need to add a mutate step at the end to get the cumulative sum (cumsum of the 'worksites'). By default, the last group is dropped (as there is only one group, it will be dropped automatically, but specifying .groups is better)

library(dplyr)
smbtest %>% 
  select(employment,small) %>%
  group_by(small) %>%
  summarise(employment = sum(employment), worksites = n(), 
         .groups = 'drop') %>% 
  mutate(worksites = cumsum(worksites))

-output

# A tibble: 4 x 3
#  small employment worksites
#* <dbl>      <dbl>     <int>
#1     1        141         3
#2     2        260         5
#3     3        459         7
#4     4        760         9

If we need multiple columns to be cumulative summed, then use across

smbtest %>% 
  select(employment,small) %>%
  group_by(small) %>%
  summarise(employment = sum(employment), worksites = n(), 
         .groups = 'drop') %>%
  mutate(across(c(employment, worksites), cumsum))

Or a similar option in collapse

library(collapse)
slt(smbtest, employment, small) %>%
    fgroup_by(small) %>%
    fsummarise(worksites = fNobs(employment), 
              employment = fsum(employment)) %>%    
    ftransformv(-1, cumsum)
# A tibble: 4 x 3
#  small worksites employment
#  <dbl>     <int>      <dbl>
#1     1         3        141
#2     2         5        401
#3     3         7        860
#4     4         9       1620

data

smbtest <- tibble(firm, employment, small)
user_37322710

Here is a small modification to @akrun's answer that gets the employment cumulative sums you were looking for:

library(dplyr)
smbtest %>% 
  select(employment,small) %>%
  group_by(small) %>%
  summarise(employment = sum(employment), worksites = n(), 
            .groups = 'drop') %>% 
  mutate(employment = cumsum(employment),
         worksites = cumsum(worksites))

#   small employment worksites
#   <dbl>      <dbl>     <int>
# 1     1        141         3
# 2     2        401         5
# 3     3        860         7
# 4     4       1620         9
user_107361630
Related