Index match or vlookup to use values of one table into the corresponding row of another table

Question

I'm sure there is a fairly straight forward solution to my problem. However, my limited R-skills let me down and I didn't come across a suitable solution yet.

I have a matrix A looking like:

year    Avg_temp
1990    14.3
1991    14.6
1992    14.5
1993    14.4
1994    14.9
1995    15.1
1996    15.2

And a matrix B which looks like:

year    Tot_hoursofsun
1992    950
1993    960
1994    945

I would like to do a VLOOKUP (or index match); the objective is to add a column with the hours of sun, in case this value is present in matrix B for the years of matrix A. See below the desired output:

year    Avg_temp    Tot_hoursofsun
1990    14.3    Not available
1991    14.6    Not available
1992    14.5    950
1993    14.4    960
1994    14.9    945
1995    15.1    Not available
1996    15.2    Not available

Thanks a lot in advance!

Answer 1

You can use the lookup() function in the qdapTools package for the equivalent of a VLOOKUP.

Assuming your matrices (or data frames) are called A and B:

library(qdapTools)
A$Tot_hoursofsun <- lookup(A$year,B)

This does not input the text "Not available" into your data frame but rather fills cells with no corresponding values with NA, which is the symbol R uses for missing data.

Answer 2

You can do a merge :

m<-merge(A,B,all=TRUE)
m[is.na(m)]<-"Not available"

 m
  year Avg_temp Tot_hoursofsun
1 1990     14.3  Not available
2 1991     14.6  Not available
3 1992     14.5            950
4 1993     14.4            960
5 1994     14.9            945
6 1995     15.1  Not available
7 1996     15.2  Not available

DATA:

A<-structure(list(year = 1990:1996, Avg_temp = c(14.3, 14.6, 14.5, 
14.4, 14.9, 15.1, 15.2)), .Names = c("year", "Avg_temp"), class = "data.frame", row.names = c(NA, 
-7L))

B<-structure(list(year = 1992:1994, Tot_hoursofsun = c(950L, 960L, 
945L)), .Names = c("year", "Tot_hoursofsun"), class = "data.frame", row.names = c(NA, 
-3L))