R: Creating long dataset when rows are variables and columns are years

Question

I have data that looks like this:

  Country.Code Indicator.Code X1960 X1961 X1962 X1963 X1964 X1965
          AFG    allsi.bi_q1    NA    NA    NA    NA    NA    NA
          AFG    allsp.bi_q1    NA    NA    NA    NA    NA    NA
          AFG    allsa.bi_q1    NA    NA    NA    NA    NA    NA
          AFG    lm_ub.bi_q1    NA    NA    NA    NA    NA    NA
          AFG  allsi.gen_pop    NA    NA    NA    NA    NA    NA
          AFG  allsp.gen_pop    NA    NA    NA    NA    NA    NA

And would like to make it look like this:

  Country.Code Year  allsi.bi_q1 allsp.bi_q1 allsa.bi_q1 lm_ub.bi_q1 allsi.gen_pop allsp.gen_pop
          AFG   1960      NA        NA        NA           NA          NA            NA
          AFG   1961      NA        NA        NA           NA          NA            NA
          AFG   1962      NA        NA        NA           NA          NA            NA
          AFG   1963      NA        NA        NA           NA          NA            NA
          AFG   1964      NA        NA        NA           NA          NA            NA
          AFG   1965      NA        NA        NA           NA          NA            NA

I have tried using melt from reshape2, but all I got was something like this (after also using as.numeric(substr(dataset$variable,2,5)) ) to get a year variable:

  Country.Code Indicator.Code variable value year
          AFG    allsi.bi_q1    X1960    NA 1960
          AFG    allsp.bi_q1    X1960    NA 1960
          AFG    allsa.bi_q1    X1960    NA 1960
          AFG    lm_ub.bi_q1    X1960    NA 1960
          AFG  allsi.gen_pop    X1960    NA 1960
          AFG  allsp.gen_pop    X1960    NA 1960

How do I get the values of indicator.code to become columns with the values of value for the respective year and country? Please disregard the NAs in my example.

I think you just need to cast this out after melting. Something like `dcast(longdat, Country.Code + year ~ Indicator.Code, value.var = "value")` — aosmith, Jun 06 '14 at 22:39
That solved it. Would you mind adding it as an answer so that I can accept and up vote it? — Kenji, Jun 06 '14 at 22:42

score 2 · Accepted Answer · answered Jun 06 '14 at 22:44

2

You just need to cast with dcast after melting (also from reshape2).

dcast(longdat, Country.Code + year ~ Indicator.Code, value.var = "value")

answered Jun 06 '14 at 22:44

aosmith

34,856
9
84
118

R: Creating long dataset when rows are variables and columns are years

1 Answers1