I have a data frame that looks like
Country Variable 2012 2013 2014
Germany Medical 11 2 4
Germany Transport 12 6 8
France Medical 15 10 12
France Transport 17 13 14
France Food 24 14 15
I would like to transpose the data frame in such a way that the final data frame takes the form of the following:
Country year Medical Transport Food
Germany 2012 11 12 NA
Germany 2013 2 6 NA
Germany 2014 4 8 NA
France 2012 15 17 24
France 2013 10 13 14
France 2014 12 14 15
I've tried several functions including melt, reshape, and spread but they didn't work. Does anybody have any ideas?
You can first convert it into long format and then into wide again
library(tidyr)
df %>%
pivot_longer(cols = -c(Country, Variable), names_to = "year") %>%
pivot_wider(names_from = Variable, values_from = value)
# A tibble: 6 x 5
# Country year Medical Transport Food
# <fct> <chr> <int> <int> <int>
#1 Germany 2012 11 12 NA
#2 Germany 2013 2 6 NA
#3 Germany 2014 4 8 NA
#4 France 2012 15 17 24
#5 France 2013 10 13 14
#6 France 2014 12 14 15
For older version of tidyr that would be with gather and spread
df %>%
gather(year, value, -c(Country, Variable)) %>%
spread(Variable, value)