0

From what I can tell, sf() merge doesn't support comparison operations by.x etc. Is there a feasible way to mesh two data sets, one of which is a simplefeature and the other a basic table from CSV or xlsx?

Example, to give a sense of what I mean. I could do something like this with a spatial dataframe generated by sp(): admin_lev1 <- merge(x=admin_lev1, y=admin_lev1_pop, by.x = "code", by.y = "CODE") and I would end up with a pleasantly aggregated dataframe. I've mangled around with sf() and it seems like this functionality - to specify by.x and by.y isn't in place and can't be reproduced.

Data for reproducible example:

admin_lev1_pop.csv:

council areas,2011_pop,code
Aberdeen City,"222,793",S12000033
Aberdeenshire,"252,973",S12000034
Angus,"115,978",S12000041
Argyll & Bute,"88,166",S12000035
Clackmannanshire,"51,442",S12000005
Dumfries & Galloway,"151,324",S12000006
Dundee City,"147,268",S12000042
East Ayrshire,"122,767",S12000008
East Dunbartonshire,"105,026",S12000045
East Lothian,"99,717",S12000010
East Renfrewshire,"90,574",S12000011
"Edinburgh, City of","476,626",S12000036
Eilean Siar,"27,684",S12000013
Falkirk,"155,990",S12000014
Fife,"365,198",S12000015
Glasgow City,"593,245",S12000046
Highland,"232,132",S12000017
Inverclyde,"81,485",S12000018
Midlothian,"83,187",S12000019
Moray,"93,295",S12000020
North Ayrshire,"138,146",S12000021
North Lanarkshire,"337,727",S12000044
Orkney Islands,"21,349",S12000023
Perth & Kinross,"146,652",S12000024
Renfrewshire,"174,908",S12000038
Scottish Borders,"113,870",S12000026
Shetland Islands,"23,167",S12000027
South Ayrshire,"112,799",S12000028
South Lanarkshire,"313,830",S12000029
Stirling,"90,247",S12000030
West Dunbartonshire,"90,720",S12000039
West Lothian,"175,118",S12000040
download.file("https://borders.ukdataservice.ac.uk/ukborders/easy_download/prebuilt/shape/Scotland_ca_2010.zip", 
              destfile = "data/Scotland_ca_2010.zip")
unzip("data/Scotland_ca_2010.zip", exdir = "data")

admin_lev1 <- st_read("data/scotland_ca_2010.shp") %>% st_transform(27700)

Ideally, what I need to do is join admin_lev1_pop data in the column 2011_pop to admin_lev1 but I'm at a loss as to how to accomplish this using the new feature set in sf().

2

2 Answers 2

1

To complete the answer

merge method for sf and data.frame object

https://r-spatial.github.io/sf/reference/merge.sf.html

library(sf)
library(dplyr)

download.file("https://borders.ukdataservice.ac.uk/ukborders/easy_download/prebuilt/shape/Scotland_ca_2010.zip", 
+               destfile = paste0(tempdir(),"/Scotland_ca_2010.zip"))
trying URL 'https://borders.ukdataservice.ac.uk/ukborders/easy_download/prebuilt/shape/Scotland_ca_2010.zip'
Content type 'application/zip;charset=UTF-8' length 9691195 bytes (9.2 MB)
downloaded 9.2 MB

unzip(paste0(tempdir(),"/Scotland_ca_2010.zip"), exdir = tempdir())

admin_lev1 <- st_read(paste0(tempdir(),"/scotland_ca_2010.shp")) %>% st_transform(27700)
Reading layer `scotland_ca_2010' from data source `C:\Users\Diego\AppData\Local\Temp\Rtmp8uXakh\scotland_ca_2010.shp' using driver `ESRI Shapefile'
Simple feature collection with 32 features and 4 fields
geometry type:  MULTIPOLYGON
dimension:      XY
bbox:           xmin: 5513 ymin: 530249 xmax: 470332 ymax: 1220302
proj4string:    +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +datum=OSGB36 +units=m +no_defs 

#Your csv
data <- read.csv("data.csv")
final <- merge(admin_lev1,data, by.x="code", by.y="code")
names(admin_lev1)
[1] "oldcode"  "name"     "code"     "label"    "geometry"
names(data)
[1] "council.areas" "X2011_pop"     "code"         
names(final)
[1] "code"          "oldcode"       "name"          "label"        
[5] "council.areas" "X2011_pop"     "geometry"

0

Fudge it: Calc a field in each dataset called 'KODE', with KODE="code" on one, and KODE="CODE" on the other, and then do the merge. Then, after the merge, just delete the 'code' and 'CODE' fields.

Also worked for me:

pe$STNCODE<-pe$STNCODE_12 #fix the name mismatch
rs<-merge(rs,pe, by.y="STNCODE") # merge the geodatapoints to table

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.