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):
        result = pycountry.countries.search_fuzzy(country)
        return result[0].alpha_3
        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.

  • \$\begingroup\$ It would help if you could add some more context. The speed of this will (partially) depend on how you are calling this code and what large means in your case. \$\endgroup\$
    – Graipher
    Mar 11 '20 at 15:41
  • 1
    \$\begingroup\$ I have edited the question as per your suggestion.If there is any thing more I can do to improve my question I would welcome the it. \$\endgroup\$ Mar 11 '20 at 15:56
  • \$\begingroup\$ Aren't your firstly and secondly the same question? \$\endgroup\$
    – Peilonrayz
    Mar 11 '20 at 16:11
  • \$\begingroup\$ Actually,i my second question is if I can do this completely differently using some other way by ignoring this code if need be.Any reference material in that case for that would be welcome. \$\endgroup\$ Mar 11 '20 at 16:18

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):
        result = pycountry.countries.search_fuzzy(country)
    except Exception:
        return np.nan
        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.

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.

  • \$\begingroup\$ Both the codes look so similar.I would like to learn how your code improves upon my defects of try except. \$\endgroup\$ Mar 11 '20 at 16:39
  • 3
    \$\begingroup\$ @AkhilSharma The first change is to only catch Exceptions. By default, except catches BaseException, which also includes GeneratorExit, SystemExit, and KeyboardInterrupt. The second change is to use try...else so that any exception raised in result[0].alpha_3 is not caught with except. \$\endgroup\$ Mar 12 '20 at 10:51

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.


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'),
            if v is None:
            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))]))

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.

  • \$\begingroup\$ Actually,each of the row is a Covid patient belonging to some certain country. \$\endgroup\$ Mar 11 '20 at 17:30

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.