Fastest method of filling missing values from lookup table

Viewed 99

I have a table containing [Suburb, Postcode, State, Country] for just about every location in the world - around 1.5 million rows. I'm using it to fill in missing values from data about an individual's location.

For example, an input of

Suburb = Monte-Carlo
Postcode = None
State = None
Country = None

would produce an output of

Suburb = Monte-Carlo
Postcode = 98000
State = Monaco
Country = MC 

The function I'm using at the moment queries the dataframe of locations to match the known values, and if a unique value is found in one of the missing fields then it is used.

def locFill(series):

cols = ['Suburb', 'Postcode', 'State', 'Country']
series.index = cols

colsGot = [col for col in cols if list(series.notnull())[cols.index(col)] == True]
colsNot = [col for col in cols if list(series.notnull())[cols.index(col)] == False]

if len(colsGot) > 0:
    query = ''
    for col in colsGot:
        query = query + col + ' == "' + series[col] + '" and '
    query = query.strip(' and ')

    df = search_df.query(query)

    output = series.copy()
    for col in colsNot:
        if df[col].nunique() == 1:
            output[col] = df[col].iloc[0]
    
    return(output)
else:
    return(pd.Series([np.nan, np.nan, np.nan, np.nan], index = cols))

Does there exist a faster way to find these matching values? Using df.query improved greatly, but is still not fast enough.

0 Answers
Related