# Compute the mean of every X rows of a pandas DataFrame

I coded a little loop that reduces a dataset by computing the mean of X rows every X rows.
I'm working with a dataset of a certain size (8 Millions rows for 10 columns) and my code takes too long to compute the whole dataset.
I am using Pandas.
Is there a way to improve my loop to make it more efficient and faster ?


# data is the dataset, pd.DataFrame()
#
# nb_row is the number of rows to collapse
#   for example : nb_row = 12 would reduce the dataset by x12
#   by computing the mean of 12 rows every 12 rows

nb_row = 12
raw_columns = ["f1", "f2", "f3", "f4"] # column names
for i in range(0, len(data), nb_row):
new_df = new_df.append(
pd.DataFrame(
[[data[elem].iloc[i : i + nb_row].mean() for elem in raw_columns]],
columns=raw_columns,
)
)


Thanks

• Is one of those "x rows" in the top row supposed to be something else? Otherwise, I think youve pretty much got it covered... : ) 3 out of 3 ain't bad; it's perfect! ...sry, I would've edited, but it's slightly over my head. Didn't want to mess it up. Nov 12 '21 at 3:11

Your current code breaks two major pandas antipatterns, explained in depth by @cs95:

The more idiomatic approaches are groupby.mean (fastest) and rolling.mean:

### 1. groupby.mean (fastest)

(This is ~5.5x faster than rolling.mean at 8M rows.)

Generate pseudo-groups with data.index // nb_row and take the groupby.mean:

nb_row = 12
new_df = data.groupby(data.index // nb_row).mean()

#               f1        f2        f3        f4
# 0      -0.171037 -0.288915  0.026885 -0.083732
# 1      -0.018497  0.329961  0.107714 -0.430527
# ...          ...       ...       ...       ...
# 666665 -0.648026  0.232827  0.290072  0.535636
# 666666 -0.373699 -0.260689  0.122012 -0.217035
#
# [666667 rows x 4 columns]


Note that if the real data has a custom index (instead of a default RangeIndex), then use groupby(np.arange(len(df)) // nb_row) instead.

### 2. rolling.mean

rak1507's answer covers rolling.mean but is missing a key detail.

rolling(nb_row) uses a window size of nb_row but slides 1 row at a time, not every nb_row rows.

• That means slicing with [nb_row-1:] results in the wrong output shape (it's not reduced by a factor of nb_row as described in the OP):

new_df = data.rolling(nb_row).mean()[nb_row-1:]
new_df.shape

# (7999989, 4)

• Instead we should slice with [nb_row::nb_row] to get every nb_rowth row:

new_df = data.rolling(nb_row).mean()[nb_row::nb_row]
new_df.shape

# (666666, 4)


### Timings (8M rows*)

%timeit data.groupby(data.index // nb_row).mean()
631 ms ± 56.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit data.rolling(nb_row).mean()[nb_row::nb_row]
3.47 s ± 274 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


* data = pd.DataFrame(np.random.random((8_000_000, 4)), columns=['f1', 'f2', 'f3', 'f4'])

• Oops yeah you're right I forgot to slice every nth element too. Nice answer - til about groupby Nov 12 '21 at 9:04