How to join data from 2 different csv-files in R?

Question

I have the following problem: in a csv-file I have a column for species, one for transect, one for the year and one for the AUC. In another csv-file I have a column for transect, one for the year, one for precipitation and one for temperature. Now I would like to join the files in R in a way, that I can have the columns for species and AUC from the second csv and the columns for the rest from the first csv. In the end I'd like to get a file with transect_id, year, day, month, species, regional_gam(=AUC), precipitation and LST(=temperature). So basically the precipitation-/ LST-values from TR001 for every day in 2008 need to be assigned to every species which has an AUC-value for 2008 and TR001. Thanks!

Answer 1

Use read.csv and then merge.

Load the two csv files into R. (Don't forget to make sure their common variables share the same name!).

df1<-read.csv(dat1,head=T)
df2<-read.csv(dat2,head=T)

Merge the dataframes together by their shared variables and add argument all.x=T (the default) to ensure all rows are kept from your database containing species.

merge(df1,df2,by=c('transect_id','year'),all.x=T)

To see this in action using test data:

test<-data.frame(sp=c(rep(letters[1:10],2)),t=c(rep(1:3,2,20)),y=c(rep(2000:2008,len=20)),AUC=1:20)
test2<-data.frame(t=c(rep(1:3,2,9)),y=c(rep(2000:2008,len=9)),ppt=c(1:9),temp=c(11:19))

merge(test,test2,by=c('t','y'),all.x=T)