Transposing and Creating Flat files Using Pandas

I have the following table

CODE    LEV NAME
A00     3   text
A000    4   text
A001    4   text
A02     3   text
A022    4   text
A0220   5   text
A33     3   text

For each top level 3, there are sub levels 4,5,6, etc. For some level 3, there are no sub-level. I need to transpose levels and create a meaningful flat file like this using pandas.

L3    L4    L5    NAME
A00   -     -     text
A00   A000  -     text
A00   A001  -     text
A02   -     -     text
A02   A022  -     text
A02   A022  A0220 text
A33   -     -     text

Answer

I’ll probably be lynched for this but since there isn’t any better answer – hope that helps :-)

>>> df
    CODE LEV  NAME
1    A00   3  text
2   A000   4  text
3   A001   4  text
4    A02   3  text
5   A022   4  text
6  A0220   5  text
7    A33   3  text

>>> (df
     ...: .groupby(['LEV','NAME'])
     ...: .agg(list)
     ...: .reset_index()
     ...: .explode('CODE')
     ...: .reset_index()
     ...: .pivot_table(index=['index', 'NAME'],columns='LEV',values='CODE', aggfunc=list)
     ...: .reset_index()
     ...: .explode('3')
     ...: .explode('4')
     ...: .explode('5')
     ...: .drop_duplicates()
     ...: .reset_index()
     ...: .drop(['level_0','index'], axis=1)
     ...: .rename({'3': 'L3', '4': 'L4', '5': 'L5'}, axis=1)
     ...: .rename_axis(None, axis=1)
     ...: .fillna('-'))[['L3','L4','L5','NAME']]

    L3    L4     L5  NAME
0  A00     -      -  text
1  A02     -      -  text
2  A33     -      -  text
3    -  A000      -  text
4    -  A001      -  text
5    -  A022      -  text
6    -     -  A0220  text