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.
CodePudding user response:
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