CODEWITHSUNDEEP
rdataframe
Ariel
Ariel

Reputation: 405

stack columns and reshape at the same time in R

I have the following structure:

Year City1 Year City2 Year City3 Year City4 Year City5 ...
2011 val11 2010 val21 2011 val31 2010 val41 2015 val51 ...
2012 val12 2011 val22 2012 val32 2011 val42 2016 val52 ...
2013 val13 2012 val23 2013 val33 2012 val43 2017 val53 ...
2014 val14 2013 val24 2014 val34 2013 val44 2018 val54 ...
...

And, I need transform that into this:

City Year Value
City1 2011 val11
City1 2012 val12
City1 2013 val13
City1 2014 val14
City2 2010 val21
City2 2011 val22
City2 2012 val23
City2 2013 val24
City3 2011 val31 
City3 2012 val32 
City3 2013 val33 
City3 2014 val34 
City4 2010 val41
City4 2011 val42
City4 2012 val43
City4 2013 val44
City5 2015 val51
City5 2016 val52
City5 2017 val53
City5 2018 val54

I need this in R, but I have no idea how to do it. Also, the original dataset is in Excel, and each city has different number of rows (different amount of years). Thanks!

Upvotes: 0

Views: 24

Answers (1)

akrun
akrun

Reputation: 887118

We use melt from data.table to reshape from 'wide' to 'long' format. Specify the column name substring in the patterns for measure, and use the index of the 'City' column to replace with the 'City' column names ('nm2')

library(data.table)
nm2 <- grep("City", names(df1), value = TRUE)

melt(setDT(df1), measure = patterns("^Year", "City"),
  value.name  = c("Year", "Value"), variable.name = 'City')[, 
       City := nm2[City]][]

-output

#     City Year Value
# 1: City1 2011 val11
# 2: City1 2012 val12
# 3: City1 2013 val13
# 4: City1 2014 val14
# 5: City2 2010 val21
# 6: City2 2011 val22
# 7: City2 2012 val23
# 8: City2 2013 val24
# 9: City3 2011 val31
#10: City3 2012 val32
#11: City3 2013 val33
#12: City3 2014 val34
#13: City4 2010 val41
#14: City4 2011 val42
#15: City4 2012 val43
#16: City4 2013 val44
#17: City5 2015 val51
#18: City5 2016 val52
#19: City5 2017 val53
#20: City5 2018 val54

Upvotes: 1

Related Questions