添加带有NA值的行

Question

我有这样一个数据框架：

   indx country year  death value
1     1   Italy 2000    hiv     1
2     1   Italy 2001    hiv     2
3     1   Italy 2005    hiv     3
4     1   Italy 2000 cancer     4
5     1   Italy 2001 cancer     5
6     1   Italy 2002 cancer     6
7     1   Italy 2003 cancer     7
8     1   Italy 2004 cancer     8
9     1   Italy 2005 cancer     9
10    4  France 2000    hiv    10
11    4  France 2004    hiv    11
12    4  France 2005    hiv    12
13    4  France 2001 cancer    13
14    4  France 2002 cancer    14
15    4  France 2003 cancer    15
16    4  France 2004 cancer    16
17    2   Spain 2000    hiv    17
18    2   Spain 2001    hiv    18
19    2   Spain 2002    hiv    19
20    2   Spain 2003    hiv    20
21    2   Spain 2004    hiv    21
22    2   Spain 2005    hiv    22
23    2   Spain  ...    ...    ...

indx是一个链接到country的值(相同的country =相同的indx)。

在这个例子中，我只使用了3个国家(country)和2个疾病(death)，在原始数据框架中更多。

从2000年到2005年，我希望每一个国家都有一排。

我想得到的是：

    indx  country  year  death  value
1      1    Italy  2000    hiv      1
2      1    Italy  2001    hiv      2
3      1    Italy  2002    hiv     NA
4      1    Italy  2003    hiv     NA
5      1    Italy  2004    hiv     NA
6      1    Italy  2005    hiv      3
7      1    Italy  2000 cancer      4
8      1    Italy  2001 cancer      5
9      1    Italy  2002 cancer      6
10     1    Italy  2003 cancer      7
11     1    Italy  2004 cancer      8
12     1    Italy  2005 cancer      9
13     4   France  2000    hiv     10
14     4   France  2001    hiv     NA
15     4   France  2002    hiv     NA
16     4   France  2003    hiv     NA
17     4   France  2004    hiv     11
18     4   France  2005    hiv     12
19     4   France  2000 cancer     NA
20     4   France  2001 cancer     13
21     4   France  2002 cancer     14
22     4   France  2003 cancer     15
23     4   France  2004 cancer     16
24     4   France  2005 cancer     NA
25     2    Spain  2000    hiv     17
26     2    Spain  2001    hiv     18
27     2    Spain  2002    hiv     19
28     2    Spain  2003    hiv     20
29     2    Spain  2004    hiv     21
30     2    Spain  2005    hiv     22
31     2    Spain  ...     ...     ...

也就是说，我想在每个国家的每一种疾病的缺失年份加上value = NA行。

例如，在2002年至2004年期间，它缺乏关于意大利艾滋病病毒的数据，然后我用value = NA添加了这几行。

我怎么能这么做？

作为一个可复制的例子：

indx <- c(rep(1, times=9), rep(4, times=7), rep(2, times=6))
country <- c(rep("Italy", times=9), rep("France", times=7), rep("Spain", times=6))
year <- c(2000, 2001, 2005, 2000:2005, 2000, 2004, 2005, 2001:2004, 2000:2005)
death <- c(rep("hiv", times=3), rep("cancer", times=6), rep("hiv", times=3), rep("cancer", times=4), rep("hiv", times=6))
value <- c(1:22)
dfl <- data.frame(indx, country, year, death, value)

Answer 1

使用基数R，您可以：

# setDF(dfl) # run this first if you have a data.table
merge(expand.grid(lapply(dfl[c("country", "death", "year")], unique)), dfl, all.x = TRUE)

这首先创建country、death和year中唯一值的所有组合，然后将其与原始数据合并，以添加values，如果组合不在原始数据中，则添加NAs。

在包tidyr中，有一个特殊的函数可以用一个命令来完成这个任务：

library(tidyr)
complete(dfl, country, year, death)

Answer 2

这里是一个更长的基数R方法。您将创建两个新的data.frames，一个包含国家、年份和死亡的所有组合，另一个包含索引键。

# get data.frame with every combination of country, year, and death
dfNew <- with(df, expand.grid("country"=unique(country), "year"=unique(year),
                              "death"=unique(death)))

# get index key
indexKey <- unique(df[, c("indx", "country")])

# merge these together
dfNew <- merge(indexKey, dfNew, by="country")

# merge onto original data set
dfNew <- merge(df, dfNew, by=c("indx", "country", "year", "death"), all=TRUE)

这会返回

dfNew
   indx country year  death value
1     1   Italy 2000 cancer     4
2     1   Italy 2000    hiv     1
3     1   Italy 2001 cancer     5
4     1   Italy 2001    hiv     2
5     1   Italy 2002 cancer     6
6     1   Italy 2002    hiv    NA
7     1   Italy 2003 cancer     7
8     1   Italy 2003    hiv    NA
9     1   Italy 2004 cancer     8
10    1   Italy 2004    hiv    NA
11    1   Italy 2005 cancer     9
12    1   Italy 2005    hiv     3
13    2   Spain 2000 cancer    NA
14    2   Spain 2000    hiv    17
15    2   Spain 2001 cancer    NA
...

如果df是一个data.table，下面是对应的代码行：

# CJ is a cross-join
setkey(df, country, year, death)
dfNew <- df[CJ(country, year, death, unique=TRUE),
            .(country, year, death, value)]

indexKey <- unique(df[, .(indx, country)])

dfNew <- merge(indexKey, dfNew, by="country")

dfNew <- merge(df, dfNew, by=c("indx", "country", "year", "death"), all=TRUE)

请注意，与其使用CJ，还可以像在data.frame版本中那样使用expand.grid：

dfNew <- df[, expand.grid("country"=unique(country), "year"=unique(year),
                          "death"=unique(death))]

Answer 3

tidyr::complete帮助创建传递给它的变量的所有组合，但是如果您有两个相同的列，它将在不需要的地方过度展开或离开NA。作为解决办法，您可以使用dplyr分组(df %>% group_by(indx, country) %>% complete(death, year))或将这两列临时合并为一个：

library(tidyr)

       # merge indx and country into a single column so they won't over-expand
df %>% unite(indx_country, indx, country) %>% 
    # fill in missing combinations of new column, death, and year
    complete(indx_country, death, year) %>% 
    # separate indx and country back to how they were
    separate(indx_country, c('indx', 'country'))

# Source: local data frame [36 x 5]
# 
#     indx country  death  year value
#    (chr)   (chr) (fctr) (int) (int)
# 1      1   Italy cancer  2000     4
# 2      1   Italy cancer  2001     5
# 3      1   Italy cancer  2002     6
# 4      1   Italy cancer  2003     7
# 5      1   Italy cancer  2004     8
# 6      1   Italy cancer  2005     9
# 7      1   Italy    hiv  2000     1
# 8      1   Italy    hiv  2001     2
# 9      1   Italy    hiv  2002    NA
# 10     1   Italy    hiv  2003    NA
# ..   ...     ...    ...   ...   ...