I have a dataframe that contains multiple hierarchy columns and a value attach to them, something like this:

Now I want to find the immediate parents of each row and add their value a a new column. My example just shows two parents but it could be more. So, the expected output would be as follows:

My Dataframe could be extremely large, so the speed and memory are important factors. So, row-wise process won't really work.

Logic:

The immediate parents for a row will be all rows that are one level higher than one of the hierarchies. For example for row 5, the potential immediate parents will be:

This is the code to create the dataframe if you want to play with it.

import pandas as pd

df = pd.DataFrame({'location hierarchy': ['US', 'US-California', 'US-California', 'US-California-LA', 'US-California-LA', 'US-California-LA', 'US-California-LA'],
                   'industry hierarchy': ['All', 'All', 'All-HT', 'All', 'All-HT', 'All-HT-SoftWare', 'All-Manufacturing'],
                   'value': ['V1', 'V2', 'V3', 'V4', 'V5', 'V6', 'V7']})

CodePudding user response：

Here you are:

df = pd.DataFrame({'Location Hierarchy': ['US', 'US-California', 'US-California', 'US-California-LA', 'US-California-LA', 'US-California-LA', 'US-California-LA'],
                   'Industry Hierarchy': ['All', 'All', 'All-HT', 'All', 'All-HT', 'All-HT-SoftWare', 'All-Manufacturing'],
                   'Value': ['V1', 'V2', 'V3', 'V4', 'V5', 'V6', 'V7']})

hierarchies = ["Location Hierarchy", "Industry Hierarchy"]
for i, hierarchy in enumerate(hierarchies): 
    df1 = df.copy()
    df1[hierarchy] = df1[hierarchy].str.split('-').str[:-1].str.join("-")
    df[f"Value_parent_{i 1}"] = df1.merge(df, on=hierarchies, how="left", suffixes=["", "_parent"])["Value_parent"]