'How best to do this pivot operation in R

Below is the sample data and the desired outcome. This is a much simplified version of the actual data set. In the actual data set, there are 20 years and 4 quarters apiece. Looking to have each unique company entry listed once and the employment data series running from beginning to end from left to right. In the event that there is no data for Vision Inc in 2019 quarter 3, then I would want it to return a O and not an NA.

 library(tidyverse)
 library(dplyr)

 legalname <- c("Vision Inc.","Expedia","Strong Enterprise","Vision Inc.","Expedia","Strong Enterprise")
 year <- c(2019,2019,2019,2019,2019,2019)
 quarter <- c(1,1,1,2,2,2)
 cnty <- c(031,029,027,031,029,027)
 naics <- c(345110,356110,362110,345110,356110,345110)
 mnth1emp <- c (11,13,15,15,17,20)
 mnth2emp <- c(12,14,15,16,18,22)
 mnth3emp <-c(13,15,15,17,21,29)

 employers <- data.frame(legalname,year,quarter,naics,mnth1emp,mnth2emp,mnth3emp)

Desired Outcome

 legalname       cnty      naics     2019m1      2019m2   2019m3   2019m4   2019m5   2019m6
 Vision Inc        031      345110      11            12      13       15       16        17
 Expedia           029      356110      13            14      15       17       18        21

r dplyr pivot

Solution 1:^[1]

Does this work for you?

First pivot longer to get the months and values in a quarter; and then pivot wider to get the wide format you want.

employers %>%
  filter(legalname != "Strong Enterprise") %>%
  pivot_longer(mnth1emp:mnth3emp, names_to = "mnth", values_to = "value") %>% 
  mutate(month_in_quarter = as.numeric(str_extract(mnth, "\\d")),
         month =str_c("m", month_in_quarter +  3*(quarter - 1)))  %>% 
  select(-c(month_in_quarter, mnth)) %>%
   pivot_wider(c(legalname,cnty, naics), names_from = c(year, month),
              values_from = value,
values_fill = 0)

values_fill will fill NAs with 0s.

Solution 2:^[2]

perhaps try this.

I found a way to get the pivot right in R. I used the library("pivottabler") with the data.frame "bhmtrains". This worked now.

library(pivottabler)
qhpvt(bhmtrains, c("=","TOC"), "TrainCategory", 
c("Mean Speed"="mean(SchedSpeedMPH, na.rm=TRUE)", "Std Dev
Speed"="sd(SchedSpeedMPH, na.rm=TRUE)"),
formats=list("%.0f", "%.1f"), totals=list("", "TrainCategory"="All",
"Categories"))

my results out of the code

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Ari Anisfeld
Solution 2	Mgripp

'How best to do this pivot operation in R

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]