[R] merging 2 frames while keeping all the entries from the "reference" frame

[Dimitri Liakhovitski]

Thanks a lot - these solutions are much more elegant than my own:

new.data<-merge(mydata[mydata$group %in%
levels(mydata$group)[1],],reference,by="mydate",all.x=T,all.y=T)
new.data[["group"]][is.na(new.data[["group"]])]<-levels(mydata$group)[1]
new.data[["values"]][is.na(new.data[["values"]])]<-0

# Continue Merging - starting with Group2:
for(i in 2:nlevels(mydata$group)){  #i<-2
	temp<-merge(mydata[mydata$group %in%
levels(mydata$group)[i],],reference,by="mydate",all.x=T,all.y=T)
	temp[["group"]][is.na(temp[["group"]])]<-levels(mydata$group)[i]
	temp[["values"]][is.na(temp[["values"]])]<-0
	new.data<-rbind(new.data,temp)
}

Dimitri

On Mon, Apr 4, 2011 at 3:07 PM, Henrique Dallazuanna <wwwhsd at gmail.com> wrote:
> Try this:
>
>  merge(mydata, cbind(reference, group = rep(unique(mydata$group), each
> = nrow(reference))), all = TRUE)
>
> On Mon, Apr 4, 2011 at 2:24 PM, Dimitri Liakhovitski
> <dimitri.liakhovitski at gmail.com> wrote:
>> To clarify just in case, here is the result I am trying to get:
>>
>> mydate  group   values
>> 12/29/2008      Group1  0.453466522
>> 1/5/2009        Group1  NA
>> 1/12/2009       Group1  0.416548943
>> 1/19/2009       Group1  2.066275155
>> 1/26/2009       Group1  2.037729638
>> 2/2/2009        Group1  -0.598040483
>> 2/9/2009        Group1  1.658999227
>> 2/16/2009       Group1  -0.869325211
>> 12/29/2008      Group2  NA
>> 1/5/2009        Group2  NA
>> 1/12/2009       Group2  NA
>> 1/19/2009       Group2  0.375284194
>> 1/26/2009       Group2  0.706785401
>> 2/2/2009        Group2  NA
>> 2/9/2009        Group2  2.104937151
>> 2/16/2009       Group2  2.880393978
>>
>>
>>
>> On Mon, Apr 4, 2011 at 1:09 PM, Dimitri Liakhovitski
>> <dimitri.liakhovitski at gmail.com> wrote:
>>> Hello!
>>> I have my data frame "mydata" (below) and data frame "reference" -
>>> that contains all the dates I would like to be present in the final
>>> data frame.
>>> I am trying to merge them so that the the result data frame contains
>>> all 8 dates in both subgroups (i.e., Group1 should have 8 rows and
>>> Group2 too). But when I merge it it's not coming out this way. Any
>>> hint would be greatly appreciated!
>>> Dimitri
>>>
>>> mydata<-data.frame(mydate=rep(seq(as.Date("2008-12-29"), length = 8,
>>> by = "week"),2),
>>> group=c(rep("Group1",8),rep("Group2",8)),values=rnorm(16,1,1))
>>> (reference);(mydata)
>>> set.seed(1234)
>>> out<-sample(1:16,5,replace=F)
>>> mydata<-mydata[-out,]; dim(mydata)
>>> (mydata)
>>>
>>> # "reference" contains the dates I want to be present in the final data frame:
>>> reference<-data.frame(mydate=seq(as.Date("2008-12-29"), length = 8, by
>>> = "week"))
>>>
>>> # Merging:
>>> new.data<-merge(mydata,reference,by="mydate",all.x=T,all.y=T)
>>> new.data<-new.data[order(new.data$group,new.data$mydate),]
>>> (new.data)
>>> # my new.data contains only 7 rows in Group 1 and 4 rows in Group 2
>>>
>>>
>>> --
>>> Dimitri Liakhovitski
>>> Ninah Consulting
>>>
>>
>>
>>
>> --
>> Dimitri Liakhovitski
>> Ninah Consulting
>> www.ninah.com
>>
>> ______________________________________________
>> R-help at 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.
>>
>
>
>
> --
> Henrique Dallazuanna
> Curitiba-Paraná-Brasil
> 25° 25' 40" S 49° 16' 22" O
>



-- 
Dimitri Liakhovitski
Ninah Consulting
www.ninah.com