I have the following (ordered) dataframe df:
Level ID
5 A
8 DD
8 DA
8 AC
5 B
8 BA
8 BB
8 BC
8 BD
The logic is that the Level 5 , which stands above the level 8 has to get mapped to it, that means:
Level ID Upper_ID
5 A A
8 DD A
8 DA A
8 AC A
5 B B
8 BA B
8 BB B
8 BC B
8 BD B
How can I achieve this? I want to map the "upper group ID" to it. So everytime a new 5 in the Level column occurs, that menas this is the new upper group for the Level 8 until the next 5 occurs.
>Solution :
If you want to propagate the value when there is a change of Level to a lower level, identify the position of the change with diff+lt, then select the top values with where and ffill:
df['Upper_ID'] = df['ID'].where(df['Level'].diff(-1).lt(0)).ffill()
Or, just matching a 5:
df['Upper_ID'] = df['ID'].where(df['Level'].eq(5)).ffill()
Output:
Level ID Upper_ID
0 5 A A
1 8 AA A
2 8 AB A
3 8 AC A
4 5 B B
5 8 BA B
6 8 BB B
7 8 BC B
8 8 BD B
Intermediates (first approach):
Level ID Upper_ID diff lt(0) where ffill
0 5 A A -3.0 True A A
1 8 AA A 0.0 False NaN A
2 8 AB A 0.0 False NaN A
3 8 AC A 3.0 False NaN A
4 5 B B -3.0 True B B
5 8 BA B 0.0 False NaN B
6 8 BB B 0.0 False NaN B
7 8 BC B 0.0 False NaN B
8 8 BD B NaN False NaN B