#Go up a hierarchical tree with pandas

# Import libraries
import pandas as pd
import numpy as np

# Import Google Ads location data
df = (
    pd.read_csv('https://developers.google.com/adwords/api/docs/appendix/geo/geotargets-2021-02-24.csv')
    .rename(columns=lambda x: x.lower().replace(' ', '_'))
    .loc[lambda x: x['country_code'] == 'FR']
    .reset_index(drop=True)
    .assign(parent_id=lambda x: x['parent_id'].fillna(0).astype('int'))
    [['criteria_id', 'canonical_name', 'parent_id']]
)
df

# Recursive algorithm: list the next parent if any, otherwise exit
def recursive(row, parents):
    if row['parent_id'] == 0:
        return parents
    else:
        parents.append(row['parent_id'])
        return recursive(df.loc[df['criteria_id'] == row['parent_id'], :].squeeze(), parents)

# Call the recursive function for each row
def get_parents(row):
    parents = []
    return recursive(row, parents)

# Apply the function and create a column with the parents list for each location
df['parents_list'] = df.apply(get_parents, axis=1)

# Display resulting DataFrame
df.sort_values('parents_list')