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
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