reconstruct data in R

Question

Now I have a data set like this:

  country index     value
1     AUS   GPD 0.8004142
2     AUS   GNI 0.8251010
3     AUS   CPI 0.6675700
4     HUN   GPD 0.3520509
5     HUN   GNI 0.4821505
6     HUN   CPI 0.3623341
7     USA   GPD 0.6431452
8     USA   GNI 0.9119910
9     USA   CPI 0.6616684

and then I use subset and merge command to reconstruct the data as followed

gdp<-subset(x,index=="GDP")# subset by index
> gdp
  country index     value
1     AUS   GDP 0.8004142
4     HUN   GDP 0.3520509
7     USA   GDP 0.6431452
names(gdp)[3]<-"GDP" # rename 'value' to 'GDP'
gdp<-gdp[c(-2)]
gni<-subset(x,index=="GNI")
names(gni)[3]<-"GNI"
gni<-gni[c(-2)]
cpi<-subset(x,index=="CPI")
names(cpi)[3]<-"CPI"
cpi<-cpi[c(-2)]
total<-merge(gdp, gni, by="country")
total1<-merge(total, cpi, by="country")
> total1
  country       GDP       GNI       CPI
1     AUS 0.8004142 0.8251010 0.6675700
2     HUN 0.3520509 0.4821505 0.3623341
3     USA 0.6431452 0.9119910 0.6616684

I am looking for a easy way to reconstruct the data like this. Kindly provide some suggestions(sample code). Any help is greatly appreciated .

Answer 1

This is a very basic "reshape" question.

The most direct way is to use dcast from "reshape2":

> library(reshape2)
> dcast(mydf, country ~ index)
  country       CPI       GNI       GPD
1     AUS 0.6675700 0.8251010 0.8004142
2     HUN 0.3623341 0.4821505 0.3520509
3     USA 0.6616684 0.9119910 0.6431452

Alternatively, in base R, there's xtabs. xtabs outputs a matrix, hence the use of as.data.frame.matrix to get your data.frame.

> as.data.frame.matrix(xtabs(value ~ country + index, mydf))
          CPI       GNI       GPD
AUS 0.6675700 0.8251010 0.8004142
HUN 0.3623341 0.4821505 0.3520509
USA 0.6616684 0.9119910 0.6431452