1

I am trying to merge non-spatial data (data frame in R) and spatial data (SpatialPolygonsDataFrame in R) and eventually create the merged file in SpatialPolygonsDataFrame form.

In specific, I downloaded 'zip areas boundary file' from census web site (https://www.census.gov/geo/maps-data/data/cbf/cbf_zcta.html) and read it as 'SpatialPolygonsDataFrame' file using 'readShapePoly' function in R.

And then I merged the SpatialPolygonsDataFrame with my data in 'data frame' form, but the merged file is 'data frame' form not 'SpatialPolygonsDataFrame'.

Can someone let me know how to create 'SpatialPolygonsDataFrame' after merging spatial data and non-spatial data? I have been spending my entire day just on this but didn't have clues.

The code that I used is as follows:

nation <- readShapePoly("C:/Users/Research/data/nation.shp") 
us_urban_zipcode <- as.data.frame(us_urban_index13[c(39,91)])
us_urban_index_nation <-merge(us_urban_zipcode, nation, by = "ZCTA5CE10", 
all.x=TRUE)

Nation is SpatialPolygonsDataFrame and us_urban_zipcode is dataframe, and merging them results in dataframe, not SpatialPolygonsDataFrame, which I need for further analysis.

My data non-spatial data looks like this:

---------------
zipcode | row
 10003  |  1
 10002  |  2
 10003  |  3
 10004  |  4
 10002  |  5
  ...   | ..
-----------------

And my spatial data looks like this:

----------------------
zipcode | AFFGEOID10
 10001  |  477175
 10002  |  2118827
 10003  |  78588518
 10004  |  9804480
 10005  |  4845242
  ...   |    ..
-----------------------

So, basically my non-spatial data is bigger than spatial data in terms of observations. The zipcode of spatial data is all unique (only one zipcode in each observation), but there are redundant zipcodes for non-spatial data. But I need to keep all observation in non-spatial data for further analysis. This is why I used 'all.x =T' or 'all.y = T' in the merge function.

  • Maybe could be with tidyverse joining data.frame from sp object with the other one – aldo_tapia Oct 26 '17 at 20:42
  • 1
    You need to provide your code. It is virtually impossible to guess at what you have done. This is a very straight forward process so, it is something that you are missing in your code. – Jeffrey Evans Oct 26 '17 at 20:49
  • 1
    You need to explain how you "merged" the data - did you use the "merge" function? With what parameters? What are the columns in your spatial and non-spatial data? How do they match up? – Spacedman Oct 26 '17 at 21:58
  • @JeffreyEvans, Sorry, Jeffrey, I provided the code that I used. Looking forward to your reply. – Emily Oct 27 '17 at 13:10
  • @Spacedman, I used 'merge' function. Merging itself was not a problem. I have matching ID for both data files and could merge them. I just want the result to be SpatialPolygonsDataFrame, not normal data frame. – Emily Oct 27 '17 at 13:12
1

I would recommend reading your shapefile in with rgdal::readOGR. If you run into performance issues you should look up how to read in spatial data and merge data using the sf library and the simple features workflow.

For this to work I like to have column names that are to be merged to be identical before performing my merge. You can also specify column names using the by.x and by.y arguments in the merge function.

library(rgdal)
mydf   <- read.csv("myCsv.csv")
myspdf <- readOGR("myShapefile.shp")

## then merge using sp's merge function
mynewspdf <- merge(myspdf, mydf)

You may get a "non-unique matches detected" error, in which case you can try..

mynewspdf <- merge(myspdf, mydf, duplicateGeoms = T)

See for more info -> https://www.rdocumentation.org/packages/sp/versions/1.2-5/topics/merge

  • thanks for the reply. As you said, I got the "non-unique matches detected" error and used duplicatedGeons = T. I didn't get any error but the data is bigger than I expected. The code that I used is us_urban_index_nation2 <- sp::merge(nation, us_urban_zipcode, by = "ZCTA5CE10", all.y=T, duplicateGeoms = TRUE) because I need to right join of the data. (The no of obs of non-spatial data is 80,000, but the code resulted in 110,000 obs approximately. It seems that the right-join command doesn't work well.) – Emily Oct 27 '17 at 14:51
  • I think the tool works as expected. I bet you have something going on with your data that causes more duplication than expected. I have a lot of experience using that tool and outer joins in general and I can tell you that it is probably your data. Also, I think you have a logical error in your approach. You want a to merge a sp object with a data frame but keep all the records in your data frame even if they don't match your sp object, then return an sp object. This is not how an sp object works. An sp object needs to have a geometry for every record. Your query logic does not allow for that. – JMT2080AD Oct 27 '17 at 21:02
  • How many records do you get if you remove all.y = T from your query? – JMT2080AD Oct 27 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.