Reduce the number of rows in a dataframe based on a condition

Question

I have a dataframe which consists of 9821 rows and one column. The values in it are listed in groups of 161 produced 61 times (161X61=9821). I need to reduce the number of rows to 9660 (161X60=9660) by replacing the first 2 values of each group of 161 into an average of those 2 values. In more simple words, in my existing dataframe the following groups of indexes (0, 1), (61, 62) ... (9760, 9761) need to be averaged in order to get a new dataframe with 9660 rows. Any ideas?

this is what I have (groups of 4 produced 3 times - 4X3=12):

0 10

1 11

2 12

3 13

4 14

5 15

6 16

7 17

8 18

9 19

10 20

11 21

this is what I want (groups of 3 produced 3 times - 3X3=9):

0 10.5

1 12

2 13

3 14.5

4 16

5 17

6 18.5

7 20

8 21

It would be nice if you could paste your data and the expected output. This is not very easy to understand. — NYC Coder, Jun 14 '20 at 15:10
Please provide a small set of sample data as text that we can copy and paste. Include the corresponding desired result. Check out the guide on [how to make good reproducible pandas examples](https://stackoverflow.com/a/20159305/3620003). — timgeb, Jun 14 '20 at 15:12
Can you please explain how you are averaging in order to arrive at the numbers in the expected output? — timgeb, Jun 14 '20 at 15:41
No problem, I apologize for the confusion caused. In the data I provided the whole data can be divided into 3 separate groups with 4 rows in each group. So what i need to do is to average the first 2 rows of each group, thus arriving at 3 separate groups with 3 rows in each group. — Rauan Saturin, Jun 14 '20 at 15:45
Could you make an example? E.g. 10.5 is calculated as follows and 18.5 is calculated as follows: ... — timgeb, Jun 14 '20 at 15:46
OK but your next number is 12, but neither (11 + 12)/2 = 12 nor (12 + 13)/2 = 12. — timgeb, Jun 14 '20 at 15:49
yes exactly, my first group is 10, 11, 12, 13 and i need only first 2 rows to be averaged and get 10.5, 12, 13. my second group is 14, 15, 16, 17 and i should get 14.5, 16, 17 (again only first 2 rows of the group are averaged) and so on. — Rauan Saturin, Jun 14 '20 at 15:52

score 1 · Accepted Answer · answered Jun 14 '20 at 16:13

1

I'm not super happy with this answer but I'm putting it out there for review.

>>> df[df.index%4 == 0] = df.groupby(df.index//4).apply(lambda s: s.iloc[:2].mean()).values
>>> df = df[:-3]
>>> df
      0
0  10.5
1  11.0
2  12.0
3  13.0
4  14.5
5  15.0
6  16.0
7  17.0
8  18.5

answered Jun 14 '20 at 16:13

timgeb

76,762
20
123
145

Worked for that example I gave, but didn't work for my real data for some reason. Thanks anyways! I have created my own code which solved my task, have a look if you wish :) – Rauan Saturin Jun 14 '20 at 19:36

score 0 · Answer 2 · answered Jun 14 '20 at 19:34

rotation - my existing dataframe (161X61) rot- my new dataframe (161X60)

arr = np.zeros((9821, 1))
rot = pd.DataFrame(arr, index=range(0, 9821))
for i in range(0, 9821):
    if i==0:
        rot.iloc[i, 0] = (rotation.iloc[i, 0]+rotation.iloc[i+1, 0])/2
    elif ((i%61)==0):
        rot.iloc[i-1, 0] = (rotation.iloc[i, 0]+rotation.iloc[i+1, 0])/2
        rot.iloc[i, 0] = 'del'
    else:
        if ((i==9820)):
            rot.iloc[i, 0] = 'del'
            break
        rot.iloc[i, 0]=rotation.iloc[i+1, 0]
rot.columns = ['alpha']
rot = rot[~rot['alpha'].isin(['del'])]
rot.reset_index(drop=True, inplace=True)
rot

Reduce the number of rows in a dataframe based on a condition

2 Answers2