referencing to an index dataframe and retreiving data where matches occur

Question

Hi there,

I am new to R and keen to learn but am finding myself particularly stuck on what seems to be a relatively straightforward idea.

I have 2 dataframes. The first (ants) is data pertaining to a number of sites, each site has a unique ID, some sites have more than one row of data. It look like this:

Site Date Time Temp

71 8-Jun-10 14:50:35 14.32
71 8-Jun-10 14:51:29 14.31
70 8-Jun-10 14:53:55 14.3
70 8-Jun-10 14:54:09 14.3
70 8-Jun-10 14:54:24 14.3
69 8-Jun-10 14:56:30 14.28 etc

The second (HRsites) is an index of the latitudes and longitudes associated with each site number.

Site lat_52 long_00

69 56.3075 9.1957

70 56.4207 8.9147

71 56.5208 8.6265 etc

What I would like to do is...Where the site numbers of the two dataframes match, I would like the corresponding lat and long data held in 'HRsites' to be added under additional new columns in the 'ants' dataframe.

I see that a new column is added by ants$lat_52<-

...and this is where I'm stuck, I'm not sure of the huge range of functions available on R and feel I'm perhaps not searching using quite the correct language. Any help would be hugely appreciated....even if it's jus the terms of functions I should be searching for.

Kind thanks, Joey :)

Answer 1

If I understand your question correctly, you want to merge them by "Site".

> lines <- "Site Date Time Temp
+ 71 8-Jun-10 14:50:35 14.32
+ 71 8-Jun-10 14:51:29 14.31
+ 70 8-Jun-10 14:53:55 14.3
+ 70 8-Jun-10 14:54:09 14.3
+ 70 8-Jun-10 14:54:24 14.3
+ 69 8-Jun-10 14:56:30 14.28"
> 
> (ants <- read.table(con <- textConnection(lines),header=TRUE)); close(con)
  Site     Date     Time  Temp
1   71 8-Jun-10 14:50:35 14.32
2   71 8-Jun-10 14:51:29 14.31
3   70 8-Jun-10 14:53:55 14.30
4   70 8-Jun-10 14:54:09 14.30
5   70 8-Jun-10 14:54:24 14.30
6   69 8-Jun-10 14:56:30 14.28
> 
> lines <- "Site lat_52 long_00
+ 69 56.3075 9.1957
+ 70 56.4207 8.9147
+ 71 56.5208 8.6265"
> 
> (HRsites <- read.table(con <- textConnection(lines),header=TRUE)); close(con)
  Site  lat_52 long_00
1   69 56.3075  9.1957
2   70 56.4207  8.9147
3   71 56.5208  8.6265
> 
> (Data <- merge(ants,HRsites,by="Site"))
  Site     Date     Time  Temp  lat_52 long_00
1   69 8-Jun-10 14:56:30 14.28 56.3075  9.1957
2   70 8-Jun-10 14:53:55 14.30 56.4207  8.9147
3   70 8-Jun-10 14:54:09 14.30 56.4207  8.9147
4   70 8-Jun-10 14:54:24 14.30 56.4207  8.9147
5   71 8-Jun-10 14:50:35 14.32 56.5208  8.6265
6   71 8-Jun-10 14:51:29 14.31 56.5208  8.6265