Search code examples
rtidycensus

Collapsing Named Rows and Moving Variables to Columns in R

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
Solution

  • 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