Reshape dataframe in R using dcast or ftable

Question

I currently have a data frame that looks like this.

country2<-c("Afghanistan","Afghanistan","Afghanistan")
continent2<-c("Asia","Asia","Asia")
series<-c('lifeexp','pop','gdp')
y1901<-c('1','3','100')
y1902<-c('2','4','101')
y1903<-c('2','4','101')
y1904<-c('2','4','101')
y1905<-c('2','4','101')
y1906<-c('2','4','101')
y1907<-c('2','4','101')

df<-data.frame(country2,continent2,series,y1901,y1902,y1903,y1904,y1905,y1906,y1907)


     country2 continent2  series y1901 y1902 y1903 y1904 y1905 y1906 y1907
1 Afghanistan       Asia lifeexp     1     2     2     2     2     2     2
2 Afghanistan       Asia     pop     3     4     4     4     4     4     4
3 Afghanistan       Asia     gdp   100   101   101   101   101   101   101

How can I reshape this data so that it will look like this?

country<-c("Afghanistan","Afghanistan","Afghanistan","Afghanistan","Afghanistan","Afghanistan","Afghanistan")
continent<-c("Asia","Asia","Asia","Asia","Asia","Asia","Asia")
year<-c("1901","1902","1903","1904","1905","1906","1907")
lifeexp<-c("1","2","2","2","2","2","2")
pop<-c('3','4','4','4','4','4','4')
gdp<-c('100','101','101','101','101','101','101')

df<-data.frame(country,continent,year,lifeexp,pop,gdp)

      country continent year lifeexp pop gdp
1 Afghanistan      Asia 1901       1   3 100
2 Afghanistan      Asia 1902       2   4 101
3 Afghanistan      Asia 1903       2   4 101
4 Afghanistan      Asia 1904       2   4 101
5 Afghanistan      Asia 1905       2   4 101
6 Afghanistan      Asia 1906       2   4 101
7 Afghanistan      Asia 1907       2   4 101

I have tried using dcast2 from the reshape2 to reshape the data but I can only enter 1 column for value.var.

dcast(df,country+region~series,value.var ='y1901',fun.aggregate = sum)

I also tried using ftable and xtabs but I'm still not sure how to enter more than 1 column for the value. The code below gives an error.

ftable(xtabs(c(y2000,y2001)~country+region+series,df))

Thanks

I think you have an error in the display of your expected output of your table... it's then 1901 and 1902 repeated 6 times — dc37, Dec 05 '19 at 07:48

dc37 · Answer 1 · 2019-12-05T07:57:26.267

I know that you are looking for a solution with ftable or dcast but just for your knowledge, you can achieve it using tidyr:

library(tidyverse)
df %>% 
  pivot_longer(., cols = starts_with("y190"), names_to = "year", values_to = "Value") %>%
  pivot_wider(., names_from = "series", values_from = "Value")  %>%
  mutate(year = gsub("y","", year)) %>% 
  rename(country = country2, continent = continent2)

# A tibble: 7 x 6
  country     continent year  lifeexp pop   gdp  
  <fct>       <fct>     <chr> <fct>   <fct> <fct>
1 Afghanistan Asia      1901  1       3     100  
2 Afghanistan Asia      1902  2       4     101  
3 Afghanistan Asia      1903  2       4     101  
4 Afghanistan Asia      1904  2       4     101  
5 Afghanistan Asia      1905  2       4     101  
6 Afghanistan Asia      1906  2       4     101  
7 Afghanistan Asia      1907  2       4     101

score 0 · Accepted Answer · answered Dec 05 '19 at 08:15

A data.table approach using melt and dcast could be

library(data.table)
setDT(df)

dcast(melt(df,measure = patterns("^y\\d+")),country2 + continent2 + variable~series)

#      country2 continent2 variable gdp lifeexp pop
#1: Afghanistan       Asia    y1901 100       1   3
#2: Afghanistan       Asia    y1902 101       2   4
#3: Afghanistan       Asia    y1903 101       2   4
#4: Afghanistan       Asia    y1904 101       2   4
#5: Afghanistan       Asia    y1905 101       2   4
#6: Afghanistan       Asia    y1906 101       2   4
#7: Afghanistan       Asia    y1907 101       2   4

Thanks! This is what I was looking for. – Chumbs Dec 05 '19 at 17:29 — Chumbs, Dec 05 '19 at 17:29

Reshape dataframe in R using dcast or ftable

2 Answers2