Sample problem:
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.
How to reshape data.frame from wide to long format? Answer #1:
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)
How to reshape data.frame from wide to long format? Answer #2:
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)
Answer #3:
Using reshape package:
#data
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 #4:
With tidyr_1.0.0
, another option is pivot_longer
library(tidyr)
pivot_longer(df1, -c(Code, Country), values_to = "Value", names_to = "Year")
# A tibble: 10 x 4
# Code Country Year Value
# <fct> <fct> <chr> <fct>
# 1 AFG Afghanistan 1950 20,249
# 2 AFG Afghanistan 1951 21,352
# 3 AFG Afghanistan 1952 22,532
# 4 AFG Afghanistan 1953 23,557
# 5 AFG Afghanistan 1954 24,555
# 6 ALB Albania 1950 8,097
# 7 ALB Albania 1951 8,986
# 8 ALB Albania 1952 10,058
# 9 ALB Albania 1953 11,123
#10 ALB Albania 1954 12,246
data
df1 <- structure(list(Code = structure(1:2, .Label = c("AFG", "ALB"), class = "factor"),
Country = structure(1:2, .Label = c("Afghanistan", "Albania"
), class = "factor"), `1950` = structure(1:2, .Label = c("20,249",
"8,097"), class = "factor"), `1951` = structure(1:2, .Label = c("21,352",
"8,986"), class = "factor"), `1952` = structure(2:1, .Label = c("10,058",
"22,532"), class = "factor"), `1953` = structure(2:1, .Label = c("11,123",
"23,557"), class = "factor"), `1954` = structure(2:1, .Label = c("12,246",
"24,555"), class = "factor")), class = "data.frame", row.names = c(NA,
-2L))
Answer #5:
Here’s a sqldf solution:
sqldf("Select Code, Country, '1950' As Year, `1950` As Value From wide
Union All
Select Code, Country, '1951' As Year, `1951` As Value From wide
Union All
Select Code, Country, '1952' As Year, `1952` As Value From wide
Union All
Select Code, Country, '1953' As Year, `1953` As Value From wide
Union All
Select Code, Country, '1954' As Year, `1954` As Value From wide;")
To make the query without typing in everything, you can use the following:
Thanks to G. Grothendieck for implementing it.
ValCol <- tail(names(wide), -2)
s <- sprintf("Select Code, Country, '%s' As Year, `%s` As Value from wide", ValCol, ValCol)
mquery <- paste(s, collapse = "\n Union All\n")
cat(mquery) #just to show the query
#> Select Code, Country, '1950' As Year, `1950` As Value from wide
#> Union All
#> Select Code, Country, '1951' As Year, `1951` As Value from wide
#> Union All
#> Select Code, Country, '1952' As Year, `1952` As Value from wide
#> Union All
#> Select Code, Country, '1953' As Year, `1953` As Value from wide
#> Union All
#> Select Code, Country, '1954' As Year, `1954` As Value from wide
sqldf(mquery)
#> 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
Unfortunately, I don’t think that PIVOT
and UNPIVOT
would work for R
SQLite
.
Hope you learned something from this post.
Follow Programming Articles for more!