Reshaping data.frame from wide to long format

Question

I have some trouble to convert my data.frame from a wide table to a long table. At the moment it looks like this:

Code Country        1950    1951    1952    1953    1954
AFG  Afghanistan    20,249  21,352  22,532  23,557  24,555
ALB  Albania        8,097   8,986   10,058  11,123  12,246

Now I like to transform this data.frame into a long data.frame. Something like this:

Code Country        Year    Value
AFG  Afghanistan    1950    20,249
AFG  Afghanistan    1951    21,352
AFG  Afghanistan    1952    22,532
AFG  Afghanistan    1953    23,557
AFG  Afghanistan    1954    24,555
ALB  Albania        1950    8,097
ALB  Albania        1951    8,986
ALB  Albania        1952    10,058
ALB  Albania        1953    11,123
ALB  Albania        1954    12,246

I have looked and tried it already with the melt() and the reshape() functions as some people were suggesting to similar questions. However, so far I only get messy results.

If it is possible I would like to do it with the reshape() function since it looks a little bit nicer to handle.

Answer 1

Here you go:

x <- read.table(textConnection(
"Code Country        1950    1951    1952    1953    1954
AFG  Afghanistan    20,249  21,352  22,532  23,557  24,555
ALB  Albania        8,097   8,986   10,058  11,123  12,246"), header=TRUE)
library(reshape)
x2 <- melt(x,id=c("Code","Country"),variable_name="Year")
x2[,"Year"] <- as.numeric(gsub("X","",x2[,"Year"]))

Answer 2

reshape() takes a while to get used to, just as melt/cast. Here is a solution with reshape, assuming your data frame id called d:

reshape(d, direction="long", varying=list(names(d)[3:7]), v.names="Value", 
        idvar=c("Code","Country"), timevar="Year", times=1950:1954)