CODEWITHSUNDEEP
rdplyrreshapereshape2
user3570187
user3570187

Reputation: 1773

reshaping with an embedded column and year name

Hi I have a data where the year value is embedded in the column name as follows and I would like to reshape it to long format. 

state<- c('MN', 'PA', 'NY')
city<-  c('Minessota', 'Pittsburgh','Newyork')
POPEST2010<- c(2899, 344,4555)
POPEST2011<- c(4444, 348,8999)
POPEST2012<- c(555, 55,77665)
df<- data.frame(state,city,  POPEST2010, POPEST2011, POPEST2012)

Any suggestions on how I can reshape to long format so I can see the data as follow:

state  city           year POPEST
MN     Minessota      2010  2899
MN     Minessota      2011  4444
MN     Minessota      2012  8999

similarly for other states Any ideas? Thanks so much!

Upvotes: 0

Views: 30

Answers (2)

Andre Elrico
Andre Elrico

Reputation: 11480

similar:

df %>%
tidyr::gather(year,POPEST,matches("POPEST")) %>% mutate(year = sub("[^0-9]+","",year))

#  state       city year POPEST
#1    MN  Minessota 2010   2899
#2    PA Pittsburgh 2010    344
#3    NY    Newyork 2010   4555
#4    MN  Minessota 2011   4444
#5    PA Pittsburgh 2011    348
#6    NY    Newyork 2011   8999
#7    MN  Minessota 2012    555
#8    PA Pittsburgh 2012     55
#9    NY    Newyork 2012  77665

Upvotes: 0

Thor6
Thor6

Reputation: 811

A solution using rename and gather

 df %>%
 rename_all(.funs = funs(gsub('POPEST', '', .))) %>%
 gather(year, POPEST, -state, -city)

Upvotes: 1

Related Questions