下面是另一个展示使用tidyr中的gather的示例。您可以通过单独删除列来选择要收集的列(就像我在这里所做的那样)，或者通过显式地包括您想要的年份来选择列。

注意，为了处理逗号(如果没有设置check.names = FALSE，则添加X)，我还使用dplyr的mutate with parse_number from readr将文本值转换回数字。这些都是tidyverse的一部分，因此可以通过库(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

你也可以使用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)

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

重塑()需要一段时间才能适应，就像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"]))

下面是一个sqldf解决方案:

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。如果你想以一种更复杂的方式写你的查询，你也可以看看这些帖子:

使用sprintf编写sql查询 将变量传递给sqldf

两种解决方案:

1)用data.table:

你可以使用melt函数:

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

这使:

长> 国家代码年值 1: AFG阿富汗1950 20,249 2: ALB阿尔巴尼亚1950 8097 第3集:AFG阿富汗1951 21,352 4: ALB阿尔巴尼亚1951 8,986 5:阿富汗空军1952 22,532 第6集:ALB阿尔巴尼亚1952 10058 7:阿富汗空军1953年23,557 8:阿尔巴尼亚1953年11,123 9:阿富汗空军1954 24555 10:阿尔巴尼亚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)带着整洁:

使用pivot_longer ():

library(tidyr)

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

注意:

names_to and values_to default to "name" and "value", respectively, so you could write this extra-succinctly as wide %>% pivot_longer(`1950`:`1954`). The cols argument uses the highly flexible tidyselect DSL, so you can select the same columns using a negative selection (!c(Code, Country)), a selection helper(starts_with("19"); matches("^\\d{4}$")), numeric indices (3:7), and more. tidyr::pivot_longer() is the successor to tidyr::gather() and reshape2::melt(), which are no longer under development.

改变值

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

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

或者在重塑过程中，使用数据。表或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)