Skip to content

Merging data tables

4 messages · Neotropical bat risk assessments, Djordje Bajic, arun +1 more

arun
# 
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.