Home How to add up two consecutive rows in a multiindex dataframe repeating the last one in the group if it has an odd number of rows?

Questions

How to add up two consecutive rows in a multiindex dataframe repeating the last one in the group if it has an odd number of rows?

byMR

July 6, 2022

With the following dataframe df1:

        0   1
0   0   8   2
    1   13  5
    2   11  9
    3   4   1
    4   83  3
1   0   25  3
    1   22  4
    2   87  8
    3   60  8
    4   33  7

How can I consecutively add up two rows from the top of the inner level leaving the last row the same if the number of inner levels is odd? What I want is to create a new dataframe df2 that looks like:

        0   1
0   0   21  7
    1   15  10
    2   83  3
1   0   47  7
    1   147 16
    2   33  7

Row 0 in df2 is the sum of rows 0 and 1 in df1, row 1 in df2 is the sum of rows 2 and 3 in df1 and row 2 in df2 is just a copy of row 4 in df1 given df1 has an odd number of rows. If the number of rows is even, of course if I had 6 rows instead of 5, I would need to perform the addition as well.

>Solution :

Because counter is in second level of MultiIndex is possible create groups by integer division by 2 with MultiIndex.get_level_values and aggregate sum:

df = df.groupby([df.index.get_level_values(0), df.index.get_level_values(1) // 2]).sum()
print (df)
       0   1
0 0   21   7
  1   15  10
  2   83   3
1 0   47   7
  1  147  16
  2   33   7

Or if set index values is possible use i1 instaed extracted values:

df = df.rename_axis(['i1','i2']).groupby(['i1', df.index.get_level_values(1) // 2]).sum()
print (df)
        0   1
i1           
0  0   21   7
   1   15  10
   2   83   3
1  0   47   7
   1  147  16
   2   33   7