Subject: Re: [R] merge data frames with same column names of different lengths and missing values
To: "Phil Spector" <spector@stat.berkeley.edu>
Date: Saturday, March 7, 2009, 5:01 PM

Phil,
Thank you - this is very helpful. However I realized that with my real data sets (not the example I have here), I also have different numbers of columns in each data frame. rbind doesn't seem to like this. Here's a modified example:

x <- data.frame(item1=c(NA,NA,3,4,5), item2=c(1,NA,NA,4,5), item3=c(NA,2,NA,4,NA), id=1:5)
y <- data.frame(item1=c(NA,2,NA,4,5,6), item2=c(NA,NA,3,4,5,NA), id=1:6)

rbind(x,y)
Error in rbind(deparse.level, ...) : 
  numbers of columns of arguments do not match

Any ideas?
Thanks,
Steve


--- On Sat, 3/7/09, Phil Spector <spector@stat.berkeley.edu> wrote:
From: Phil Spector <spector@stat.berkeley.edu>
Subject: Re: [R] merge data frames with
 same column names of different lengths and missing values
To: "Steven Lubitz" <slubitz1@yahoo.com>
Date: Saturday, March 7, 2009, 1:56 AM

Steven -
  I believe this gives the output that you desire:

> xy = rbind(x,y)
>
aggregate(subset(xy,select=-id),xy['id'],function(x)rev(x[!is.na(x)])[1])
   id item1 item2
1  1    NA     1
2  2     2    NA
3  3     3     3
4  4     4     4
5  5     5     5
6  6     6    NA

  But I think what merge x y; by id; would give you is

> aggregate(subset(xy,select=-id),xy['id'],function(x)x[length(x)])
   id item1 item2
1  1    NA    NA
2  2     2    NA
3  3    NA     3
4  4     4     4
5  5     5     5
6  6     6    NA

                                        - Phil Spector
 					 Statistical Computing Facility
 					 Department of Statistics
 					 UC Berkeley
 					
 spector@stat.berkeley.edu





On Fri, 6 Mar 2009, Steven Lubitz wrote:

>
> Hello, I'm switching over from SAS to R and am having trouble merging
data frames. The data frames have several columns with the same name, and each
has a different number of rows. Some of the values are missing from cells with
the same column names in each data frame. I had hoped that when I merged the
dataframes, every column with the same name would be merged, with the value in a
complete cell overwriting the value in an empty cell from the other data frame.
I cannot seem to achieve this result, though I've tried several merge
adaptations:
>
> x <- data.frame(item1=c(NA,NA,3,4,5), item2=c(1,NA,NA,4,5), id=1:5)
> y <- data.frame(item1=c(NA,2,NA,4,5,6), item2=c(NA,NA,3,4,5,NA),
id=1:6)
>
>
> merge(x,y,by="id") #I lose observations here (n=1 in this
example), and my items are
 duplicated - I do not want this result
>  id item1.x item2.x item1.y item2.y
> 1  1      NA       1      NA      NA
> 2  2      NA      NA       2      NA
> 3  3       3      NA      NA       3
> 4  4       4       4       4       4
> 5  5       5       5       5       5
>
>
> merge(x,y,by=c("id","item1","item2")) #again
I lose observations (n=4 here) and do not want this result
>  id item1 item2
> 1  4     4     4
> 2  5     5     5
>
>
>
merge(x,y,by=c("id","item1","item2"),all.x=T,all.y=T)
#my rows are duplicated and the NA values are retained - I instead want one row
per ID
>  id item1 item2
> 1  1    NA     1
> 2  1    NA    NA
> 3  2     2    NA
> 4  2    NA    NA
> 5  3     3    NA
> 6  3    NA     3
> 7  4     4     4
> 8  5     5     5
> 9  6     6    NA
>
> In reality I
 have multiple data frames with numerous columns, all with
this problem. I can do the merge seamlessly in SAS, but am trying to learn and
stick with R for my analyses. Any help would be greatly appreciated.
>
> Steve Lubitz
> Cardiovascular Research Fellow, Brigham and Women's Hospital and
Massachusetts General Hospital
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>



      


      
	[[alternative HTML version deleted]]


