[R] Merging data tables

[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.