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]]
see ?merge, for your dataframes, the following should work: merge(bat_activity, weather, by=c(date, time), all=T) 2012/12/28 Neotropical bat risk assessments <neotropical.bats@gmail.com>> 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. >[[alternative HTML version deleted]]
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.bats at gmail.com> To: r-help at r-project.org; deducer at 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 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.
HI, It is related to the memory. Check these links for discussions regarding this matter: http://r.789695.n4.nabble.com/Memory-Problem-td853869.html http://r.789695.n4.nabble.com/Memory-issues-td3220252.html A.K. ________________________________ From: Neotropical bat risk assessments <neotropical.bats at gmail.com> To: arun <smartpink111 at yahoo.com> Sent: Friday, December 28, 2012 9:19 AM Subject: Re: [R] Merging data tables Hi, I was able to get the merge to work OK, I think... Bats_weather<-merge(sewage.Wind_Temp.temp,Tabr_all2.temp,by.x="row.names",by.y="row.names",incomparables ????= NA,all.x =F,all.y =T)> rm(list=c("sewage.Wind_Temp.temp","Tabr_all2.temp")) > dput(Bats_weather,'C:/=Bat data????working/Rainey/Sewage/Tadbra_weather.robj')> Bats_weather[,9]<-as.character(Bats_weather[,9]) > dput(Bats_weather,'C:/=Bat data????working/Rainey/Sewage/Tadbra_weather.robj')> obj_a <- t.test(x = Bats_weather$Number,y ????Bats_weather$Temp_E,paired = TRUE) > print(obj_a)But I can not run the correlations...> ggcorplot(cor.mat=corr.mat,data=Bats_weather,+ cor_text_limits=c(5,20), + line.method="lm") Error: cannot allocate vector of size 1.6 Gb In addition: Warning message: In get(x, envir = this, inherits = inh) : ? restarting interrupted promise evaluation> rm('corr.mat')-- Bruce W. Miller, PhD. Neotropical bat risk assessments If we lose the bats, we may lose much of the tropical vegetation and the lungs of the planet Using acoustic sampling to map species distributions for >15 years. Providing Interactive identification keys to the vocal signatures of New World Bats For various project details see: https://sites.google.com/site/batsoundservices/