How can I reformat this data to a wide format?
species val price
setosa 5.1 3
setosa 4.9 3
setosa 4.7 3
setosa 4.6 2
Desired output:
species val1 val2 val3 val4 price1 price2 price3 price4
setosa 5.1 4.9 4.7 4.6 3 3 3 2
I have tried spread from tidyr but without success.
data.table v 1.9.6+ allows you to pass more than one value.vars, so you can simply do
library(data.table)
dcast(setDT(df), species ~ val + price, value.var = c("val", "price"))
# species val.1_4.6_2 val.1_4.7_3 val.1_4.9_3 val.1_5.1_3 price.1_4.6_2 price.1_4.7_3 price.1_4.9_3 price.1_5.1_3
# 1: setosa 4.6 4.7 4.9 5.1 2 3 3 3
You can try this:
> d
species val price
1 setosa 5.1 3
2 setosa 4.9 3
3 setosa 4.7 3
4 setosa 4.6 2
5 versicolor 5.1 3
6 versicolor 4.9 3
7 versicolor 4.7 3
8 versicolor 4.6 2
> t(sapply(split(d,d$species),function(x){unlist(x[,-1]) }))
val1 val2 val3 val4 price1 price2 price3 price4
setosa 5.1 4.9 4.7 4.6 3 3 3 2
versicolor 5.1 4.9 4.7 4.6 3 3 3 2
Here's a tidyr solution in a dplyr chain:
library(dplyr)
library(tidyr)
dat <- data.frame(species = "setosa", val = c(5.1, 4.9, 4.7, 4.6), price = c(3,3,3,2))
out.df <- dat %>% mutate(row.n = row_number()) %>%
gather(var, value, -c(species, row.n)) %>%
unite(col.n, c(var, row.n), sep = "") %>%
mutate(col.n = factor(col.n, levels = unique(col.n))) %>% # preserve column order
spread(col.n, value)
out.df
species val1 val2 val3 val4 price1 price2 price3 price4
1 setosa 5.1 4.9 4.7 4.6 3 3 3 2
