Pandas - Using `.rolling()` on multiple columns

Question

Consider a pandas DataFrame which looks like the one below

      A     B     C
0  0.63  1.12  1.73
1  2.20 -2.16 -0.13
2  0.97 -0.68  1.09
3 -0.78 -1.22  0.96
4 -0.06 -0.02  2.18

I would like to use the function .rolling() to perform the following calculation for t = 0,1,2:

Select the rows from t to t+2
Take the 9 values contained in those 3 rows, from all the columns. Call this set S
Compute the 75th percentile of S (or other summary statistics about S)

For instance, for t = 1 we have S = { 2.2 , -2.16, -0.13, 0.97, -0.68, 1.09, -0.78, -1.22, 0.96 } and the 75th percentile is 0.97.

I couldn't find a way to make it work with .rolling(), since it apparently takes each column separately. I'm now relying on a for loop, but it is really slow.

Do you have any suggestion for a more efficient approach?

You have enough rep know that not post the data as picture — BENY
– BENY, Commented Jul 31, 2018 at 14:35
@RushabhMehta: that's exactly the function I am trying to use, but I don't understand how pull the data from the 3 columns A,B and C together to compute the percentile. — Abramodj
– Abramodj, Commented Jul 31, 2018 at 14:39
Just paste it as text and format it as code. Read this: stackoverflow.com/questions/20109391/… — user3483203
– user3483203, Commented Jul 31, 2018 at 14:45
I don't like the answers involving reshaping things. I used scipy.ndimage.generic_filter to do this properly, though it needed some trial and error to get the same effect as Pandas' rolling's center=False effect. — Ahmed Fasih
– Ahmed Fasih, Commented Apr 19, 2020 at 19:25

ALollz · Accepted Answer · 2018-08-15 15:27:31Z

4

One solution is to stack the data and then multiply your window size by the number of columns and slice the result by the number of columns. Also, since you want a forward looking window, reverse the order of the stacked DataFrame

wsize = 3
cols = len(df.columns)

df.stack(dropna=False)[::-1].rolling(window=wsize*cols).quantile(0.75)[cols-1::cols].reset_index(-1, drop=True).sort_index()

Output:

0    1.12
1    0.97
2    0.97
3     NaN
4     NaN
dtype: float64

In the case of many columns and a small window:

import pandas as pd
import numpy as np

wsize = 3
df2 = pd.concat([df.shift(-x) for x in range(wsize)], 1)
s_quant = df2.quantile(0.75, 1)

# Only necessary if you need to enforce sufficient data. 
s_quant[df2.isnull().any(1)] = np.NaN

Output: s_quant

0    1.12
1    0.97
2    0.97
3     NaN
4     NaN
Name: 0.75, dtype: float64

edited Aug 15, 2018 at 15:27

answered Jul 31, 2018 at 14:50

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Abramodj Over a year ago

This is not equivalent, as it would compute the statistics many more times. This is because the window would move every 1 entry and not every 3. In my real dataset I have many more columns than 3, so this actually makes a huge difference computationally speaking.

ALollz Over a year ago

@Abramodj I find it takes 8x longer for 1000x the number of columns. So as long as you have a reasonable number of columns, the excess probably wont kill you. It wasn't clear that you needed an extremely performant solution over just a solution to your problem. Let me see what I can do.

Abramodj Over a year ago

Actually, I have more than 10k columns

Abramodj Over a year ago

About one thousand, so the for loop is actually feasible in this case. I just wanted to understand if this can be done, as it could be very useful in other circumstances.

FLab Over a year ago

@ALollz I think you should specify dropna=True in stack, otherwise the window size would be consistent

|

Dhanush1215 · Accepted Answer · 2018-07-31 15:05:51Z

0

You can use numpy ravel. Still you may have to use for loops.

for i in range(0,3):
    print(df.iloc[i:i+3].values.ravel())

If your t steps in 3s, you can use numpy reshape function to create a n*9 dataframe.

answered Jul 31, 2018 at 15:05

Dhanush1215

514 bronze badges

2 Comments

Abramodj Over a year ago

This is the solution I am using at the moment, which requires a for loop. It is not as fast as rolling because it is not vectorized

Dhanush1215 Over a year ago

@Abramodj Have you tried this: x = pd.concat([df, df.shift(-1),df.shift(-2)], axis=1) then x['m'] = x.mean(axis=1). For quartile you can use x['q3'] = x.quantile(0.75, axis=1).

Collectives™ on Stack Overflow

Pandas - Using `.rolling()` on multiple columns

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related