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 would 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 at and already tried using the melt()
and the reshape()
functions
as some people were suggesting in 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.
Best Answer
Three alternative solutions:
1) With data.table:
You can use the same
melt
function as in thereshape2
package (which is an extended & improved implementation).melt
fromdata.table
has also more parameters that themelt
-function fromreshape2
. You can for example also specify the name of the variable-column:which gives:
Some alternative notations:
2) With tidyr:
Some alternative notations:
3) With reshape2:
Some alternative notations that give the same result:
NOTES:
NA
values, you can addna.rm = TRUE
to themelt
as well as thegather
functions.Another problem with the data is that the values will be read by R as character-values (as a result of the
,
in the numbers). You can repair that withgsub
andas.numeric
:Or directly with
data.table
ordplyr
:Data: