Search code examples
pythonpandasdataframemulti-index

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?


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