How to pivot wider in R on one column value

Question

Below is the sample data and the manipulations that I have done so far. I have tried this in other ways but have an idea that may make this a bit simpler. The intended result is at the bottom. what i am looking for is a way to pivot wider based on when the smb column says total. There are five possible values for smb.. 1,2,3,4, and total. I want there to be a new column smb.total which will have the total for each smb/year/qtr/area combination. I have tried putting a filter in front of the pivot wider statement (at the bottom)

  library(readxl)
  library(dplyr)
  library(stringr)
  library(tidyverse)
  library(gt)


 employment <- c(1,45,125,130,165,260,600,601,2,46,127,132,167,265,601,602,50,61,110,121,170,305,55,603,52,66,112,123,172,310,604,605)
 small <- c(1,1,2,2,3,4,NA,NA,1,1,2,2,3,4,NA,NA,1,1,2,2,3,4,NA,NA,1,1,2,2,3,4,NA,NA)
 area <-c(001,001,001,001,001,001,001,001,001,001,001,001,001,001,001,001,003,003,003,003,003,003,003,003,003,003,003,003,003,003,003,003)
 year<-c(2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020)
 qtr <-c(1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2)

 smbtest <- data.frame(employment,small,area,year,qtr)

 smbtest$smb <-0

 smbtest <- smbtest %>% mutate(smb = case_when(employment >=0 & employment <100 ~ "1",employment >=0 
 & employment <150 ~ "2",employment >=0 & employment <250 ~ "3", employment >=0 & employment <500 ~ 
"4", employment >=0 & employment <100000 ~ "Total"))


smbsummary2<-smbtest %>% 
mutate(period = paste0(year,"q",qtr)) %>%
group_by(area,period,smb) %>%
summarise(employment = sum(employment), worksites = n(), 
        .groups = 'drop_last') %>% 
mutate(employment = cumsum(employment),
     worksites = cumsum(worksites))

smbsummary2<- smbsummary2%>%
group_by(area,smb)%>%
mutate(empprevyear=lag(employment),
     empprevyearpp=employment-empprevyear,
     empprevyearpct=((employment/empprevyear)-1), 
empprevyearpct=scales::percent(empprevyearpct,accuracy = 0.01)
)

 area   period    smb      employment     worksites     smb.Total
   1    2020q1     1          46            2              1927
   1    2020q1     2         301            4              1927
   1    2020q1     3         466            5              1927
   1    2020q1     4         726            6              1927
   1    2020q1    Total     1927            8              1927 

smbsummary2<-smbsummary2 %>%
filter(small=='Total')
pivot_wider(names_from = small, values_from = employment)

Answer 1

Maybe this code will solve your question:

employment <- c(1, 45, 125, 130, 165, 260, 600, 601, 2, 46, 127, 
                132, 167, 265, 601, 602, 50, 61, 110, 121, 170, 
                305, 55, 603, 52, 66, 112, 123, 172, 310, 604, 605)
small <- c(1, 1, 2, 2, 3, 4, NA, NA, 1, 1, 2, 2, 3, 4, NA, NA, 1, 1,
           2, 2, 3, 4, NA, NA, 1, 1, 2, 2, 3, 4, NA, NA)
area <-c(001, 001, 001, 001, 001, 001, 001, 001, 001, 001, 001, 001,
         001, 001, 001, 001, 003, 003, 003, 003, 003, 003, 003, 003,
         003, 003, 003, 003, 003, 003, 003, 003)
year<-c(2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
        2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
        2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
        2020, 2020)
qtr <-c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1,
        1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2)

smbtest <- tibble(employment, small, area, year, qtr)

smbtest$smb <- 0

smbtest <- smbtest %>% 
  mutate(smb = case_when(employment >=0 & employment <100 ~ "1",
                         employment >=0 & employment <150 ~ "2",
                         employment >=0 & employment <250 ~ "3", 
                         employment >=0 & employment <500 ~ "4", 
                         employment >=0 & employment <100000 ~ "Total"))

smbtest <- smbtest %>% 
  relocate(smb, year, qtr, area, small, employment)

smbsummary2 <- smbtest %>% 
  mutate(period = paste0(year,"q",qtr)) %>%
  group_by(area, period, smb) %>% 
  summarise(employment = sum(employment), 
            worksites = n()) %>% 
  mutate(employment = cumsum(employment),
         worksites = cumsum(worksites))

smbsummary2 %>% 
  group_by(area, period) %>% 
  mutate(`employ/period (%)` = employment/employment[smb == "Total"]*100)

Probably not the best answer, but for your data I think it's works well. If not please tell me.

Good job!

Answer 2

I do know if I understand correctly.

Do you wanna smb.total of what? employment variable? If yes. In your object "smbsummary2" use this code:

smbsummary2 <- smbtest %>% 
relocate(smb, year, qtr, area, small, employment) %>% 
group_by(smb, year, qtr, area) %>% 
mutate(smb.total = n())

If was not it, do you could be explain me better?