合并数据帧并使用重复的列和相应的日期填充空白-解网

问：

我有三个数据帧（df1、df2 和 df3）。我想合并它们，并填补彼此的空白。例如：df1 包含 1990 年至 1993 年的美国数据，df2 包含 1994 年至 1999 年的美国数据。当我使用reduce函数合并它们时，我得到重复的数据（USA.x，USA.y），而不仅仅是具有所有连续日期和值的USA。

我做了如下：

df1 <- data.frame(
  stringsAsFactors = FALSE, 
  Date = c("01/01/1990", "01/01/1991", 
           "01/01/1992", "01/01/1993", "01/01/1994", "01/01/1995"), 
  USA = c(1, 4, 2, 1, NA, NA), 
  FRANCE = c(4, 4, 2, 5, NA, NA), 
  ITALY = c(1, 4, 5, 2, NA, NA))

df2 <-data.frame(
  stringsAsFactors = FALSE, 
  Date = c("01/01/1994", "01/01/1995", 
           "01/01/1996", "01/01/1997", "01/01/1998", "01/01/1999"), 
  USA = c(3, 3, 1, 4, 3, 1), 
  FRANCE = c(2, 5, 2, 5, 5, 1), 
  MEXICO = c(4, 1, 4, 3, NA, NA))

df3 <- data.frame(
  stringsAsFactors = FALSE, 
  Date = c("01/01/1998", "01/01/1999", 
           "01/01/2000", "01/01/2001", "01/01/2002", "01/01/2003"), 
  MEXICO = c(3, 3, 5, 4, 2, 3), 
  BELGIUM = c(4, 2, 1, 4, 5, 1))


df_list <- list(df1, df2, df3)

选项 1

dfall1 <- Reduce(function(x, y) merge(x, y, all=TRUE), df_list, accumulate=FALSE)
View(dfall1)

结果 dfall1

它根据重复的名称集成列，但重复日期。每个 contry 只有一列，但同一日期有 2 行：一行包含 NA，另一行包含来自另一个数据框的值。

选项 2

dfall2 <- Reduce(function(x, y) merge(x, y, all=TRUE, by = "Date"), df_list, accumulate=FALSE)
View(dfall2)

结果 dfall2

列名不重复的日期行将重命名为 .x 和 .y。

问题如何避免重复的行和列。我希望所有数据都能根据其列名和相应的日期进行整合

我想以以下内容结束：

任何帮助将不胜感激。提前致谢。

r 合并重塑 reduce

> lapply(df_list, reshape2:::melt.data.frame, id.vars='Date') |> 
+   do.call(what='rbind') |> na.omit() |> reshape2::dcast(Date ~ variable) 
         Date USA FRANCE ITALY MEXICO BELGIUM
1  01/01/1990   1      4     1     NA      NA
2  01/01/1991   4      4     4     NA      NA
3  01/01/1992   2      2     5     NA      NA
4  01/01/1993   1      5     2     NA      NA
5  01/01/1994   3      2    NA      4      NA
6  01/01/1995   3      5    NA      1      NA
7  01/01/1996   1      2    NA      4      NA
8  01/01/1997   4      5    NA      3      NA
9  01/01/1998   3      5    NA      3       4
10 01/01/1999   1      1    NA      3       2
11 01/01/2000  NA     NA    NA      5       1
12 01/01/2001  NA     NA    NA      4       4
13 01/01/2002  NA     NA    NA      2       5
14 01/01/2003  NA     NA    NA      3       1

数据：

> dput(df_list)
list(structure(list(Date = c("01/01/1990", "01/01/1991", "01/01/1992", 
"01/01/1993", "01/01/1994", "01/01/1995"), USA = c(1, 4, 2, 1, 
NA, NA), FRANCE = c(4, 4, 2, 5, NA, NA), ITALY = c(1, 4, 5, 2, 
NA, NA)), class = "data.frame", row.names = c(NA, -6L)), structure(list(
    Date = c("01/01/1994", "01/01/1995", "01/01/1996", "01/01/1997", 
    "01/01/1998", "01/01/1999"), USA = c(3, 3, 1, 4, 3, 1), FRANCE = c(2, 
    5, 2, 5, 5, 1), MEXICO = c(4, 1, 4, 3, NA, NA)), class = "data.frame", row.names = c(NA, 
-6L)), structure(list(Date = c("01/01/1998", "01/01/1999", "01/01/2000", 
"01/01/2001", "01/01/2002", "01/01/2003"), MEXICO = c(3, 3, 5, 
4, 2, 3), BELGIUM = c(4, 2, 1, 4, 5, 1)), class = "data.frame", row.names = c(NA, 
-6L)))

合并数据帧并使用重复的列和相应的日期填充空白

Merge dataframes and fill gaps with duplicated columns and corresponding dates

评论

评论