user3651829 user3651829 - 3 months ago 15
R Question

long to wide in r with all data and unique observation labels in R

I have a data frame with 3 columns (Animal, Var1, Var2) and 3 rows (1 each for mouse, cat and dog):

Animal Var1 Var2
Mouse 5 5.5
Cat 7 7.4
Dog 1 6.3


I want it to look like this:

Mouse.Var1 Mouse.Var2 Cat.Var1 Cat.Var2 Dog.Var1 Dog.Var2
5 5.5 7 7.4 1 6.3


I have been trying to use reshape and reshape2 but neither seem to work.
Please help.

Answer

This can in fact be done with the reshape2 package (though I'm not sure why you would want to). First you have to melt the data so that the Var1 and Var2 columns become labels in a condensed column

library(reshape2)

df <- data.frame(Animal = c('Mouse', 'Cat', 'Dog'), Var1 = c(5, 7, 1), Var2 = c(5.5, 7.4, 6.3))
df.melt <- melt(df, id.vars = 'Animal')

This produces:

  Animal variable value
1  Mouse     Var1   5.0
2    Cat     Var1   7.0
3    Dog     Var1   1.0
4  Mouse     Var2   5.5
5    Cat     Var2   7.4
6    Dog     Var2   6.3

This can then be cast to the format you're after:

df.wide <- dcast(df.melt, . ~ Animal + variable)

  . Cat_Var1 Cat_Var2 Dog_Var1 Dog_Var2 Mouse_Var1 Mouse_Var2
1 .        7      7.4        1      6.3          5        5.5