Compute the mean of every X rows of a pandas DataFrame

Question

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

Answer 1

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

• Avoid iterating a DataFrame
• Avoid growing a DataFrame

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_row^th 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'])}