3

I am trying to merge a table with a shapefile using R. The shapefile's database is bigger than the table and I need to have null or zero values where the two data sets don't match.

Table data

no.   table_id   value
 1.        a11     123
 2.        a12     456
 3.        a14     789

Shapefile's data db

no.   polygon_id     
 1.          a11      
 2.          a12      
 3.          a13
 4.          a14
 5.          a15

Desired result (on the shapefile):

no.    id   value
 1.   a11     123
 2.   a12     456
 3.   a13       0
 4.   a14     789
 5.   a15       0

What I tried

I tried merge but it didn't work I think because the two tables have different sizes.

#trying to merge
joined <- merge(table,polygons, by.x="table_id", by.y="polygons_id",all=T)
## overwrite the file with this new copy
library(foreign)
write.dbf(joined, "Polygons_R.dbf")

Ward_polygons <- readShapePoly(fn="Polygons_R")

Receiving this error

Error in `row.names<-.data.frame`(`*tmp*`, value = value) : 
  invalid 'row.names' length

I tried also 

polygons$polygons_id<-(table)

Getting this error

Error in `[[<-.data.frame`(`*tmp*`, name, value = c(1, 2, 1, 2, 9, 8,  : 
  replacement has 1309 rows, data has 2490

I read a previous post (here), as well, but I couldn't find my answer

nmtoken
  • 13,355
  • 5
  • 38
  • 87
George Christou
  • 35
  • 1
  • 7

1 Answers1

6

You want to do a left outer join on the shapefile's data.frame (data db) with the new table (table data). That will keep all the existing rows of your 'data db', join the appropriate fields together, and fill in missing data for rows that don't match. I'm assuming you're reading the shapefile as an sp object (using rgdal package in my example). I also changed the name of table (also a common function name) to myTable and polygons to myShp:

library('rgdal')
myShp <- readOGR(dsn, layer)
myShp@data <- merge(x=myShp@data, y=myTable, 
    by.x="table_id", by.y="polygons_id", all.x=TRUE)

At this point, the new columns from myTable will be appended as columns to the myShp@data data.frame and any rows thta could not be joined will be filled with NA values. You can go through and replace the NA values as needed, e.g.:

myShp@data$value[which(is.na(myShp@data$value))] <- 0
aaryno
  • 836
  • 5
  • 13