Home > OS >  How to calculate all aggregations at once without using a loop over indices?
How to calculate all aggregations at once without using a loop over indices?

Time:02-21

How to calculate all aggregations at once without using a loop over indices?

%%time
import random
random.seed(1)
df = pd.DataFrame({'val':random.sample(range(10), 10)})

for j in range(10):
    for i in df.index:
        df.loc[i,'mean_last_{}'.format(j)] = df.loc[(df.index < i) & (df.index >= i - j),'val'].mean()
        df.loc[i,'std_last_{}'.format(j)] = df.loc[(df.index < i) & (df.index >= i - j),'val'].std()
        df.loc[i,'max_last_{}'.format(j)] = df.loc[(df.index < i) & (df.index >= i - j),'val'].max()
        df.loc[i,'min_last_{}'.format(j)] = df.loc[(df.index < i) & (df.index >= i - j),'val'].min()
        df.loc[i,'median_last_{}'.format(j)] = df.loc[(df.index < i) & (df.index >= i - j),'val'].median()

CodePudding user response:

You could use the rolling method, see for example:

df = pd.DataFrame({'val': np.random.random(100)})
for i in range(10):
    agg = df["val"].rolling(i).aggregate(['mean', 'median'])
    df[[f"mean_{i}", f"median_{i}"]] = agg.values

CodePudding user response:

I think what you're looking for is something like this:

import random
random.seed(1)
df = pd.DataFrame({'val':random.sample(range(10), 10)})

for j in range(1, 10):
    df[f'mean_last_{j}'] = df['val'].rolling(j, min_periods=1).mean()
    df[f'std_last_{j}'] = df['val'].rolling(j, min_periods=1).std()
    df[f'max_last_{j}'] = df['val'].rolling(j, min_periods=1).max()
    df[f'min_last_{j}'] = df['val'].rolling(j, min_periods=1).min()
    df[f'median_last_{j}'] = df['val'].rolling(j, min_periods=1).median()

However, my code is "off-by-one" relative to your example code. Do you intend for each aggregation INCLUDE value from the current row, or should it only use the previous j rows, without the current one? My code includes the current row, but yours does not. Your code results in NaN values for the first group of aggregations.

Edit: The answer from @Carlos uses rolling(j).aggregate() to specify list of aggregations in one line. Here's what that looks like:

import random
random.seed(1)
df = pd.DataFrame({'val':random.sample(range(10), 10)})

for j in range(10):
    aggs = ['mean', 'std', 'max', 'min', 'median']
    stats = df["val"].rolling(j, min_periods=min(j, 1)).aggregate(aggs)
    df[[f"{a}_last_{j}" for a in aggs]] = stats.values
  • Related