Reshape data from long to wide with multiple measure columns using spread() or other reshape functions

Question

I know there're already many questions here asked about this topic. But after I looked over a few, all of them have only one 'measure' column. I still couldn't figure out how to do it with my data.

My data look like the following with X1, X2 indicate different regions. so each column in this data set represents all the ages collected for a single region.

age     X1   X2
age 0   2    2
age 1   2    2 
age 2   2    3
  ...

I want to reshape the data to wide form:

     age 0  age 1 age 2
X1    2      2     2 
X2    2      2     3
     ...

To recreate the dataset, please use

data <-structure(list(age = c("age 0", "age 1", "age 2", "age 3", "age 4", 
"age 5", "age 6", "age 7", "age 8", "age 9", "age 10", "age 11", 
"age 12"), X1 = c(2, 2, 2, 4, 7, 12, 19, 22, 18, 11, 6, 3, 3), 
    X2 = c(2, 2, 3, 4, 8, 14, 21, 24, 20, 12, 7, 4, 3)), row.names = c("0", 
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"
), class = "data.frame")

Transposing does the trick:

rownames(data)<- data[,1]
wide <- t(data)[2:3,]

But I wonder how to do it using spread() or other reshape functions.

library(tidyr)
wide <- spread(data, age, X1)
#X2 remains the same, and X1 is not correcty reshaped.
wide <- spread(data, age, X1, X2)
#Error in spread.data.frame(data, age, X1, X2) : object 'X2' not found

score 1 · Accepted Answer · answered Jul 17 '18 at 19:48

1

A tidyr solution below. You need to gather the region into a single column to be able to spread it.

library(tidyr)
data %>% gather(region,val,-age) %>% spread(age,val)  

#   region age 0 age 1 age 10 age 11 age 12 age 2 age 3 age 4 age 5 age 6 age 7 age 8 age 9
# 1     X1     2     2      6      3      3     2     4     7    12    19    22    18    11
# 2     X2     2     2      7      4      3     3     4     8    14    21    24    20    12

answered Jul 17 '18 at 19:48

jasbner

2,253
12
24

Thanks!!! Both solutions work! I like transposing better, it's more straightforward. Also, the second solution changes the order of the ages, ages 10-12 are in front of age 2 in the result. – Eric Wang Jul 17 '18 at 19:49

score 0 · Answer 2 · answered Jul 17 '18 at 19:56

0

It looks like you could accomplish this by transposing your data:

t(data)

If you are set on reshaping your data for any reason, you can use the reshape package, convert your data fully into long format, and then cast it into wide format:

library(reshape2)
dcast(melt(data, measure.vars = c("X1","X2")), variable~age)

answered Jul 17 '18 at 19:56

AnnVyrgyl

56
6

Thanks. It works. But the results from `dcast()` somehow changes the order of the ages, ages 10-12 are now between age 1 and age 2. see jasbner 's code above. – Eric Wang Jul 17 '18 at 20:00
That would be because your "age" variable is being sorted alphabetically, not numerically. The word "age" before the numbers causes the variable to be treated as character data. – AnnVyrgyl Jul 17 '18 at 20:08

score 0 · Answer 3 · answered Jul 17 '18 at 20:26

Or in base R just

> t("rownames<-"(data[, -1], data[, 1]))
   age 0 age 1 age 2 age 3 age 4 age 5 age 6 age 7 age 8 age 9 age 10 age 11 age 12
X1     2     2     2     4     7    12    19    22    18    11      6      3      3
X2     2     2     3     4     8    14    21    24    20    12      7      4      3

Reshape data from long to wide with multiple measure columns using spread() or other reshape functions

3 Answers3

Linked