CODEWITHSUNDEEP
pythonpandasdataframemulti-index
Pedro de Sá
Pedro de Sá

Reputation: 780

Add new index to multiindex as a count of first level

I would like to add a new multiindex in between the already existing indexes 'Warnings' and 'equip' with the sum of the column 'count per equip' for each 'Warnings' level.

idx = pd.MultiIndex.from_product([['warning1', 'warning2', 'warning3'],
                                  ['ff0001', 'ff0002', 'ff0003']],
                                 names=['Warnings', 'equip'])
col = ['count per equip']

df = pd.DataFrame([100,2,1,44,45,20,25,98,0], idx, col)
df

So the resulting dataframe would have the same number of index in level 0, 'Warnings', and for this example it would be [103, 109, 123], respectively.

I've managed to sum and insert the index at the right place, but when trying to do all together, all values are NaN's:

df = df.assign(total=df.groupby(level=[0]).size()).set_index('total', append=True).reorder_levels(['Warnings','total','equip'])

Upvotes: 0

Views: 113

Answers (1)

ragas
ragas

Reputation: 916

In assign we can't do groupby. So, the following code create similar data.

idx = pd.MultiIndex.from_product([['warning1', 'warning2', 'warning3'],
                                  ['ff0001', 'ff0002', 'ff0003']],
                                 names=['Warnings', 'equip'])
col = ['count per equip']

df = pd.DataFrame([100,2,1,44,45,20,25,98,0], idx, col)

Grouping based on level = 0

df['total'] = df.groupby(level=0).transform(lambda x: x.size)

df = df.set_index('total', append=True).reorder_levels(['Warnings','total','equip'])

print(df)

                       count per equip
Warnings total equip                  
warning1 3     ff0001              100
               ff0002                2
               ff0003                1
warning2 3     ff0001               44
               ff0002               45
               ff0003               20
warning3 3     ff0001               25
               ff0002               98
               ff0003                0

Upvotes: 1

Related Questions