how to reshape the following tables?

Question

There is a couple of similar questions here but I failed to apply them to my case. I have many tables like this:

GDP:

time   |    AT    |    BE   |     BG    |     CF    | : United kingdom
2014Q4 |   4564   |  4646   |    6541   |     122   | :
2014Q3 |   1234   |  5789   |    3545   |     3546  | :
2014Q2 |   1456   |   354   |    3541   |     3543  | :
:      |    :     |    :    |     :     |      :    | :
1990Q1 |   1234   |  3546   |    6546   |     5466  |

REER: the similar table etc.

how can I reshape them to the following style:

       Country | time   |    GDP |  REER  |   :..
            AT | 1990Q1 |   4564 |  4646  |   6541  |    122  | :
            AT | 1990Q2 |   1234 |  5789  |   3545  |   3546  | :
            AT | 1990Q3 |   1456 |   354  |   3541  |   3543  | 
             : |  :     |   :    |   :    |     :   |      :  | :
United Kingdom | 2014Q4 |   1234 |  3546  |   6546  |   5466  |

the code

setwd ("D:/Documents/R/eurostatData")

library (SmarterPoland)
library(reshape)
library (xlsx)
# Write to xls file --------------------------------------------------------
save.xlsx <- function (file, ...)
{
  require(xlsx, quietly = TRUE)
  objects <- list(...)
  fargs <- as.list(match.call(expand.dots = TRUE))
  objnames <- as.character(fargs)[-c(1, 2)]
  nobjects <- length(objects)
  for (i in 1:nobjects) {
    if (i == 1)
      write.xlsx(objects[[i]], file, sheetName = objnames[i])
    else write.xlsx(objects[[i]], file, sheetName = objnames[i],
                append = TRUE)
  }
  print(paste("Workbook", file, "has", nobjects, "worksheets."))
}

# load TOC
TOC <- grepEurostatTOC("Production") # get everything about Production
TOC2 <- getEurostatTOC()
View(TOC)
# END TOC

dat_raw <- getEurostatRCV("sts_inpr_q") # quaterly

# how many unique values are?
unique(dat_raw$indic_bt)
unique(dat_raw$nace_r2) # initialy I need C - manufacturing
unique(dat_raw$time) # 1980-20104

production <- cast(dat_raw,  time ~ geo, subset= nace_r2=="C" & s_adj=="GROSS" )


# RESHAPING
# !!!!nothing to write, since the steps I did were not successful.!!!!


save.xlsx("data.xlsx", production, turnover_dom, turnover_ndom, import_price,  producer_prices, labour, GCF, gdp, inflation)

You can start by providing a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) so that we don't have to guess at what your data structure is really like! It doesn't have to be real data, just enough to be able to create the problem you're trying to solve. — A5C1D2H2I1M1N2O1R2T1, Apr 21 '15 at 10:04
My guess is that you probably need something like `rbind` and `reshape` (or `melt`, but it would need to be the `melt` from the development version of "data.table" since you want a semi-wide dataset). — A5C1D2H2I1M1N2O1R2T1, Apr 21 '15 at 10:22
I was playing with reshape and melt, but no theresult I was expected — Rom, Apr 21 '15 at 10:25
@Roman, Please use the "edit" button to add further details to your question. Comments only allow a very limited set of formatting so are not very useful for sharing code. — A5C1D2H2I1M1N2O1R2T1, Apr 21 '15 at 10:39
@Ananda Mahto Well, I failed to receive the needed result... (in terms of reshape application) — Rom, Apr 21 '15 at 13:02

score 0 · Accepted Answer · answered Apr 21 '15 at 15:20

0

You can use gather from the tidyr package.

    GDP <- gather(GDP_table, Country, GDP, -time)
    REER <- gather(REER_table, Country, REER, -time)

Then use the merge function to put them together.

    data <- merge(GDP, REER)

I hope this helps.

answered Apr 21 '15 at 15:20

user3239929

73
2

how to reshape the following tables?

1 Answers1