Can I join a CSV field to one of any matching values in a single cell within a shapefile?

Question

I'm working with the Global Administrative Boundaries shapefile, and am have a CSV of values I'd like to join to it.

My issue, however, is that there are multiple ways to spell the region names. Look at the Catalonia example below: there are 6 different ways to spell the region name, depending on the language.

My question is this: If the join field value is "Catalonia" in my CSV, how would I go about joining my data to the shapefile on any of the values in the VARNAME_1 column? Or should I split VARNAME_1 into multiple columns, and then somehow join on "one of any, if any" values?

Note 1: There isn't a consistent number of languages in the VARNAME_1 column. Note 2: Using QGIS 2.18.2, MacOS

Answer 1

Use the DB Manager and an SQLite JOIN:

• Load both layers in QGIS

• Open QGIS | Database | DB Manager...

  • double-click Virtual Layers | Project layers
  • open the SQL Window (2nd symbol from left)

  • run (Execute)

    SELECT a.*,
           b.*
    FROM <shapefile_name> AS a
    JOIN <csv_layer_name> AS b
      ON instr(a.str, b.str) > 0;
    

    where instr(string, substring) checks string for substring and returns true the position of the first matched character if there's a pattern match (as in regex pattern matching, with all implied caution; substring = 'York' will match string = 'NewYork'. The join is one-to-one excluding, meaning that there will only be as many rows as there are matches; use LEFT JOIN if there possibly are more entries in the shapefile_name layer that you want to keep (and also switch the layers in the join if it's the other way around).

  • tick Load as new layer, choose a name and hit Load

  • <new_layer> context menu | Save as.. and save to file

Answer 2

While not everyone will have the same workflow, I think it is good practice to split all possible values into different fields. For example, in Canada, some federally generated data has attribute tables where toponyms appear for both official languages. They are split into two fields, which makes searching for entities much easier.

I would advise to split the fields. Joining will be much easier, and searching the data will also be simplified. If your csv always uses the same language, you might want to extract only the matching value in your field. For this you can use the method described here and repeat it until your string is split in the required fields.

I took a look at your data and the number of entities is relatively low, which makes it realistic to fix them. To split the values into new fields, go to the field calculator and use those two expressions (replacing "Test" with your own field).

The first one isolates the first substring before the '|' character while the second one will isolate the rest of the string after the first '|'. Just repeat the second one on every new split field until you have no vertical slashes left.

You'll probably have to manually copy the values you want in a new field because the languages aren't all the same but it'll work for this small entity number.

Answer 3

This might be a simple answer, but couldn't you create a new field named "Join" and then do a simple "if else" statement. I'm not the best at python but I'm sure you could do something along the lines of "if [Name_1] == Cataluña", "then [join] == Catalonia" "elif [Name_1] == Catalogna", then [join] == Catalonia", with a statement for each different spelling and the last expression being an 'else' instead of 'elif'. Sorry I can't help with the specific syntax, but it will look something like what is below. Good luck!

if expression1:
   then statement 
elif expression 2:
   then statement 
elif expression 3:
   then statement 
else expression 4:
   then statement