当前位置： 文江博客话题详情

按行不同列合并数据集

发布于 2024-12-12 02:33:56 字数 2203 浏览 0 评论 0原文

我需要按行合并数据集，但它们有不同的列。如何轻松地让 R 合并行、添加缺失的列并用 NA 填充缺失的列？目前我会这样做（多次合并非常耗时）：

创建假数据...

x1<-LETTERS[1:3]
x2<-letters[1:3]
x3<-rnorm(3)
x4<-rnorm(3)
x5<-rnorm(3)

具有一些相似列、一些不同列的多个 data.frame 的示例...

data.frame(x1,x2,x3,x4,x5)
data.frame(x1,x3,x4,x5)
data.frame(x2,x3,x4,x5)
data.frame(x1,x2,x3,x4,x5)

我现在如何合并它...

DF<-data.frame(rbind(data.frame(x1,x2,x3,x4,x5),
data.frame(x1,x2,x3,x4,x5),
data.frame("x2"=rep(NA,3),data.frame(x1,x3,x4,x5)),
data.frame("x1"=rep(NA,3),data.frame(x2,x3,x4,x5))))

DF

编辑： 我尝试了建议的代码如下：

l <- list(data.frame(x1,x2,x3,x4,x5),
          data.frame(x1,x3,x4,x5),
          data.frame(x2,x3,x4,x5),
          data.frame(x1,x2,x3,x4,x5))

merger <- function(l) lapply(2:length(l), function(x) merge(l[[x-1]], l[[x]], all=TRUE)) 
while (length(l) != 1) l<-merger(l) 

l

产生：

[[1]]
  x1       x3      x4        x5 x2
1  A  0.25492 0.30160  0.259287  a
2  B -0.25937 0.45936 -0.075415  b
3  C -0.53493 1.18316  0.627335  c

不是：

> DF
     x1   x2       x3      x4        x5
1     A    a  0.25492 0.30160  0.259287
2     B    b -0.25937 0.45936 -0.075415
3     C    c -0.53493 1.18316  0.627335
4     A    a  0.25492 0.30160  0.259287
5     B    b -0.25937 0.45936 -0.075415
6     C    c -0.53493 1.18316  0.627335
7     A <NA>  0.25492 0.30160  0.259287
8     B <NA> -0.25937 0.45936 -0.075415
9     C <NA> -0.53493 1.18316  0.627335
10 <NA>    a  0.25492 0.30160  0.259287
11 <NA>    b -0.25937 0.45936 -0.075415
12 <NA>    c -0.53493 1.18316  0.627335

编辑2： 抱歉延长我原来的帖子，但我的低代表不允许我回答我自己的问题。

结合 Jaron 和 daroczig 的回答就得到了我想要的结果。我不想将每个数据帧分配给一个对象，因此将它们组合为一个列表，然后使用 rbind fill 效果非常好（请参见下面的代码）

谢谢你们俩！

x1<-LETTERS[1:3] 
x2<-letters[1:3] 
x3<-rnorm(3) 
x4<-rnorm(3) 
x5<-rnorm(3)

DFlist<-list(data.frame(x1,x2,x3,x4,x5), 
             data.frame(x1,x3,x4,x5),
             data.frame(x2,x3,x4,x5), 
             data.frame(x1,x2,x3,x4,x5))

rbind.fill(DFlist)
原文

I have the need to merge data sets by row but they have differing columns. How can I easily get R to merge the rows, add missing columns and fill in the missing columns with NAs? Currently I would do it like this (very time consuming for multiple merges):

Creating fake data...

x1<-LETTERS[1:3]
x2<-letters[1:3]
x3<-rnorm(3)
x4<-rnorm(3)
x5<-rnorm(3)

Example of multiple data.frames with some similar columns, some different...

data.frame(x1,x2,x3,x4,x5)
data.frame(x1,x3,x4,x5)
data.frame(x2,x3,x4,x5)
data.frame(x1,x2,x3,x4,x5)

How I merge it now...

DF<-data.frame(rbind(data.frame(x1,x2,x3,x4,x5),
data.frame(x1,x2,x3,x4,x5),
data.frame("x2"=rep(NA,3),data.frame(x1,x3,x4,x5)),
data.frame("x1"=rep(NA,3),data.frame(x2,x3,x4,x5))))

DF

EDIT:
I tried the suggested code as follows:

l <- list(data.frame(x1,x2,x3,x4,x5),
          data.frame(x1,x3,x4,x5),
          data.frame(x2,x3,x4,x5),
          data.frame(x1,x2,x3,x4,x5))

merger <- function(l) lapply(2:length(l), function(x) merge(l[[x-1]], l[[x]], all=TRUE)) 
while (length(l) != 1) l<-merger(l) 

l

Which yields:

[[1]]
  x1       x3      x4        x5 x2
1  A  0.25492 0.30160  0.259287  a
2  B -0.25937 0.45936 -0.075415  b
3  C -0.53493 1.18316  0.627335  c

Not:

> DF
     x1   x2       x3      x4        x5
1     A    a  0.25492 0.30160  0.259287
2     B    b -0.25937 0.45936 -0.075415
3     C    c -0.53493 1.18316  0.627335
4     A    a  0.25492 0.30160  0.259287
5     B    b -0.25937 0.45936 -0.075415
6     C    c -0.53493 1.18316  0.627335
7     A <NA>  0.25492 0.30160  0.259287
8     B <NA> -0.25937 0.45936 -0.075415
9     C <NA> -0.53493 1.18316  0.627335
10 <NA>    a  0.25492 0.30160  0.259287
11 <NA>    b -0.25937 0.45936 -0.075415
12 <NA>    c -0.53493 1.18316  0.627335

EDIT 2: Sorry to extend my original post but my low rep will not allow me to answer my own question.

Combining Jaron and daroczig's responses results in exactly what I want. I don't want to assign each data frame to an object, so combining them as a list and then using rbind fill works very nicely (see code below)

Thank you to both of you!

x1<-LETTERS[1:3] 
x2<-letters[1:3] 
x3<-rnorm(3) 
x4<-rnorm(3) 
x5<-rnorm(3)

DFlist<-list(data.frame(x1,x2,x3,x4,x5), 
             data.frame(x1,x3,x4,x5),
             data.frame(x2,x3,x4,x5), 
             data.frame(x1,x2,x3,x4,x5))

rbind.fill(DFlist)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

九命猫 2024-12-19 02:33:56

在我明白你在寻找什么之前，我不得不读你的问题好几次，但也许你想要来自 plyr 的 rbind.fill ：

d1 <- data.frame(x1,x2,x3,x4,x5)
d2 <- data.frame(x1,x3,x4,x5)
d3 <- data.frame(x2,x3,x4,x5)
d4 <- data.frame(x1,x2,x3,x4,x5)

> rbind.fill(d1,d4,d2,d3)
     x1   x2        x3         x4         x5
1     A    a 1.1216923  0.9236393  0.2749292
2     B    b 1.1913278  1.1145664 -0.5070576
3     C    c 0.2837657 -0.6631544 -1.0675885
4     A    a 1.1216923  0.9236393  0.2749292
5     B    b 1.1913278  1.1145664 -0.5070576
6     C    c 0.2837657 -0.6631544 -1.0675885
7     A <NA> 1.1216923  0.9236393  0.2749292
8     B <NA> 1.1913278  1.1145664 -0.5070576
9     C <NA> 0.2837657 -0.6631544 -1.0675885
10 <NA>    a 1.1216923  0.9236393  0.2749292
11 <NA>    b 1.1913278  1.1145664 -0.5070576
12 <NA>    c 0.2837657 -0.6631544 -1.0675885

I had to read your question quite a few times before I understood what you were looking for, but maybe you want rbind.fill from plyr:

d1 <- data.frame(x1,x2,x3,x4,x5)
d2 <- data.frame(x1,x3,x4,x5)
d3 <- data.frame(x2,x3,x4,x5)
d4 <- data.frame(x1,x2,x3,x4,x5)

> rbind.fill(d1,d4,d2,d3)
     x1   x2        x3         x4         x5
1     A    a 1.1216923  0.9236393  0.2749292
2     B    b 1.1913278  1.1145664 -0.5070576
3     C    c 0.2837657 -0.6631544 -1.0675885
4     A    a 1.1216923  0.9236393  0.2749292
5     B    b 1.1913278  1.1145664 -0.5070576
6     C    c 0.2837657 -0.6631544 -1.0675885
7     A <NA> 1.1216923  0.9236393  0.2749292
8     B <NA> 1.1913278  1.1145664 -0.5070576
9     C <NA> 0.2837657 -0.6631544 -1.0675885
10 <NA>    a 1.1216923  0.9236393  0.2749292
11 <NA>    b 1.1913278  1.1145664 -0.5070576
12 <NA>    c 0.2837657 -0.6631544 -1.0675885
回复 原文
我爱人 2024-12-19 02:33:56

使用带有 fill = TRUE 选项的 data.table::rbindlist ：

data.table::rbindlist(
  list(data.frame(x1,x2,x3,x4,x5), 
       data.frame(x1,x3,x4,x5),
       data.frame(x2,x3,x4,x5), 
       data.frame(x1,x2,x3,x4,x5)),
  fill = TRUE)

Using data.table::rbindlist with fill = TRUE option:

data.table::rbindlist(
  list(data.frame(x1,x2,x3,x4,x5), 
       data.frame(x1,x3,x4,x5),
       data.frame(x2,x3,x4,x5), 
       data.frame(x1,x2,x3,x4,x5)),
  fill = TRUE)
回复 原文
尘世孤行 2024-12-19 02:33:56

假设您的数据框位于一个不错的列表中：

l <- list(
    data.frame(x2=rnorm(3),x1=rnorm(3)),
    data.frame(x1=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3),x5=rnorm(3)),
    data.frame(x5=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3),x1=rnorm(3)),
    data.frame(x5=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3)),
    data.frame(x2=rnorm(3),x1=rnorm(3),x3=rnorm(3),x4=rnorm(3))
)

抓住第一个数据框，然后（按照@joran的建议）合并所有其余数据，例如。清晰的循环：

r <- l[[1]]
for (i in 2:length(l)) {
    r <- merge(r, l[[i]], all=TRUE)
}

查看 r：

> r
         x2        x3       x4       x1        x5
1  -1.72436 -0.774652  3.10001  0.23249 -1.278216
2  -1.25640        NA       NA  0.32997        NA
3  -1.00652 -0.946254  1.17313       NA  2.014517
4  -0.53770 -0.466626 -0.63369 -1.48375 -1.135515
5  -0.49787        NA       NA -0.34020        NA
6  -0.49704 -0.054175  0.85477       NA  0.831706
7   0.13027  0.421750 -0.18126 -0.65452  0.476576
8   0.18519 -1.006994  0.15141  0.66808        NA
9   0.33954 -0.224478  1.38596       NA  0.145807
10  0.57782  1.126430 -0.89582  0.80199        NA
11  0.59149 -0.447669  0.74855 -1.65790  0.059767
12  0.61374  0.751528 -1.93715  0.40125 -0.148243
13  0.89399  0.758481 -0.94801  0.05084        NA
14  0.94200        NA       NA  0.24945        NA
15  0.99509  0.586097 -0.91455 -0.49909  0.823696

我不喜欢那个循环，所以写了一些递归的东西：

> merger <- function(l) lapply(2:length(l), function(x) merge(l[[x-1]], l[[x]], all=TRUE))
> while (length(l) != 1) l<-merger(l)
> l
[[1]]
         x2       x1        x3       x4        x5
1  -1.72436  0.23249 -0.774652  3.10001 -1.278216
2  -1.25640  0.32997        NA       NA        NA
3  -1.00652       NA -0.946254  1.17313  2.014517
4  -0.53770 -1.48375 -0.466626 -0.63369 -1.135515
5  -0.49787 -0.34020        NA       NA        NA
6  -0.49704       NA -0.054175  0.85477  0.831706
7   0.13027 -0.65452  0.421750 -0.18126  0.476576
8   0.18519  0.66808 -1.006994  0.15141        NA
9   0.33954       NA -0.224478  1.38596  0.145807
10  0.57782  0.80199  1.126430 -0.89582        NA
11  0.59149 -1.65790 -0.447669  0.74855  0.059767
12  0.61374  0.40125  0.751528 -1.93715 -0.148243
13  0.89399  0.05084  0.758481 -0.94801        NA
14  0.94200  0.24945        NA       NA        NA
15  0.99509 -0.49909  0.586097 -0.91455  0.823696

Let us say you have your data frames in a nice list:

l <- list(
    data.frame(x2=rnorm(3),x1=rnorm(3)),
    data.frame(x1=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3),x5=rnorm(3)),
    data.frame(x5=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3),x1=rnorm(3)),
    data.frame(x5=rnorm(3),x2=rnorm(3),x3=rnorm(3),x4=rnorm(3)),
    data.frame(x2=rnorm(3),x1=rnorm(3),x3=rnorm(3),x4=rnorm(3))
)

Grab the first one and (as @joran suggested) merge all the rest to that with eg. a lucid loop:

r <- l[[1]]
for (i in 2:length(l)) {
    r <- merge(r, l[[i]], all=TRUE)
}

Check out r:

> r
         x2        x3       x4       x1        x5
1  -1.72436 -0.774652  3.10001  0.23249 -1.278216
2  -1.25640        NA       NA  0.32997        NA
3  -1.00652 -0.946254  1.17313       NA  2.014517
4  -0.53770 -0.466626 -0.63369 -1.48375 -1.135515
5  -0.49787        NA       NA -0.34020        NA
6  -0.49704 -0.054175  0.85477       NA  0.831706
7   0.13027  0.421750 -0.18126 -0.65452  0.476576
8   0.18519 -1.006994  0.15141  0.66808        NA
9   0.33954 -0.224478  1.38596       NA  0.145807
10  0.57782  1.126430 -0.89582  0.80199        NA
11  0.59149 -0.447669  0.74855 -1.65790  0.059767
12  0.61374  0.751528 -1.93715  0.40125 -0.148243
13  0.89399  0.758481 -0.94801  0.05084        NA
14  0.94200        NA       NA  0.24945        NA
15  0.99509  0.586097 -0.91455 -0.49909  0.823696

I didn't like that loop, so wrote some recursive stuff:

> merger <- function(l) lapply(2:length(l), function(x) merge(l[[x-1]], l[[x]], all=TRUE))
> while (length(l) != 1) l<-merger(l)
> l
[[1]]
         x2       x1        x3       x4        x5
1  -1.72436  0.23249 -0.774652  3.10001 -1.278216
2  -1.25640  0.32997        NA       NA        NA
3  -1.00652       NA -0.946254  1.17313  2.014517
4  -0.53770 -1.48375 -0.466626 -0.63369 -1.135515
5  -0.49787 -0.34020        NA       NA        NA
6  -0.49704       NA -0.054175  0.85477  0.831706
7   0.13027 -0.65452  0.421750 -0.18126  0.476576
8   0.18519  0.66808 -1.006994  0.15141        NA
9   0.33954       NA -0.224478  1.38596  0.145807
10  0.57782  0.80199  1.126430 -0.89582        NA
11  0.59149 -1.65790 -0.447669  0.74855  0.059767
12  0.61374  0.40125  0.751528 -1.93715 -0.148243
13  0.89399  0.05084  0.758481 -0.94801        NA
14  0.94200  0.24945        NA       NA        NA
15  0.99509 -0.49909  0.586097 -0.91455  0.823696
回复 原文
~没有更多了~

关于作者

凉风有信

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

已经忘了多久

文章 0 评论 0

15867725375

文章 0 评论 0

LonelySnow

文章 0 评论 0

走过海棠暮

文章 0 评论 0

轻许诺言

文章 0 评论 0

信馬由缰

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文