Using Reshape from wide to long in R [closed]

by

Here are three examples (along with some sample data that I think is representative of what you described).

Here’s the sample data:

set.seed(1)
mydf <- data.frame(
  company = LETTERS[1:4],
  earnings_2012 = runif(4),
  earnings_2011 = runif(4),
  earnings_2010 = runif(4),
  assets_2012 = runif(4),
  assets_2011 = runif(4),
  assets_2010 = runif(4)
)

mydf
#   company earnings_2012 earnings_2011 earnings_2010 assets_2012 assets_2011 assets_2010
# 1       A     0.2655087     0.2016819    0.62911404   0.6870228   0.7176185   0.9347052
# 2       B     0.3721239     0.8983897    0.06178627   0.3841037   0.9919061   0.2121425
# 3       C     0.5728534     0.9446753    0.20597457   0.7698414   0.3800352   0.6516738
# 4       D     0.9082078     0.6607978    0.17655675   0.4976992   0.7774452   0.1255551

Option 1: reshape

One limitation is that it won’t handle “unbalanced” datasets (for example, if you didn’t have “assets_2010” as part of your data, this wouldn’t work).

reshape(mydf, direction = "long", idvar="company", 
        varying = 2:ncol(mydf), sep = "_")
#        company time   earnings    assets
# A.2012       A 2012 0.26550866 0.6870228
# B.2012       B 2012 0.37212390 0.3841037
# C.2012       C 2012 0.57285336 0.7698414
# D.2012       D 2012 0.90820779 0.4976992
# A.2011       A 2011 0.20168193 0.7176185
# B.2011       B 2011 0.89838968 0.9919061
# C.2011       C 2011 0.94467527 0.3800352
# D.2011       D 2011 0.66079779 0.7774452
# A.2010       A 2010 0.62911404 0.9347052
# B.2010       B 2010 0.06178627 0.2121425
# C.2010       C 2010 0.20597457 0.6516738
# D.2010       D 2010 0.17655675 0.1255551

Option 2: The “reshape2” package

Quite popular for its syntax. Needs a little bit of processing before it can work since the column names need to be split in order for us to get this “double-wide” type of data. Is able to handle unbalanced data, but won’t be the best if your varying columns are of different column types (numeric, character, factor).

library(reshape2)
dfL <- melt(mydf, id.vars="company")
dfL <- cbind(dfL, colsplit(dfL$variable, "_", c("var", "year")))
dcast(dfL, company + year ~ var, value.var="value")
#    company year    assets   earnings
# 1        A 2010 0.9347052 0.62911404
# 2        A 2011 0.7176185 0.20168193
# 3        A 2012 0.6870228 0.26550866
# 4        B 2010 0.2121425 0.06178627
# 5        B 2011 0.9919061 0.89838968
# 6        B 2012 0.3841037 0.37212390
# 7        C 2010 0.6516738 0.20597457
# 8        C 2011 0.3800352 0.94467527
# 9        C 2012 0.7698414 0.57285336
# 10       D 2010 0.1255551 0.17655675
# 11       D 2011 0.7774452 0.66079779
# 12       D 2012 0.4976992 0.90820779

Option 3: merged.stack from “splitstackshape”

merged.stack from my “splitstackshape” package has pretty straightforward syntax and should be pretty fast if you need to end up with this “double-wide” type of structure. It was created to be able to handle unbalanced data and since it treats columns separately, won’t have problems with converting column types.

library(splitstackshape)
merged.stack(mydf, id.vars="company", 
             var.stubs=c("earnings", "assets"), sep = "_")
#     company .time_1   earnings    assets
#  1:       A    2010 0.62911404 0.9347052
#  2:       A    2011 0.20168193 0.7176185
#  3:       A    2012 0.26550866 0.6870228
#  4:       B    2010 0.06178627 0.2121425
#  5:       B    2011 0.89838968 0.9919061
#  6:       B    2012 0.37212390 0.3841037
#  7:       C    2010 0.20597457 0.6516738
#  8:       C    2011 0.94467527 0.3800352
#  9:       C    2012 0.57285336 0.7698414
# 10:       D    2010 0.17655675 0.1255551
# 11:       D    2011 0.66079779 0.7774452
# 12:       D    2012 0.90820779 0.4976992

More Related Contents:

  1. Convert an upper to lower triangle matrix in r [duplicate]
  2. How to reshape data from long to wide format
  3. Reshaping data.frame from wide to long format
  4. Reshaping multiple sets of measurement columns (wide format) into single columns (long format)
  5. Transpose / reshape dataframe without “timevar” from long to wide format
  6. Split delimited strings in a column and insert as new rows [duplicate]
  7. Reshape three column data frame to matrix (“long” to “wide” format) [duplicate]
  8. Gather multiple sets of columns
  9. dcast warning: ‘Aggregation function missing: defaulting to length’
  10. Reshape multiple value columns to wide format
  11. R Reshape data frame from long to wide format? [duplicate]
  12. Frequency counts in R [duplicate]
  13. Reshaping wide to long with multiple values columns [duplicate]
  14. Reshape multiple values at once
  15. Easy way to convert long to wide format with counts [duplicate]
  16. Grouped bar plot in ggplot
  17. Subsetting R data frame results in mysterious NA rows
  18. Compute mean and standard deviation by group for multiple variables in a data.frame
  19. Getting a stacked area plot in R
  20. Aggregate and reshape from long to wide
  21. how to pivot/unpivot (cast/melt) data frame? [duplicate]
  22. R: Reshaping Multiple Columns from Long to Wide
  23. Is there an R function to reshape this data from long to wide?
  24. What methods can we use to reshape VERY large data sets?
  25. can the value.var in dcast be a list or have multiple value variables?
  26. Reshape a dataframe to long format with multiple sets of measure columns [duplicate]
  27. R reshape a vector into multiple columns
  28. Apply multiple functions to multiple columns in data.table
  29. Get row and column indices of matches using `which()`
  30. “Set Difference” between two vectors with duplicate values

Leave a Comment