2
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – Nate T
    Nov 12 '21 at 3:11
3
\$\begingroup\$

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'])

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.