Collapsing Named Rows and Moving Variables to Columns in R

Question

I'm using R to pull census data through 'tidycensus', but it pulls different variables for the same geography into rows, rather than using a single row geography and multiple variable columns.

I've tried various transpose, gather and spread functions, but can't collapse the spread values into a single row. My code looks like this:

Median_Inc<-get_acs(geography="County Subdivision",table=B06011,state="MA",county="Middlesex","Essex","Suffolk","Plymouth","Norfolk","Worcester")

which generates a table:

2500901260  Amesbury Town city, Essex County, Massachusetts B06011_001  37891
2500901260  Amesbury Town city, Essex County, Massachusetts B06011_002  37402
2500901260  Amesbury Town city, Essex County, Massachusetts B06011_003  47925
2500901260  Amesbury Town city, Essex County, Massachusetts B06011_004  NA
2500901260  Amesbury Town city, Essex County, Massachusetts B06011_005  27303

I expect these results, but what I'm trying to do is generate a table that has a single row for all values, and where the columns are the variable names, like:

GEOID   NAME    B06011_001  B06011_002  B06011_003  B06011_004  B06011_005
2500901260  Amesbury Town city, Essex County, Massachusetts 37891   37402   47925   NA  27303

An easier way to do this is to just set output = "wide" in your get_acs() call. So it would be something like this: get_acs( geography = "County Subdivision", table = "B06011", state = "MA", county = c("Middlesex", "Essex", "Suffolk", "Plymouth", "Norfolk", "Worcester"), output = "wide" ) — mfherman, Nov 17 '19 at 02:51

score 0 · Accepted Answer · answered Nov 11 '19 at 19:57

I didn't change get_acs function, but with few manipulations, you can have what you want.

Original data named tab :

         Num                 City        County          State        Code   value
1 2500901260   Amesbury Town city  Essex County  Massachusetts  B06011_001   37891
2 2500901260   Amesbury Town city  Essex County  Massachusetts  B06011_002   37402
3 2500901260   Amesbury Town city  Essex County  Massachusetts  B06011_003   47925
4 2500901260   Amesbury Town city  Essex County  Massachusetts  B06011_004      NA
5 2500901260   Amesbury Town city  Essex County  Massachusetts  B06011_005   27303

To have columns names :

colnames(tab) <- c("Num", "City", "County", "State", "Code", "value")

After manipulation :

library(reshape2)
data_wide <- dcast(tab, Num + City + County + State ~ Code, value.var="value")

     Num                 City        County          State  B06011_001  B06011_002  B06011_003  B06011_004  B06011_005
1 2500901260   Amesbury Town city  Essex County  Massachusetts       37891       37402       47925          NA       27303

Collapsing Named Rows and Moving Variables to Columns in R

1 Answers1