Reshaping data.frame from wide format to long format [other examples haven't helped]

Question

I have some trouble to convert my data.frame from a wide table to a long table. At the moment it looks like this:

Data Frame

I've looked at commands such as [melt] and [reshape] but having trouble to convert the table. I need it to like like this

Monat   Value
2008-01  849
2008-02  771
2008-03  822
.
.
2015-12  719

Data frame should be presented like this

I have tried the following code but its not working:

reshape(SuicideTable, direction = "long", varying = list(names(SuicideTable)[13:9]), v.names = "Value", 
        idvar = c("Monat"), timevar = "Value1", times = 2008:2015)

You might get better responses if you follow [these guidelines](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/11258495) for R, especially if your dataset is defined in a variable. Otherwise, you're asking others to take the time to create & populate the dataset themselves in order to present a runnable solution. — wibeasley, Mar 09 '18 at 18:01
To address your specific question, try [`tidyr::gather()`](http://tidyr.tidyverse.org/). You'll need to concatenate year & month after that. — wibeasley, Mar 09 '18 at 18:03
This is a duplicate question, reference: [Reshaping data.frame from wide to long format ](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — OTStats, Aug 08 '18 at 22:00

Daniel · Accepted Answer · 2018-03-09T18:50:21.750

you could use gather and unite for this:

  library(dplyr)
  library(tidyr)

  # example data
  mat <- (matrix(rnorm(12*8, mean = 800, sd = 200), nrow = 12))
  df <- data.frame(months = month.abb, mat)
  colnames(df)[-1] <- 2008:2015

  #    months     2008      2009      2010     2011     2012      2013  ...    
  # 1     Jan 578.1627 1005.3642  622.2480 738.3829 448.1257 1112.2660  
  # 2     Feb 950.1085  857.4998  866.0585 629.5612 848.3288  714.2643  
  # 3     Mar 650.0593  852.8997  797.3760 719.5924 696.3195  793.7964  
  # ...

  df %>% gather(year, Value, - months) %>% 
       unite(Month, year, months, sep = "-")

 #       Month     Value
 # 1  2008-Jan  578.1627
 # 2  2008-Feb  950.1085
 # 3  2008-Mar  650.0593
 # 4  2008-Apr  590.9742
 # 5  2008-May  671.8589
 # 6  2008-Jun  829.1035
 # 7  2008-Jul  755.9633
 # 8  2008-Aug  824.7879
 # ...

If you want the months to be represented as numbers and you know that your data set is of the same order as in your picture, you could mutate that variable to be represented as number from 1:12

   library(stringr)

   df %>% mutate(months = str_pad(1:12, 2,pad = "0")) %>%
          gather(year, Value, - months) %>% 
          unite(Month, year, months, sep = "-")

   #      Month     Value
   # 1  2008-01  578.1627
   # 2  2008-02  950.1085
   # 3  2008-03  650.0593
   # 4  2008-04  590.9742
   # 5  2008-05  671.8589
   # 6  2008-06  829.1035
   # ...

Reshaping data.frame from wide format to long format [other examples haven't helped]

1 Answers1