Edit GISJOIN attribute ID from census data

Question

I have two dataframes d1 and d2. My data frames are census data from 2010. I want to merge them using a common attribute (merge (d1, d2, by.x="GEOID", by.y= "GISJOIN").

d1 has the common id as GEOID (for eg. 310019654001) while d2 has the same id attribute as GISJOIN (for eg. 31000109654001). I need to remove the 3rd and 7th 0 from the GISJOIN attribute. Is it possible to do that in R? Also, any other way to make a common attribute?

Answer 1

Not pretty, but you can use substring to drop the characters nhgis inserted (note I am dropping the G that precedes the numbers in all nhgis products)

d2$GEOID<-paste0(substring(text = d2$GISJOIN, first=2,last=3),substring(text = d2$GISJOIN, first=5,last=7),substring(text = d2$GISJOIN, first=9))
d3<-merge(d1,d2,by="GEOID")