8

I'm having an annoying problem that I'm trying to find an automated solution for. The shorthand version is that I have a shapefile and a table of created data for regions within countries. The created data table does NOT have any sort of standardized GIDs/admin codes to match to shapefiles, and the region names are not exact matches either. Let's take a closer look; here's my dummy data frame + shapefile.

library(rgdal)

#load in shapefile
arm <- readOGR("D:/Country-Shapefiles/ARM_adm_shp", layer = "ARM_adm1")

#create dummy data frame
id <- c(100:110)
name <- c("Aragatsotn", "Ararat", "Armavir", "Gaghark'unik'", "Kotayk", "Lorri", 
          "Shirak", "Syunik'", "Tavush", "Vayots' Dzor", "Yerevan City")
value <- runif(11, 0.0, 1.0)
df <- data.frame(id, name, value)

So what I have is a table with seemingly random IDs, region names, and a value to be plotted with a choropleth map. Looks like this:

> df
    id          name     value
1  100    Aragatsotn 0.6923852
2  101        Ararat 0.5762024
3  102       Armavir 0.4688358
4  103 Gaghark'unik' 0.4702253
5  104        Kotayk 0.9347992
6  105         Lorri 0.1937813
7  106        Shirak 0.5162604
8  107       Syunik' 0.4332389
9  108        Tavush 0.9889513
10 109  Vayots' Dzor 0.2182024
11 110  Yerevan City 0.5791886

Looking at the shapefile attributes of interest, we've got this:

> arm@data[c("ID_1", "NAME_1")]

       ID_1      NAME_1
    0     1  Aragatsotn
    1     2      Ararat
    2     3     Armavir
    3     4      Erevan
    4     5 Gegharkunik
    5     6      Kotayk
    6     7        Lori
    7     8      Shirak
    8     9      Syunik
    9    10      Tavush
    10   11 Vayots Dzor

Ideally, df would include some sort of matching admin IDs to join to the shapefile. Whoever created the data that I'm using did not follow these conventions, unfortunately. Alternatively, it'd be great to match the region names themselves...but as you can see, there are slight variations in each name.

Matching by hand is always a backup solution, but who wants to take the time to do that? ;) But really, even barring laziness, the project that I'm working on will be mapping dozens and dozens of different countries, so I'm looking for an automated solution that can do everything without having to do anything by hand. Is this possible? Can I somehow match these -almost- region names to the shapefiles?

Sidenote: I'm looking into grepl for partial string matches per this post, but I'm not sure if this is a potential solution because I'll need to draw from the column names rather than inputting each region name by hand.

EDIT: When I match the IDs by hand, what I've done is create a new column in my data frame and adding the exact matching terms from the shapefile. Unfortunately, because of the peculiarities of the data, the order of the names don't match up either, so this still requires some manual input. I'm hoping for some sort of completely automated solution (if it's even possible).

  • If you are lucky and have the same number of records in the same order in both the shapefile and table, you could copy and paste the names into adjoining columns in a new table, join that to the shapefile using its names, and join that to the table using its names. (Or using a copy of your shapefile paste the table names directly into its dbf in a pre-2007 Excel or Libre/Open Office sheet.) If you don't have an exact number of one to one records but many long "stretches" of them you can mix a bit of manual work with copy and pastes. – johns Jan 13 '16 at 17:57
  • This is what I ended up doing manually by hand, but unfortunately they're not in the correct order. Even if listed alphabetically, it still might not work all the time (in this example, Erevan = Yerevan City, which throws the rest of the list out of order). – Lauren Jan 13 '16 at 18:00
6

I would go for stringdist package which has implemented many algorithms to calculate the partial similarity (distance) of strings including Jaro-winkler. Here is a fast solution for you:

  #df to be joined
  id <- c(100:111)
  name <- c("Aragatsotn", "Ararat", "Armavir", "Gaghark'unik'", "Kotayk", "Lorri", 
            "Shirak", "Syunik'", "Tavush", "Vayots' Dzor", "Yerevan City","Aragatsotn")
  value <- runif(12, 0.0, 1.0)
  df <- data.frame(id, name, value)

  #create shape data df
  shpNames <- c("Aragatsotn",
               "Ararat",
               "Armavir",
               "Erevan",
               "Gegharkunik",
               "Kotayk",
               "Lori",
               "Shirak",
               "Syunik",
               "Tavush",
               "VayotsDzor")
  arm.data  <- data.frame(ID_1=1:11,NAME_1=shpNames)

  #simple match (only testing)
  match(df$name,arm.data$NAME_1)
  #simple merge (testing)
  merge(arm.data,df,by.x="NAME_1",by.y="name",all.x=TRUE)

  #partial match using stringdist package
  library("stringdist")
  am<-amatch(arm.data$NAME_1,df$name,maxDist = 3)
  b<-data.frame()
  for (i in 1:dim(arm.data)[1]) {
      b<-rbind(b,data.frame(arm.data[i,],df[am[i],]))
  }
  b

it outputs:

ID_1      NAME_1  id          name     value
1     1  Aragatsotn 100    Aragatsotn 0.8510984
2     2      Ararat 101        Ararat 0.3004329
3     3     Armavir 102       Armavir 0.9258740
4     4      Erevan  NA          <NA>        NA
5     5 Gegharkunik 103 Gaghark'unik' 0.9935353
6     6      Kotayk 104        Kotayk 0.6025050
7     7        Lori 105         Lorri 0.9577662
8     8      Shirak 106        Shirak 0.6346550
9     9      Syunik 107       Syunik' 0.6531175
10   10      Tavush 108        Tavush 0.9726032
11   11  VayotsDzor 109  Vayots' Dzor 0.3457315

You can play with maxDist parameter of amatch method. Although 3 works best with your sample data!

  • Yes, this worked for my example! Now to test a few more! Related question: how can I achieve this same join while keeping the shapefile spatial? It would appear that this bit of code has just created a dataframe with the joined data, but I will still need to be able to map it. – Lauren Jan 14 '16 at 18:28
  • I have created the data frame manually so your problem can be reproducible. When you read a shapefile via readOGR, the output class would be one of "sp" derivatives class such as "SpatialPointsDataFrame". And they all have a "data" attribute which contains all the attribute data which is of type dataframe. In my example I am joining to the dataframe and the geometrical information are untouched. So for your example, simply change arm.data to arm@data and it would work just fine. – Farid Cheraghi Jan 14 '16 at 21:43
  • Do not use arm@data, that would create a big mess (records not matching their correct geometries) – Robert Hijmans Jan 17 '16 at 20:46
6

I want to add some details to Farid Cher's answer as this is a very common problem. Using amatch can do wonders, but with these Spatial objects you should not use base::merge and not access the @data slot. That would inevitably leads to a terrible mess (base::merge changes the order of records, and they would no longer match geometries).

Instead, use the sp::merge method, by using the SpatialPolygonsDataFrame as first argument in merge. Also note the potential problem of having duplicated records. And I added data so that the example is self-contained and reproducible.

library(raster)
#example data.frame
name <- c("Aragatsotn", "Ararat", "Armavir", "Gaghark'unik'", "Kotayk", "Lorri", "Shirak", "Syunik'", "Tavush", "Vayots' Dzor", "Yerevan City","Aragatsotn")
value <- runif(12, 0.0, 1.0)
df <- data.frame(name, value)

# example SpatialPolygonsDataFrame
arm <- getData('GADM', country='ARM', level=1)[, c('NAME_1')]

This

merge(arm, df, by.x='NAME_1', by.y='name')

fails with message

#Error in .local(x, y, ...) : non-unique matches detected

Because there are two records for "Aragatsotn" in df. You could do

merge(arm, df, by.x='NAME_1', by.y='name', duplicateGeoms=TRUE)

But normally the sane approach is to use someting like

df <- aggregate(df[, 'value', drop=FALSE], df[, 'name', drop=FALSE], mean)
m <- merge(arm, df, by.x='NAME_1', by.y='name')
data.frame(m)

data.frame(m)
#        NAME_1       value
#1   Aragatsotn 0.421576186
#2       Ararat 0.003138734
#3      Armavir 0.703402672
#4       Erevan          NA
#5  Gegharkunik          NA
#6       Kotayk 0.926883799
#7         Lori          NA
#8       Shirak 0.430585540
#9       Syunik          NA
#10      Tavush 0.121784395
#11 Vayots Dzor          NA

Now, merge does not work well in this case because the names do not match. So you can use

i <- amatch(df$name, arm$NAME_1, maxDist = 3)
df$match[!is.na(i)] <- arm$NAME_1[i[!is.na(i)]]
df
#            name       value       match
#1     Aragatsotn 0.421576186  Aragatsotn
#2         Ararat 0.003138734      Ararat
#3        Armavir 0.703402672     Armavir
#4  Gaghark'unik' 0.682169824 Gegharkunik
#5         Kotayk 0.926883799      Kotayk
#6          Lorri 0.128894086        Lori
#7         Shirak 0.430585540      Shirak
#8        Syunik' 0.163562936      Syunik
#9         Tavush 0.121784395      Tavush
#10  Vayots' Dzor 0.383439033 Vayots Dzor
#11  Yerevan City 0.168033419        <NA>

Almost there, but "Yerevan City" did not match with "Erevan". In this case you can increase maxDist

i <- amatch(df$name, arm$NAME_1, maxDist = 10)
df$match[!is.na(i)] <- arm$NAME_1[i[!is.na(i)]]

But increasing maxDist will not always work or give the wrong matches becuase variant names can be very distinct. So in many cases you will end up doing some manual replacements like:

df[df$name=="Yerevan City", 'match'] <- "Erevan"

In both cases followed by

m <- merge(arm, df, by.x='NAME_1', by.y='match')

In any case you will want to check if sum(table(i) > 1) == 0; although merge should fail anyway if there are duplicate matches.

  • Nice details! This is why I called my answer fast. However the matched dataframe (df) wouldn't contain the geometry data. would it? The OP wants to map the joined df. Spatial aggregate instead of attribute aggregate would be another alternative for multiple join cases. – Farid Cheraghi Jan 17 '16 at 23:22
  • df has no geometries, hence the final step with merge. Spatial aggregate is useful for different cases (if, in this example, NAME_1 had duplicates.) – Robert Hijmans Jan 18 '16 at 2:14

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.