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