Ggtcf

I've been looking for an answer to this problem for a few days, but can't find anything similar in other threads.

I have a lookup table to define classification for some input data. The classification depends on continent, country and city. However, some classes may depend on a subset of these variables, e.g. only continent and country (no city). An example of such lookup table is below. In my example, I'm using one and two stars as wildcards:
- One Star: I want all cities in France to be classified as France, and
- Two Stars: All cities in US, excepting New York and San Francisco as USA - Other.

lookup_df = pd.DataFrame('Continent': ['Europe', 'Europe', 'Asia', 'America', 'America', 'America', 'America', 'Africa'], 
 'Country': ['France', 'Italy', 'Japan', 'USA', 'USA', 'USA', 'Argentina', '*'],
 'City': ['*', '*', '*', 'New York', 'San Francisco', '**', '*', '*'],
 'Classification': ['France', 'Italy', 'Japan', 'USA - NY', 'USA - SF', 'USA - Other', 'Argentina', 'Africa'])

If my dataframe is

df = pd.DataFrame('Continent': ['Europe', 'Europe', 'Asia', 'America ', 'America', 'America', 'Africa'], 
 'Country': ['France', 'Italy', 'Japan', 'USA', 'USA', 'USA', 'Egypt'], 
 'City': ['Paris', 'Rome', 'Tokyo', 'San Francisco', 'Houston', 'DC', 'Cairo'])

I am trying to get the following result:

 Continent Country City Classification
0 Europe France Paris France
1 Europe Italy Rome Italy
2 Asia Japan Tokyo Japan
3 America USA San Francisco USA - SF
4 America USA Houston USA - Other
5 America USA DC USA - Other
6 Africa Egypt Cairo Africa

I need to start from a lookup table or similar because it's easier to maintain, easier to explain and it's also used by other processes. I can't create a full table, because I would have to consider all possible cities in the world.

Is there any pythonic way of doing this? I thought I could use pd.merge, but I haven't found any examples of this online.

One easy-to-maintain way to handle your task is to use maps:

df2 = df.copy()

# below will yield a field df2.Classification and save the value when all "Continent", "Country" and "City" match, otherwise np.nan
df2 = df2.merge(lookup_df, how='left', on = ["Continent", "Country", "City"])

# create map1 from lookup_df when City is '*' but Country is not '*'
map1 = lookup_df.loc[lookup_df.City.str.match('^*+$') & ~lookup_df.Country.str.match('^*+$')].set_index(['Continent','Country']).Classification.to_dict()
map1
#('Europe', 'France'): 'France',
# ('Europe', 'Italy'): 'Italy',
# ('Asia', 'Japan'): 'Japan',
# ('America', 'USA'): 'USA - Other',
# ('America', 'Argentina'): 'Argentina'

# create map2 from lookup_df when both City and Country are '*'
map2 = lookup_df.loc[lookup_df.City.str.match('^*+$') & lookup_df.Country.str.match('^*+$')].set_index('Continent').Classification.to_dict()
map2
#'Africa': 'Africa'

# create a function to define your logic:
def set_classification(x):
 return x.Classification if x.Classification is not np.nan else 
 map1[(x.Continent, x.Country)] if (x.Continent, x.Country) in map1 else 
 map2[x.Continent] if x.Continent in map2 else 
 np.nan

# apply the above function to each row of the df2
df2["Classification"] = df2.apply(set_classification, axis = 1)

Note: your original df.Continent on the 4th row contains an extra trailing space 'America ' which will fail the above df2 = df2.merge(...) line. you will need to fix this data issue though.