Pandas, append data based on hierarchy values in columns

user_12864900 0
Viewed 93

Trying to find a suitable pandas code to do this in optimized way (need to deal with millions of rows)

The input dataframe look like

    levels value
0      5.1     X
1    5.1.1     Y
2    5.1.1.1   Y1
3    5.1.2     Z
4      5.2     A
5    5.2.2     B
6  5.2.2.1     C
7  5.2.2.2     D

and the output dataframe should be like

    levels value  newValue
0      5.1     X  X,Y,Y1,Z
1    5.1.1     Y      Y,Y1
2  5.1.1.1    Y1        Y1
3    5.1.2     Z         Z
4      5.2     A   A,B,C,D
5    5.2.2     B     B,C,D
6  5.2.2.1     C         C
7  5.2.2.2     D         D

Rules for appending values is, when you see a level, append its value with all sub levels matching '^{level}'. For example, if it is level 5.2.2, then append all rows matching r'^5.2.2*'. If the level is 5.2. then append all rows matching r'^5.2*' Any advise?

pandas-groupby pandas lambda python
2 Answers

This does the accumulation of the levels. Should work with extended levels, too

df.reset_index(inplace=True)
ml = df.levels.str.len().max()

l = []
for i in range(3, ml+1, 2):
    df1 = df.groupby(df.levels.str[:i])['value'].apply(list)
    l.append(pd.merge(df[['index','levels']], df1, on='levels'))
    df = df[~df.levels.isin(df1.index)]
pd.concat(l).sort_values('index').set_index('index')

Output

        levels          value
index                        
0          5.1  [X, Y, Y1, Z]
1        5.1.1        [Y, Y1]
2      5.1.1.1           [Y1]
3        5.1.2            [Z]
4          5.2   [A, B, C, D]
5        5.2.2      [B, C, D]
6      5.2.2.1            [C]
7      5.2.2.2            [D]
user_142777220

You could do this one line with list comprehension. Essentially, you can return all values into a list, where the level is a substring of another row's level per row.

df['value_list'] = df.apply(lambda x: [z for y,z in zip(df['levels'], df['value']) if x['levels'] in y], axis=1)
df
Out[1]: 
    levels value     value_list
0      5.1     X  [X, Y, Y1, Z]
1    5.1.1     Y        [Y, Y1]
2  5.1.1.1    Y1           [Y1]
3    5.1.2     Z            [Z]
4      5.2     A   [A, B, C, D]
5    5.2.2     B      [B, C, D]
6  5.2.2.1     C            [C]
7  5.2.2.2     D            [D]
user_63667700
Related