Reshaping Data in R with Reshape

Question

I have this dataset and for the life of me, I couldn't figure out how to get it in the format I want. I know that I needed to use melt from reshape package and possibly do a bit of transpose...

Would appreciate any help! Thank you!

Reproducible Data

df <- data.frame("Date" = c("jun", "aug", "dec"), 
                 "A1" = c("1", "3", "5"),
                 "A2" = c(10,11,12))

  Date A1 A2
1  jun  1 10
2  aug  3 11
3  dec  5 12

Intended Output

  Date Category Amt
1  jun       A1   1
2  aug       A1   3
3  dec       A1   5
4  jun       A2  10
5  aug       A2  11
6  dec       A2  12

akrun · Answer 1 · 2019-09-05T01:44:04.360

We can do this with gather to reshape from 'wide' to 'long' format. Specify the names of the 'key', 'value' column as 'Category', 'Amt' and the columnsto reshape with one of the select_helpers matches which will do a regex matching by checking the names that start (^) with 'A' followed by one or more digits (\\d+) till the end ($) of the string.

library(dplyr)
library(tidyr)
df %>%
   gather(Category, Amt, matches("^A\\d+$"))
#   Date Category Amt
#1  jun       A1   1
#2  aug       A1   3
#3  dec       A1   5
#4  jun       A2  10
#5  aug       A2  11
#6  dec       A2  12

We can also specify the columns with index

df %>% 
     gather(Category, Amt, 2:3)

Or another option is melt from data.table

library(data.table)
melt(setDT(df), id.var = 'Date', value.name = 'Amt', variable.name = 'Category')

Or using reshape from base R

reshape(df, idvar = 'Date', direction = 'long', varying = list(2:3))

Note: Assuming that the 'A1' column is numeric or character class

could you explain this part pls? `matches("^A\\d+$")` – Javier Sep 05 '19 at 01:33 — Javier, Sep 05 '19 at 01:33
also, is it possible to do it with `melt`? – Javier Sep 05 '19 at 01:33 — Javier, Sep 05 '19 at 01:33
@Javier yes, both requests are done – akrun Sep 05 '19 at 01:35 — akrun, Sep 05 '19 at 01:35

Reshaping Data in R with Reshape

1 Answers1