将data.frame从宽格式调整为长格式

我有一些麻烦将我的data.frame从宽表转换为长表。 目前它看起来是这样的:

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
现在我想把这个data.frame转换成一个长data.frame。 就像这样:

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
我已经看过并且已经尝试使用melt()和reshape()函数 就像一些人在类似的问题中暗示的那样。 然而，到目前为止，我只得到混乱的结果

如果有可能，我想用reshape()函数来做 它看起来更好处理一些

256421 次浏览
小开
最佳答案

reshape()需要一段时间来适应，就像melt/cast一样。下面是一个使用重塑的解决方案，假设你的数据帧被称为d:

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

使用重塑包:

#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"]))
小开

两种解决方案:

1) :

你可以使用melt函数:

library(data.table)
long <- melt(setDT(wide), id.vars = c("Code","Country"), variable.name = "year")

这使:

> 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

其他表示法:

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 :

使用pivot_longer():

library(tidyr)


long <- wide %>%
pivot_longer(
cols = `1950`:`1954`,
names_to = "year",
values_to = "value"
)

注意:

  • names_to和values_to分别默认为"name"和"value"，所以你可以非常简洁地将其写成wide %>% pivot_longer(`1950`:`1954`)。
  • cols参数使用高度灵活的tidyselect DSL，所以你可以使用一个负选择(!c(Code, Country))，一个选择助手(starts_with("19");matches("^\\d{4}$"))，数值索引(3:7)等。
  • tidyr::pivot_longer()是tidyr::gather()和reshape2::melt()的后续版本，这两个版本已不再开发。

改变值

数据的另一个问题是，值将被R读取为字符值(作为数字中的,的结果)。你可以在重塑之前使用gsub和as.numeric进行修复:

long$value <- as.numeric(gsub(",", "", long$value))

或者在重塑过程中，使用data.table或tidyr:

# data.table
long <- melt(setDT(wide),
id.vars = c("Code","Country"),
variable.name = "year")[, value := as.numeric(gsub(",", "", value))]


# tidyr
long <- wide %>%
pivot_longer(
cols = `1950`:`1954`,
names_to = "year",
values_to = "value",
values_transform = ~ as.numeric(gsub(",", "", .x))
)

数据:

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)
小开

下面是另一个例子，展示了tidyr中的gather的使用。你可以选择gather的列，方法是单独删除它们(就像我在这里做的那样)，或者显式地包括你想要的年份。

注意，为了处理逗号(如果未设置check.names = FALSE则添加X)，我还使用dplyr的mutate与parse_number from readr来将文本值转换回数字。这些都是tidyverse的一部分，因此可以与library(tidyverse)一起加载

wide %>%
gather(Year, Value, -Code, -Country) %>%
mutate(Year = parse_number(Year)
, Value = parse_number(Value))

返回:

   Code     Country Year Value
1   AFG Afghanistan 1950 20249
2   ALB     Albania 1950  8097
3   AFG Afghanistan 1951 21352
4   ALB     Albania 1951  8986
5   AFG Afghanistan 1952 22532
6   ALB     Albania 1952 10058
7   AFG Afghanistan 1953 23557
8   ALB     Albania 1953 11123
9   AFG Afghanistan 1954 24555
10  ALB     Albania 1954 12246
小开

由于这个答案被标记为，我觉得分享另一个来自基数R的替代:stack会很有用。

然而，请注意，stack对__abc1不起作用——它只在is.vector为TRUE时起作用，从is.vector的文档中，我们发现:

如果x是指定模式的向量，没有除了名字属性，is.vector返回TRUE。否则返回FALSE。

我正在使用示例数据来自@Jaap的回答，其中年份列中的值为__abc0。

下面是stack方法:

cbind(wide[1:2], stack(lapply(wide[-c(1, 2)], as.character)))
##    Code     Country values  ind
## 1   AFG Afghanistan 20,249 1950
## 2   ALB     Albania  8,097 1950
## 3   AFG Afghanistan 21,352 1951
## 4   ALB     Albania  8,986 1951
## 5   AFG Afghanistan 22,532 1952
## 6   ALB     Albania 10,058 1952
## 7   AFG Afghanistan 23,557 1953
## 8   ALB     Albania 11,123 1953
## 9   AFG Afghanistan 24,555 1954
## 10  ALB     Albania 12,246 1954
小开

下面是一个解决方案:

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;")

要在不输入所有内容的情况下进行查询，您可以使用以下命令:

感谢G. Grothendieck的实施。

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

不幸的是，我不认为PIVOT和UNPIVOT将适用于R SQLite。如果你想以一种更复杂的方式写你的查询，你也可以看看这些帖子:

小开

对于tidyr_1.0.0，另一个选项是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

数据

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))
小开

你也可以使用cdata包，它使用(转换)控制表的概念:

# 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)


library(cdata)
# build control table
drec <- data.frame(
Year=as.character(1950:1954),
Value=as.character(1950:1954),
stringsAsFactors=FALSE
)
drec <- cdata::rowrecs_to_blocks_spec(drec, recordKeys=c("Code", "Country"))


# apply control table
cdata::layout_by(drec, wide)

我目前正在探索这个包，发现它很容易访问。它是为更复杂的转换而设计的，包括反向转换。有一个教程可用。


提交答案