41

I am trying to use a pandas.DataFrame.rolling.apply() rolling function on multiple columns. Python version is 3.7, pandas is 1.0.2.

import pandas as pd

#function to calculate
def masscenter(x):
    print(x); # for debug purposes
    return 0;

#simple DF creation routine
df = pd.DataFrame( [['02:59:47.000282', 87.60, 739],
                    ['03:00:01.042391', 87.51, 10],
                    ['03:00:01.630182', 87.51, 10],
                    ['03:00:01.635150', 88.00, 792],
                    ['03:00:01.914104', 88.00, 10]], 
                   columns=['stamp', 'price','nQty'])
df['stamp'] = pd.to_datetime(df2['stamp'], format='%H:%M:%S.%f')
df.set_index('stamp', inplace=True, drop=True)

'stamp' is monotonic and unique, 'price' is double and contains no NaNs, 'nQty' is integer and also contains no NaNs.

So, I need to calculate rolling 'center of mass', i.e. sum(price*nQty)/sum(nQty).

What I tried so far:

df.apply(masscenter, axis = 1)

masscenter is be called 5 times with a single row and the output will be like

price     87.6
nQty     739.0
Name: 1900-01-01 02:59:47.000282, dtype: float64

It is desired input to a masscenter, because I can easily access price and nQty using x[0], x[1]. However, I stuck with rolling.apply() Reading the docs DataFrame.rolling() and rolling.apply() I supposed that using 'axis' in rolling() and 'raw' in apply one achieves similiar behaviour. A naive approach

rol = df.rolling(window=2)
rol.apply(masscenter)

prints row by row (increasing number of rows up to window size)

stamp
1900-01-01 02:59:47.000282    87.60
1900-01-01 03:00:01.042391    87.51
dtype: float64

then

stamp
1900-01-01 02:59:47.000282    739.0
1900-01-01 03:00:01.042391     10.0
dtype: float64

So, columns is passed to masscenter separately (expected).

Sadly, in the docs there is barely any info about 'axis'. However the next variant was, obviously

rol = df.rolling(window=2, axis = 1)
rol.apply(masscenter)

Never calls masscenter and raises ValueError in rol.apply(..)

> Length of passed values is 1, index implies 5

I admit that I'm not sure about 'axis' parameter and how it works due to lack of documentation. It is the first part of the question: What is going on here? How to use 'axis' properly? What it is designed for?

Of course, there were answers previously, namely:

How-to-apply-a-function-to-two-columns-of-pandas-dataframe
It works for the whole DataFrame, not Rolling.

How-to-invoke-pandas-rolling-apply-with-parameters-from-multiple-column
The answer suggests to write my own roll function, but the culprit for me is the same as asked in comments: what if one needs to use offset window size (e.g. '1T') for non-uniform timestamps?
I don't like the idea to reinvent the wheel from scratch. Also I'd like to use pandas for everything to prevent inconsistency between sets obtained from pandas and 'self-made roll'. There is another answer to that question, suggessting to populate dataframe separately and calculate whatever I need, but it will not work: the size of stored data will be enormous. The same idea presented here:
Apply-rolling-function-on-pandas-dataframe-with-multiple-arguments

Another Q & A posted here
Pandas-using-rolling-on-multiple-columns
It is good and the closest to my problem, but again, there is no possibility to use offset window sizes (window = '1T').

Some of the answers were asked before pandas 1.0 came out, and given that docs could be much better, I hope it is possible to roll over multiple columns simultaneously now.

The second part of the question is: Is there any possibility to roll over multiple columns simultaneously using pandas 1.0.x with offset window size?

Improve this question
3
  • masscenter is just a function that maps every x into zero? Commented Aug 9, 2022 at 6:03
  • df2['stamp'] undefined though. Did I miss anything? Commented Aug 9, 2022 at 11:08
  • 1
    @HighGPA the 'masscenter' function was constructed this way to create a minimal, reproducible example. Have you declared columns=['stamp', 'price','nQty']? Commented Aug 13, 2022 at 14:55

7 Answers 7

Reset to default
47

How about this:

import pandas as pd

def masscenter(ser: pd.Series, df: pd.DataFrame):
    df_roll = df.loc[ser.index]
    return your_actual_masscenter(df_roll)

masscenter_output = df['price'].rolling(window=3).apply(masscenter, args=(df,))

It uses the rolling logic to get subsets via an arbitrary column. The arbitrary column itself is not used, only the rolling index is used. This relies on the default of raw=False which provides the index values for those subsets. The applied function uses those index values to get multi-column slices from the original dataframe.

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

15 Comments

This is the answer at least for the second part. I knew that it should be possible. Such simple and pythonic solution! Thank you very much!
This is a very expensive solution, though, for larger datasets.
Could you elaborate on your observation?
"then you use those index values to get multi-column slices from your original DataFrame" - do you mean .iloc[i, c] to reach into the dataframe from inside masscenter? i.e. masscenter doesn't have the arguments it needs directly?
Works for me though pandas' devs should really implement this somehow...
|
20

You can use rolling_apply function from numpy_ext module:

import numpy as np
import pandas as pd
from numpy_ext import rolling_apply


def masscenter(price, nQty):
    return np.sum(price * nQty) / np.sum(nQty)


df = pd.DataFrame( [['02:59:47.000282', 87.60, 739],
                    ['03:00:01.042391', 87.51, 10],
                    ['03:00:01.630182', 87.51, 10],
                    ['03:00:01.635150', 88.00, 792],
                    ['03:00:01.914104', 88.00, 10]], 
                   columns=['stamp', 'price','nQty'])
df['stamp'] = pd.to_datetime(df['stamp'], format='%H:%M:%S.%f')
df.set_index('stamp', inplace=True, drop=True)

window = 2
df['y'] = rolling_apply(masscenter, window, df.price.values, df.nQty.values)
print(df)

                            price  nQty          y
stamp                                             
1900-01-01 02:59:47.000282  87.60   739        NaN
1900-01-01 03:00:01.042391  87.51    10  87.598798
1900-01-01 03:00:01.630182  87.51    10  87.510000
1900-01-01 03:00:01.635150  88.00   792  87.993890
1900-01-01 03:00:01.914104  88.00    10  88.000000
Improve this answer

2 Comments

Thank you, but alas! It also accepts fixed window size - 2 (or whatever number) of points, but not seconds or another so-called offsets. However, you gave me an idea. I will try it and if it works will post soon.
If your data is not too sparse, you could use rolling_apply as suggested using a window large enough to encompass given time offset records, and incorporate a bounds check against the stamp inside the applied function. You might need to use large windows, but potentially rolling_apply's ability to execute parallel jobs might make up for that.
7

For performing a rolling window operation with access to all columns of a dataframe, you can pass mehtod='table' to rolling(). Example:

import pandas as pd
import numpy as np
from numba import jit

df = pd.DataFrame({'a': [1, 2, 3, 4, 5, 6], 'b': [1, 3, 5, 7, 9, 11]})

@jit
def f(w):
    # we have access to both columns of the dataframe here
    return np.max(w), np.min(w)

df.rolling(3, method='table').apply(f, raw=True, engine='numba')

It should be noted that method='table' requires numba engine (pip install numba). The @jit part in the example is not mandatory but helps with performance. The result of the above example code will be:

a b
NaN NaN
NaN NaN
5.0 1.0
7.0 2.0
9.0 3.0
11.0 4.0
Improve this answer

Comments

6

With reference to the excellent answer from @saninstein.

Install numpy_ext from: https://pypi.org/project/numpy-ext/

import numpy as np
import pandas as pd
from numpy_ext import rolling_apply as rolling_apply_ext

def box_sum(a,b):
    return np.sum(a) + np.sum(b)

df = pd.DataFrame({"x": [1,2,3,4], "y": [1,2,3,4]})

window = 2
df["sum"] = rolling_apply_ext(box_sum, window , df.x.values, df.y.values)

Output:

print(df.to_string(index=False))
 x  y  sum
 1  1  NaN
 2  2  6.0
 3  3 10.0
 4  4 14.0

Notes

  • The rolling function is timeseries friendly. It defaults to always looking backwards, so the 6 is the sum of present and past values in the array.
  • In the sample above, imported rolling_apply as rolling_apply_ext so it cannot possibly interfere with any existing calls to Pandas rolling_apply (thanks to comment by @LudoSchmidt).

As a side note, I gave up trying to use Pandas. It's fundamentally broken: it handles single-column aggreagate and apply with little problems, but it's a overly complex rube-goldberg machine when trying to get it to work with more two columns or more.

Improve this answer

4 Comments

personal expérience with your respond : I pip install and used "from numpy_ext import rolling_apply". But it destroyed my pandas in my script.
@LudoSchmidt Good point. Updated code above to import rolling_apply as rolling_apply_ext, so everything is backwards compatible with the existing rolling_apply calls in Pandas.
"I gave up trying to use Pandas. It's fundamentally broken" - this, unfortunately.
how would you do this on a GroupBy?
1

So I found no way to roll over two columns, however without inbuilt pandas functions. The code is listed below.

# function to find an index corresponding
# to current value minus offset value
def prevInd(series, offset, date):
    offset = to_offset(offset)
    end_date = date - offset
    end = series.index.searchsorted(end_date, side="left")
    return end

# function to find an index corresponding
# to the first value greater than current
# it is useful when one has timeseries with non-unique
# but monotonically increasing values
def nextInd(series, date):
    end = series.index.searchsorted(date, side="right")
    return end

def twoColumnsRoll(dFrame, offset, usecols, fn, columnName = 'twoColRol'):
    # find all unique indices
    uniqueIndices = dFrame.index.unique()
    numOfPoints = len(uniqueIndices)
    # prepare an output array
    moving = np.zeros(numOfPoints)
    # nameholders
    price = dFrame[usecols[0]]
    qty   = dFrame[usecols[1]]

    # iterate over unique indices
    for ii in range(numOfPoints):
        # nameholder
        pp = uniqueIndices[ii]
        # right index - value greater than current
        rInd = afta.nextInd(dFrame,pp)
        # left index - the least value that 
        # is bigger or equal than (pp - offset)
        lInd = afta.prevInd(dFrame,offset,pp)
        # call the actual calcuating function over two arrays
        moving[ii] = fn(price[lInd:rInd], qty[lInd:rInd])
    # construct and return DataFrame
    return pd.DataFrame(data=moving,index=uniqueIndices,columns=[columnName])

This code works, but it is relatively slow and inefficient. I suppose one can use numpy.lib.stride_tricks from How to invoke pandas.rolling.apply with parameters from multiple column? to speedup things. However, go big or go home - I ended writing a function in C++ and a wrapper for it.
I'd like not to post it as answer, since it is a workaround and I have not answered neither part of my question, but it is too long for a commentary.

Improve this answer

Comments

1 
(df['price'] * df['nQty']).rolling(2).sum() / df['nQty'].rolling(2).sum()

# output
    stamp
1900-01-01 02:59:47.000282          NaN
1900-01-01 03:00:01.042391    87.598798
1900-01-01 03:00:01.630182    87.510000
1900-01-01 03:00:01.635150    87.993890
1900-01-01 03:00:01.914104    88.000000
dtype: float64

You can use rolling sum for price*nQty and nQty part then calculating the mean. The same solution can be used with offset window size.

Improve this answer

1 Comment

This works, but one needs to compute rolling more than once, so the solution is (very) slow for large datasets.
0

How about this?

ggg = pd.DataFrame({"a":[1,2,3,4,5,6,7], "b":[7,6,5,4,3,2,1]})

def my_rolling_apply2(df, fun, window):
    prepend = [None] * (window - 1)
    end = len(df) - window
    mid = map(lambda start: fun(df[start:start + window]), np.arange(0,end))
    last =  fun(df[end:])
    return [*prepend, *mid, last]

my_rolling_apply2(ggg, lambda df: (df["a"].max(), df["b"].min()), 3)

And result is:

[None, None, (3, 5), (4, 4), (5, 3), (6, 2), (7, 1)]
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.