HI, YOu may try ?merge() or ?join() from library(plyr) bat_activity<-read.table(text=" Date Time Label Number 6/3/2011 10:01 Tadbra 2 6/3/2011 10:02 Tadbra 4 6/3/2011 10:08 Tadbra 1 6/3/2011 10:13 Tadbra 2 6/3/2011 10:49 Tadbra 2 6/3/2011 10:51 Tadbra 2 6/3/2011 10:52 Tadbra 4 ",sep="",header=TRUE,stringsAsFactors=FALSE)
Weather<-read.table(text=" date time Temp_I Temp_E RH mph_a mph_x 6/3/2011 0:00 15 15.7 30.4 4.4 15.5 ----------------------------------------------------------------------- -------------------------------------------------------------------- 6/3/2011 11:00 29.5 29.4 9.8 1.8 4 6/3/2011 11:30 30 29.9 9.2 2.7 7.8 ",sep="",header=TRUE,stringsAsFactors=FALSE) bat_activity1<-data.frame(dateTime=as.POSIXct(paste(bat_activity[,1],bat_activity[,2]),format="%m/%d/%Y %H:%M"),bat_activity[,3:4]) Weather1<-data.frame(dateTime=as.POSIXct(paste(Weather[,1],Weather[,2]),format="%m/%d/%Y %H:%M"),Weather[,3:7]) res<- merge(bat_activity1,Weather1,by="dateTime",all=TRUE) head(res) #here there are NAs because there are no corresponding values in one of the dataset # dateTime Label Number Temp_I Temp_E RH mph_a mph_x #1 2011-06-03 00:00:00 <NA> NA 15.0 15.7 30.4 4.4 15.5 #2 2011-06-03 00:30:00 <NA> NA 15.0 15.2 31.6 5.7 11.2 #3 2011-06-03 01:00:00 <NA> NA 15.0 15.1 31.3 10.3 17.5 #4 2011-06-03 01:30:00 <NA> NA 14.0 13.6 44.5 4.7 12.1 #5 2011-06-03 02:00:00 <NA> NA 12.5 13.2 37.9 2.1 6.5 #6 2011-06-03 02:30:00 <NA> NA 12.5 13.5 35.3 6.3 10.1 Also, you mentioned about merging more than 2 datasets. In that case, use: Reduce(function(...) merge(...,by="dateTime"),list(bat_activity1,Weather1,dat3,dat4)) #or library(reshape) merge_recurse(list(bat_activity1,Weather,dat3,dat4), by="dateTime") Hope this helps. A.K. ----- Original Message ----- From: Neotropical bat risk assessments <neotropical.b...@gmail.com> To: r-help@r-project.org; dedu...@googlegroups.com Cc: Sent: Friday, December 28, 2012 6:46 AM Subject: [R] Merging data tables Hi all, I am trying to merge several data sets and end up with a long data format by date & time so I can run correlations and plots. I am using Deducer as an R GUI but can just use the R console if easier. The data sets are weather with wind speed, relative humidity and temperatures by date and minute and bat activity with date, time, label, and an activity index number. The bat activity is only during the nocturnal time frames while the weather data was recorded for 24 hours. Therefore a lot of weather data with no related activity for bats. I have failed so far to achieve what I need and tried plyr and reshape2. There are many Null data rows with no data or 0 (zero) for wind speed.. What other tools steps would be more appropriate short of manually in Excel cutting and pasting for a day or more? Data formats are: bat activity Date Time Label Number 6/3/2011 10:01 Tadbra 2 6/3/2011 10:02 Tadbra 4 6/3/2011 10:08 Tadbra 1 6/3/2011 10:13 Tadbra 2 6/3/2011 10:49 Tadbra 2 6/3/2011 10:51 Tadbra 2 6/3/2011 10:52 Tadbra 4 Weather: date time Temp_I Temp_E RH mph_a mph_x 6/3/2011 0:00 15 15.7 30.4 4.4 15.5 6/3/2011 0:30 15 15.2 31.6 5.7 11.2 6/3/2011 1:00 15 15.1 31.3 10.3 17.5 6/3/2011 1:30 14 13.6 44.5 4.7 12.1 6/3/2011 2:00 12.5 13.2 37.9 2.1 6.5 6/3/2011 2:30 12.5 13.5 35.3 6.3 10.1 6/3/2011 3:00 12 12.1 37.7 3 7.4 6/3/2011 3:30 11.5 11.5 38.7 3.4 6 6/3/2011 4:00 10 9.9 52.7 1.4 4.2 6/3/2011 4:30 9.5 9.1 43.2 1.3 3.8 6/3/2011 5:00 8 8.7 59.2 1.2 3.1 6/3/2011 5:30 7 8 62.5 1.1 4.2 6/3/2011 6:00 6 7.8 47.8 0.5 2.2 6/3/2011 6:30 7.5 11.5 37.5 1.7 3.8 6/3/2011 7:00 10.5 14 33.1 0.6 2.2 6/3/2011 7:30 14 17.3 32.1 1.6 3.6 6/3/2011 8:00 17.5 20.3 23.9 0.4 2 6/3/2011 8:30 21.5 22.8 20.7 0.4 1.8 6/3/2011 9:00 24.5 24.9 14.1 0.3 2.2 6/3/2011 9:30 26 26.9 20.3 1.7 5.6 6/3/2011 10:00 27.5 27.4 20.7 2.5 6.5 6/3/2011 10:30 28.5 29.8 10 1.6 4.2 6/3/2011 11:00 29.5 29.4 9.8 1.8 4 6/3/2011 11:30 30 29.9 9.2 2.7 7.8 Tnx for any suggestions, Bruce [[alternative HTML version deleted]] ______________________________________________ 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. ______________________________________________ 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.
