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

Rachel, welcome to SO! It really helps us if you can provide a little more of a reproducible question. This includes sample data (e.g., dput(head(x))), and expected output. Refs: stackoverflow.com/questions/5963269, stackoverflow.com/help/mcve, and stackoverflow.com/tags/r/info. — r2evans
– r2evans, Commented Jan 20, 2019 at 19:44
No, not in a comment, edit the question, please, like this it's unreadable. — Rui Barradas
– Rui Barradas, Commented Jan 20, 2019 at 20:03

Jon Spring · Accepted Answer · 2019-01-20 20:57:40Z

1

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.

answered Jan 20, 2019 at 20:57

Jon Spring

70.2k4 gold badges42 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jay.sf · Accepted Answer · 2019-01-20 21:20:36Z

1

Here comes a base R solution.

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

answered Jan 20, 2019 at 21:20

jay.sf

76.3k8 gold badges66 silver badges132 bronze badges

Collectives™ on Stack Overflow

Replace values in a data frame in R using a key

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related