CODEWITHSUNDEEP
rdplyrsummarizegt
Tim Wilcox
Tim Wilcox

Reputation: 1331

Issues with creating gt table after adding new fields

Below is the sample data, packages, and the manipulations. Part 3 and Part 4 are where the core question lies

The goal here is to produce a table that has the employment by smb category and time period. If I leave part 3 out and the col_order item, it works great. So the question is how do I create the items in part 3 and create a gt table that does not have these fields and also does not offset the data in the table. I have tried embedding a "%>% select (-"empprevyear",-"emprevyearpp",-"empprevyearpct") into it but that still messes things up. Also, have tried col_order (http://www.sthda.com/english/wiki/reordering-data-frame-columns-in-r) but still no success.

In short if you run all of the code except part 3, it produces the desired result. Question is how do I get the same result after adding additional calculated fields?

library(readxl)
library(dplyr)
library(data.table)
library(odbc)
library(DBI)
library(stringr)

employment <- c(1,45,125,130,165,260,600,2,46,127,132,167,265,601,50,61,110,121,170,305,55,603,66,112,123,172,310,604)
small <- c(1,1,2,2,3,4,NA,1,1,2,2,3,4,NA,1,1,2,2,3,4,NA,1,1,2,2,3,4,NA)
area <-c(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)
 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)
qtr <-c(1,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,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"))


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

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

 ###part 4
 smblonger2<-smbsummary2 %>%
 ungroup() %>%
 pivot_longer(cols = employment:worksites, names_to = "measure", values_to = "value") %>%
 group_by(area,measure) %>%
 pivot_wider(names_from = period, values_from = value)%>%gt()

Upvotes: 1

Views: 71

Answers (1)

Tim Wilcox
Tim Wilcox

Reputation: 1331

The answer is that I needed to put a select statement in the first line of part 4.

select("area","period","smb","employment","worksites")%>%

Upvotes: 1

Related Questions