Replace values in a data frame in R using a key

Question

I have a data set of geographical codes for local councils and counties (geog_lookup) - it provides a key to which councils belong to which counties.

I also have a 'messy' data set (test) that gives information for both counties and councils and I want to convert it all to councils using the key.

Does anyone know how to do this? Here is what I have so far?

for (i in geog_lookup[,1]){
  test[,1]<-replace(test[,1], n, value="i")}


> head(geog_lookup)
   DataZone InterZone   Council
1 S01000001 S02000003 S12000033
2 S01000002 S02000001 S12000033
3 S01000003 S02000001 S12000033
4 S01000004 S02000001 S12000033
5 S01000005 S02000003 S12000033
6 S01000006 S02000003 S12000033



> head(test)
   Location Year Reference.Area Dwellings.AC Dwellings.DE Dwellings.FH Total.Crime Hosp.Admissions House.Price
16        i 2008      S01000001         43.5         32.2         24.4          NA            1555      148500
17        i 2009      S01000001         43.5         32.2         24.4          NA             917      122750
18        i 2010      S01000001         44.3         31.7         24.0          NA             875      135000
19        i 2011      S01000001         44.0         31.5         24.5          NA             870      179000
20        i 2012      S01000001         44.3         31.1         24.6          NA            1174      155556
21        i 2013      S01000001         44.2         30.8         25.0          NA              NA      118118
   Job.Seekers Waste.Percapita Percent.Recycling Proximity.Derelict
16       0.350              NA                NA               95.2
17       0.700              NA                NA               95.2
18       0.325              NA                NA               95.0
19       0.975              NA                NA               95.0
20       0.650              NA                NA               93.1
21          NA              NA                NA               90.9

Answer 1

Here comes a base R solution.

merge(test, geog_lookup, by.x="Reference.Area", by.y="DataZone")

Answer 2

Here's how to do that with dplyr:

library(dplyr)
test2 <- test %>%
  left_join(geog_lookup, by = c("Reference.Area" = "DataZone"))

This will do a database join (like VLOOKUP in Excel) that will add the columns from geog_lookup where the DataZone there matches the Reference.Area in your source data.