reshaping data frame in R: changing a column to row name

Question

I am working in R. I have a data frame with three columns. Column A contains Company names column b contains date and column c contains prices

>

   A           B          C
  Apple     2012/06/01   410
  Coke      2012/06/01   210
  Pepsi     2012/06/01   152
  Apple     2012/06/02   420
  Coke      2012/06/02   220
  Pepsi     2012/06/02   142
  Apple     2012/06/03   440
  Coke      2012/06/03   260
  Pepsi     2012/06/03   122

I want to reshape my data frame so that Company name becomes row names and date becomes column names and price is in the corresponding cell

             Apple     Coke    Pepsi

2012/06/03   410      210      152
2012/06/03   420      220      142
2012/06/03   460      260      162

I tried using melt and dcast function, but couldn't find a solution.

possible duplicate of [Reshape data from long to wide format R](http://stackoverflow.com/questions/5890584/reshape-data-from-long-to-wide-format-r) — aosmith, Sep 11 '15 at 15:59

score 4 · Answer 1 · answered Sep 11 '15 at 07:07

You could use tidyr::spread

library(tidyr)
spread(d,A,C)

output

           B Apple Coke Pepsi
1 2012/06/01   410  210   152
2 2012/06/02   420  220   142
3 2012/06/03   440  260   122

data

d <- read.table(text="   A           B          C
  Apple     2012/06/01   410
  Coke      2012/06/01   210
  Pepsi     2012/06/01   152
  Apple     2012/06/02   420
  Coke      2012/06/02   220
  Pepsi     2012/06/02   142
  Apple     2012/06/03   440
  Coke      2012/06/03   260
  Pepsi     2012/06/03   122",head=TRUE)

RHertel · Answer 2 · 2015-09-11T08:29:35.043

You can reshape the data from "long" to "wide" format with the the dcast() function from the reshape2 package:

library(reshape2)
dcast(df1, B ~ A , value.var = "C")
#           B Apple Coke Pepsi
#1 2012/06/01   410  210   152
#2 2012/06/02   420  220   142
#3 2012/06/03   440  260   122

data

df1 <- structure(list(A = c("Apple", "Coke", "Pepsi", "Apple", "Coke", 
           "Pepsi", "Apple", "Coke", "Pepsi"), 
           B = c("2012/06/01", "2012/06/01", "2012/06/01", "2012/06/02", 
                 "2012/06/02", "2012/06/02", "2012/06/03", "2012/06/03", 
                 "2012/06/03"), 
            C = c(410L, 210L, 152L, 420L, 220L, 142L, 440L, 260L, 122L)),
           .Names = c("A", "B", "C"),
            class = "data.frame", row.names = c(NA, -9L))

This webpage is an example of a source that provides an explanation of the command that has been used. It also describes other possibilities to convert data formats.

score 0 · Answer 3 · answered Sep 11 '15 at 09:37

We can use the function reshape from the base package stats:

reshape(df, idvar='B', timevar='A', direction='wide')

Output:

           B C.Apple C.Coke C.Pepsi
1 2012/06/01     410    210     152
4 2012/06/02     420    220     142
7 2012/06/03     440    260     122

Data:

df <- structure(list(A = c("Apple", "Coke", "Pepsi", "Apple", "Coke", 
           "Pepsi", "Apple", "Coke", "Pepsi"), 
           B = c("2012/06/01", "2012/06/01", "2012/06/01", "2012/06/02", 
                 "2012/06/02", "2012/06/02", "2012/06/03", "2012/06/03", 
                 "2012/06/03"), 
            C = c(410L, 210L, 152L, 420L, 220L, 142L, 440L, 260L, 122L)),
           .Names = c("A", "B", "C"),
            class = "data.frame", row.names = c(NA, -9L))

reshaping data frame in R: changing a column to row name

3 Answers3