使用重塑包:

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

下面是另一个展示使用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

下面是一个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

对于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

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

使用重塑包:

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