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.