Reshaping data.frame from wide to long format
Three alternative solutions:
1) With data.table:
You can use the same melt
function as in the reshape2
package (which is an extended & improved implementation). melt
from data.table
has also more parameters that the melt
-function from reshape2
. You can for example also specify the name of the variable-column:
library(data.table)
long <- melt(setDT(wide), id.vars = c("Code","Country"), variable.name = "year")
which gives:
> long Code Country year value 1: AFG Afghanistan 1950 20,249 2: ALB Albania 1950 8,097 3: AFG Afghanistan 1951 21,352 4: ALB Albania 1951 8,986 5: AFG Afghanistan 1952 22,532 6: ALB Albania 1952 10,058 7: AFG Afghanistan 1953 23,557 8: ALB Albania 1953 11,123 9: AFG Afghanistan 1954 24,555 10: ALB Albania 1954 12,246
Some alternative notations:
melt(setDT(wide), id.vars = 1:2, variable.name = "year")
melt(setDT(wide), measure.vars = 3:7, variable.name = "year")
melt(setDT(wide), measure.vars = as.character(1950:1954), variable.name = "year")
2) With tidyr:
library(tidyr)
long <- wide %>% gather(year, value, -c(Code, Country))
Some alternative notations:
wide %>% gather(year, value, -Code, -Country)
wide %>% gather(year, value, -1:-2)
wide %>% gather(year, value, -(1:2))
wide %>% gather(year, value, -1, -2)
wide %>% gather(year, value, 3:7)
wide %>% gather(year, value, `1950`:`1954`)
3) With reshape2:
library(reshape2)
long <- melt(wide, id.vars = c("Code", "Country"))
Some alternative notations that give the same result:
# you can also define the id-variables by column number
melt(wide, id.vars = 1:2)
# as an alternative you can also specify the measure-variables
# all other variables will then be used as id-variables
melt(wide, measure.vars = 3:7)
melt(wide, measure.vars = as.character(1950:1954))
NOTES:
- reshape2 is retired. Only changes necessary to keep it on CRAN will be made. (source)
- If you want to exclude
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 with gsub
and as.numeric
:
long$value <- as.numeric(gsub(",", "", long$value))
Or directly with data.table
or dplyr
:
# data.table
long <- melt(setDT(wide),
id.vars = c("Code","Country"),
variable.name = "year")[, value := as.numeric(gsub(",", "", value))]
# tidyr and dplyr
long <- wide %>% gather(year, value, -c(Code,Country)) %>%
mutate(value = as.numeric(gsub(",", "", value)))
Data:
wide <- read.table(text="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, check.names=FALSE)
reshape()
takes a while to get used to, just as melt
/cast
. Here is a solution with reshape, assuming your data frame is called d
:
reshape(d,
direction = "long",
varying = list(names(d)[3:7]),
v.names = "Value",
idvar = c("Code", "Country"),
timevar = "Year",
times = 1950:1954)