如何使用dcast保留多年份世界银行发展数据？

手里有一份2016-2020年世界银行发展数据的CSV表格，结构包含Country.Code、Series.Name及各年份数值列。原本只用到2020年数据，但部分变量缺失，需要保留所有年份数据，想把表格转成以Country.Code为行、Series.Name为列，同时保留2016-2020年所有数据的宽表。

尝试过的代码及错误 #

最初用这段代码只能保留2020年数据：

ControlM <- dcast(Control, Country.Code ~ Series.Name)

尝试指定value.var包含所有年份列时触发错误：

ControlM <- dcast(
  Control, 
  Country.Code ~ Series.Name, 
  value.var = c("2016", "2017", "2018", "2019", "2020")
)

错误信息：

Error in if (!(value.var %in% names(data))) { : 
  the condition has length > 1

试过转成data.table、设置value.var = NULL等方法，均无效。

数据样本 #

dput(head(Control))输出：

structure(list(Country.Name = c("Argentina", "Argentina", "Argentina", 
"Argentina", "Argentina", "Armenia"), Country.Code = c("ARG", 
"ARG", "ARG", "ARG", "ARG", "ARM"), Series.Name = c("Gini index", 
"Trade (% of GDP)", "Population density (people per sq. km of land area)", 
"Population, total", "Educational attainment, at least completed post-secondary, population 25+, total (%) (cumulative)", 
"Gini index"), Series.Code = c("SI.POV.GINI", "NE.TRD.GNFS.ZS", 
"EN.POP.DNST", "SP.POP.TOTL", "SE.SEC.CUAT.PO.ZS", "SI.POV.GINI"
), `2016` = c("42", "26.0938878488799", "15.9281350828921", "43590368", 
"..", "32.5"), `2017` = c("41.1", "25.2896011376779", "16.0941907925267", 
"44044811", "..", "33.6"), `2018` = c("41.3", "30.7625359549926", 
"16.2585100979651", "44494502", "..", "34.4"), `2019` = c("42.9", 
"32.6306150458499", "16.4208266190179", "44938712", "..", "29.9"
), `2020` = c("42.3", "30.2197998857878", "16.580892611147", 
"45376763", "..", "25.2")), row.names = c(NA, -6L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x00000193c0bd5930>)

不管用data.table还是reshape2的dcast，都不能直接给value.var传多个列名。正确思路是先把数据转成长格式（年份作为单独一列），再转成宽格式，把年份和Series.Name组合成列名。

方法1：使用data.table（效率更高） #

# 加载data.table包
library(data.table)

# 转成长格式：将2016-2020列转成Year和Value两列
Control_long <- melt(Control, 
                     id.vars = c("Country.Code", "Series.Name"),
                     measure.vars = c("2016", "2017", "2018", "2019", "2020"),
                     variable.name = "Year",
                     value.name = "Value")

# 转成宽格式：Country.Code为行，Year和Series.Name组合为列
ControlM <- dcast(Control_long, 
                  Country.Code ~ Year + Series.Name,
                  value.var = "Value")

方法2：使用reshape2包 #

# 加载reshape2包
library(reshape2)

# 转成长格式
Control_long <- melt(Control, 
                     id.vars = c("Country.Code", "Series.Name"),
                     measure.vars = c("2016", "2017", "2018", "2019", "2020"),
                     variable.name = "Year",
                     value.name = "Value")

# 转成宽格式
ControlM <- dcast(Control_long, 
                  Country.Code ~ Year + Series.Name,
                  value.var = "Value")

额外处理：替换缺失值标记 #

数据里的".."是缺失值标记，可以在转格式前后替换成NA：

# 替换所有".."为NA
Control[Control == ".."] <- NA
# 可选：将Value列转为数值型
Control_long[, Value := as.numeric(Value)]

内容的提问来源于stack exchange，提问作者Jana