Basic function to convert Country name to ISO code using Pycountry

Question

I am working on a dataset which contains a column with common country names.

Task: To convert country name into standard ISO names

I have written a basic function which converts country names into country codes using pycountry library.

import pandas as pd
import pycountry
df = pd.DataFrame({"Country":["China","US","Iceland"])


def do_fuzzy_search(country):
    try:
        result = pycountry.countries.search_fuzzy(country)
        return result[0].alpha_3
    except:
        return np.nan

df["country_code"] = df["Country"].apply(lambda country: do_fuzzy_search(country))

But I am facing issues.Its taking too long. My dataset has 17003 rows,which is not even that large a number.

Firstly, suggestions on the code to improve its performance would be welcome.

Secondly, I would like to know if there is completely different way to do this faster.

Answer 1

I always get my code to be as clean as possible before starting work on performance. Here you have a bare except, which can hide errors. Change it to something better, for now I'll change it to Exception. You are also hiding a potential IndexError and AttributeError if the data ever changes shape as they are in the try.

def do_fuzzy_search(country):
    try:
        result = pycountry.countries.search_fuzzy(country)
    except Exception:
        return np.nan
    else:
        return result[0].alpha_3

Since there are roughly 200 sovereign states and you're working with 17003 rows you likely have a lot of the same values hitting a costly function. To resolve this issue you can use functools.lru_cache. Running in amortized \$O(n)\$ time and \$O(n)\$ space.

@functools.lru_cache(None)
def do_fuzzy_search(country):
    ...

Alternately you can sort the data by the provided countries name and then get each country once. Running in \$O(n\log n)\$ time and \$O(1)\$ space.

Answer 2

Apart from speeding up your code, there are some other improvements possible that I don't see mentioned yet.

The speedup

As other users have already picked up, you only need to transform around 200 countries instead of 17k rows. You can do this efficiently by storing the mapping in a dictionary:

iso_map = {country: do_fuzzy_search(country) for country in df["Country"].unique()}
df["country_code"] = df["Country"].map(iso_map)

This is in essence very similar to using the lru_cache proposed by Peilonrayz, but it's a bit more explicit.

---

Consistent naming

As a side note, I would advise you to use a consistent naming scheme in your DataFrame. This doesn't have to follow the python convention, but it should be easy to remember.

For example, turn all columns to lowercase by using something like:

df = df.rename(columns = {name: name.lower() for name in df.columns}

---

Using categoricals

Since the number of countries (and isocodes) is limited, you might want to consider using categorical columns. These have the advantage of requiring less memory and being faster on some operations.

It might not be worth the extra lines of code in your situation, but it's something useful to keep in the back of your mind.

Answer 3

Here is a way to do this differently. You will have to test it to see if it is actually faster (since I don't have an example of 17k different almost matching ways to write countries).

First note that you can extract the underlying data used by pycountry. On my machine the file containing the data on all existing countries can be found at /usr/local/lib/python3.6/dist-packages/pycountry/databases/iso3166-1.json. Depending on your OS this will be in a different place, but the actual filename should be the same.

Next, there exists a tool that can directly merge dataframes on certain columns, using fuzzy search, called fuzzy_pandas. With it you can do something like this:

import json
import pandas as pd
import fuzzy_pandas as fpd

with open("/tmp/iso3166-1.json") as f:
    countries = pd.DataFrame.from_dict(json.load(f)["3166-1"])
countries = countries.drop(columns=["alpha_2", "numeric"])
countries = countries.fillna(method="ffill", axis=1)

data = pd.DataFrame({"country": ["Samos", "Germanz"]})


fpd.fuzzy_merge(data, countries, left_on=["country"], right_on=["name"], method="levenshtein")
#    country alpha_3     name                official_name                  common_name
# 0    Samos     WSM    Samoa   Independent State of Samoa   Independent State of Samoa
# 1  Germanz     DEU  Germany  Federal Republic of Germany  Federal Republic of Germany

You might have to try which column to use for right_on. You might have to use all of them in separate calls to pfd.fuzzy_merge and then decide what to do with the ones that give different results. In the implementation of pycountry the name and official name are used and given a certain weighting factor:

    # Prio 3: partial matches on country names
    for candidate in countries:
        # Higher priority for a match on the common name
        for v in [candidate._fields.get('name'),
                  candidate._fields.get('official_name')]:
            if v is None:
                continue
            v = remove_accents(v.lower())
            if query in v:
                # This prefers countries with a match early in their name
                # and also balances against countries with a number of
                # partial matches and their name containing 'new' in the
                # middle
                add_result(candidate, max([5, 30-(2*v.find(query))]))
                break

Answer 4

The question I have is why do you have 17K rows when there are only 200+ countries. Are the country names translated in many languages perhaps ? Isn't it conceivable to use a simplified dataset ? What does the current dataset look like ?

When you're saying "Its taking too long", do you mean the function is too slow (how long does it take on average ?), or it is because you have a lot of data to process ? If some of the data is repetitive, perhaps you could regroup the identical records so as to call the function only once instead of repeating the process for each row. Basically, reorder the data a bit before processing by your script.

Maybe the task could be performed equally fine in SQL, for example from a SQLite DB with an appropriate LIKE or a MATCH clause (or even soundex - not available by default, requires recompilation), possibly using FTS or (partial) indexes.