group_by: How to Transform my Data

Question

I'm coming from Stata and struggling to get used to group_by in dplyr. Perhaps using group_by is the wrong approach, but if you know Stata, you'll understand why I'm trying to use it (I think).

I'd like to transform the data as shown below:

Essentially, I'm trying to collapse each NAICS code into a single row, and to create columns for the 5 statistics under CONC and HHI for each NAICS code (the 4 values for CONC and one for HHI, ignoring the Xs)

The data is here. The code I'm trying is as follows:

ManufMktConc <- read.csv("Manufacturing.csv",
                            stringsAsFactors = FALSE)

ManufMktConc %<>% 
  tbl_df %>% 
  slice(-1) %>% 
  filter(CONCENFI.display.label!="All companies") %>%
  select(-GEO.id,-GEO.id2,-GEO.display.label,-COMPANY,-RCPTOT,-YEAR.id)

ManufMktConc_byInd <- group_by(ManufMktConc,NAICS.id) 

ManufMktConc_byInd %<>% 
      arrange(CONCENFI.id) %>% 
      mutate(FourFirm = ManufMktConc_byInd$CCORCPPCT[ManufMktConc_byInd$CONCENFI.id=="856"])

That gives me the following error:

> ManufMktConc_byInd %<>% arrange(CONCENFI.id) %>% mutate(FourFirm=ManufMktConc_byInd$CCORCPPCT[ManufMktConc_byInd$CONCENFI.id=="856"])
Error: incompatible size (651), expecting 4 (the group size) or 1

So I can tell that I am asking R to stick the 651 instances of ManufMktConc_byInd$CONCENFI.id == "856" into the group of 4. I guess this is where my understanding of group_by falls apart. Why isn't my logical statement only applied within each group?

Thank you.

Answer 1

ManufMktConc <- read.csv("Downloads/Manufacturing.csv",
                         stringsAsFactors = FALSE)

ManufMktConc %<>% 
  tbl_df %>% 
  slice(-1) %>% 
  filter(CONCENFI.display.label!="All companies") %>%
  select(-GEO.id,-GEO.id2,-GEO.display.label,-COMPANY,-RCPTOT,-YEAR.id)

##  use melt and cast from reshape2 
require(reshape2)

# melt makes the data set tall and thin using id variables and measure variables
# 
ManufMktConc_molten <- as.data.frame(ManufMktConc) %>%
  melt(id.vars=1:4, measure.vars=5:6) %>%
  filter(value!="X")
ManufMktConc_molten[1:5,]

    NAICS.id       NAICS.display.label CONCENFI.id CONCENFI.display.label  variable value
1      311        Food manufacturing         856    4 largest companies CCORCPPCT  16.3
2      311        Food manufacturing         857    8 largest companies CCORCPPCT  24.2
3      311        Food manufacturing         858   20 largest companies CCORCPPCT  38.4
4      311        Food manufacturing         859   50 largest companies CCORCPPCT  50.9
5     3111 Animal food manufacturing         856    4 largest companies CCORCPPCT  30.2



# make a new column with the eventual column header. (Note this is different that your example)
ManufMktConc_molten$label <- paste0(ManufMktConc_molten$variable,
                                    trimws(substr(ManufMktConc_molten$CONCENFI.display.label,1,2)))

# cast it into multiple columns (something like a pivot in Excel).
ManufMktConc_result  <- ManufMktConc_molten %>%
  cast(NAICS.id + NAICS.display.label ~ label) %>%
  select(1,2,4,6,3,5,7)     ## reorder columns

ManufMktConc_result[1:5,]

      NAICS.id             NAICS.display.label CCORCPPCT4 CCORCPPCT8 CCORCPPCT20 CCORCPPCT50 VSHERFI50
    1      311              Food manufacturing       16.3       24.2        38.4        50.9     110.7
    2     3111       Animal food manufacturing       30.2       40.7        57.8        71.5     368.6
    3    31111       Animal food manufacturing       30.2       40.7        57.8        71.5     368.6
    4   311111  Dog and cat food manufacturing       67.8       80.6        89.6        96.5    2019.4
    5   311119 Other animal food manufacturing       24.3       36.2        51.5        68.2     228.3