CODEWITHSUNDEEP
pythonpython-3.xpandas
New_Bee
New_Bee

Reputation: 5

Adding Rows in pandas data frame based on multilevel index

i have a dataframe like :

(multilevel)index  aaa,aaa,aaa,bbb,bbb,bbb,ccc,ccc

    Column          1,  1 , 1 , 0,  1,  0,  1 , 1

i want to add rows based on index so that i get:

   index       aaa,    bbb,     ccc 
   column       3,      1,       2

is it possible ?

any help would be appreciated.

Thank you

Upvotes: 0

Views: 161

Answers (2)

ALollz
ALollz

Reputation: 59549

If the columns have the same name, you can get where you want with stack + pivot_table setting the aggregation function to sum.

Setup

import pandas as pd
df = pd.DataFrame({'id1': [1,1,1,2], 'id2': [1,2,3,1],
                 '1': [1,1,1,1], '2': [0,0,1,1], '3': [0,1,0,0],
                 '4': [0,0,0,0], '5': [1,1,0,0], '6': [0,1,0,0]})
df = df.set_index(['id1', 'id2'])
df.columns=['aaa','aaa','aaa','bbb','bbb','bbb']

         aaa  aaa  aaa  bbb  bbb  bbb
id1 id2                              
1   1      1    0    0    0    1    0
    2      1    0    1    0    1    1
    3      1    1    0    0    0    0
2   1      1    1    0    0    0    0

Stack and pivot

stacked = df.stack().reset_index()
df = pd.pivot_table(stacked, index=['id1', 'id2'], columns='level_2', values=0, aggfunc='sum')
df.columns.name=None

print(df)
         aaa  bbb
id1 id2          
1   1      1    1
    2      2    2
    3      2    0
2   1      2    0

Upvotes: 0

Ankur Sinha
Ankur Sinha

Reputation: 6649

Perhaps you can do something like this by transposing?

import pandas as pd

df = pd.DataFrame([['aaa', 'aaa', 'aaa','bbb', 'bbb', 'bbb', 'ccc', 'ccc'],
                   [1, 1, 1, 0, 1, 0, 1, 1]], index = ['index', 'column'])

So, I would first take transpose, group them, and then take a transpose again:

df = df.T
df = df.groupby('index').sum()
df = df.T

Output:

index   aaa  bbb  ccc
column    3    1    2

Upvotes: 1

Related Questions