Python equivalent to Spark rangeBetween for window?

user_1308667 0
Viewed 179

I am trying to find if there is a way in python to do the equivalent of a rangeBetween in a rolling aggregation. In Spark, you can use rangeBetween such that the window does not have to be symmetrical on the targeted row, ie for each row, I can look -5h to +3h: All rows that happen between 5 hours before and 3 hours after absed on a datetime column. I know that python has the pd.rolling option, but after reading all the documentation i can find on it it looks like it only takes 1 input as the window. You can change whether that window is centered on each row or not but I can't find a way to explicitly set it so it can look at a range of my choosing.

Does anyone know of another function or functionality that I am not aware of that would work to do this?

pyspark apache-spark python
1 Answers

I'm not sure if it's the best answer but it's mine and it works so I guess it'll have to do until there is a better option. I made a python function out of it so you can sub in whatever aggregation function you want.

def rolling_stat(pdf, lower_bound, upper_bound, group , statistic = 'mean' )
    import pandas as pd
    import numpy as np
    import random
    from datetime import datetime, timedelta

    group = pdf[group].drop_duplicates()
    for grp in group:
        dataframe_grp = dataframe[dataframe['group']==grp]
        dataframe_grp.sort_index()
        for index, row in dataframe_grp.iterrows(): 
            lower= (index - timedelta(minutes = lower_bound))
            upper=  (index + timedelta(minutes = upper_bound))    
            agg = dataframe_grp.loc[lower:upper]['nbr'].agg([statistic])
            dataframe_grp.at[index, 'agg'] = agg[0]     
            data_agg = data_agg.append(dataframe_grp)
user_13086670
Related