Utilizing rolling() with an apply() function with groups in pd.DataFrame object

user_14804206 0
Viewed 47

I am trying to calculate the gradient between each row of the same group with

def get_gradient(row, row2):
    return (row['foo'] - row2['foo'])/(row['bar'] - row2['bar'])

On the example table below:

Running get_gradient(demo.iloc[0],demo.iloc[1]) would get the first non np.nan value. then get_gradient(demo.iloc[1],demo.iloc[2]) would get the next value. However, when baz becomes B, we shift to a "new window"

demo = pd.DataFrame(
    {
        'foo':[0.023939,0.180167,0.316840,0.497396,0.632713,0.332927,0.489553,0.677083,0.810815,0.967808],
        'bar':[0.027107,0.070630,0.014774,0.059228,0.033585,0.010692,0.012511,0.011934,0.028882,0.365916],
        'baz':['A','A','A','A','A','B','B','B','B','B',]
    }
)
foo bar baz
0 0.023939 0.027107 A
1 0.180167 0.07063 A
2 0.31684 0.014774 A
3 0.497396 0.059228 A
4 0.632713 0.033585 A
5 0.332927 0.010692 B
6 0.489553 0.012511 B
7 0.677083 0.011934 B
8 0.810815 0.028882 B
9 0.967808 0.365916 B

I expect to achieve the following table:

foo bar baz grad
0 0.023939 0.027107 A nan
1 0.180167 0.07063 A 3.58955
2 0.31684 0.014774 A -2.44688
3 0.497396 0.059228 A 4.06164
4 0.632713 0.033585 A -5.27696
5 0.332927 0.010692 B nan
6 0.489553 0.012511 B 86.1056
7 0.677083 0.011934 B -325.009
8 0.810815 0.028882 B 7.89072
9 0.967808 0.365916 B 0.465808

I am aware of groupby(), rolling() and apply(,axis=1), but unable to determine the correct chaining sequence. What is the best way to chain these methods to form the expected table?

pandas dataframe python-3.x
2 Answers

Here is a simple way to do it:

foo_diff = demo.groupby('baz')['foo'].diff()
bar_diff = demo.groupby('baz')['bar'].diff()
demo['gradient'] = foo_diff/bar_diff

Result:

    foo         bar         baz gradient
0   0.023939    0.027107    A   NaN
1   0.180167    0.070630    A   3.589550
2   0.316840    0.014774    A   -2.446881
3   0.497396    0.059228    A   4.061637
4   0.632713    0.033585    A   -5.276957
5   0.332927    0.010692    B   NaN
6   0.489553    0.012511    B   86.105553
7   0.677083    0.011934    B   -325.008666
8   0.810815    0.028882    B   7.890725
9   0.967808    0.365916    B   0.465808
user_74559170

Here is how I would do it with just the features of a dataframe

demo = pd.DataFrame(
    {
        'foo':[0.023939,0.180167,0.316840,0.497396,0.632713,0.332927,0.489553,0.677083,0.810815,0.967808],
        'bar':[0.027107,0.070630,0.014774,0.059228,0.033585,0.010692,0.012511,0.011934,0.028882,0.365916],
        'baz':['A','A','A','A','A','B','B','B','B','B',]
    }
)
demo['foo_prev'] = demo.groupby('baz')['foo'].shift()
demo['bar_prev'] = demo.groupby('baz')['bar'].shift()
demo['grad']=(demo['foo'] - demo['foo_prev'])/(demo['bar'] - demo['bar_prev'])

and this gives

    foo         bar         baz foo_prev    bar_prev    grad
0   0.023939    0.027107    A   NaN         NaN         NaN
1   0.180167    0.070630    A   0.023939    0.027107    3.589550
2   0.316840    0.014774    A   0.180167    0.070630    -2.446881
3   0.497396    0.059228    A   0.316840    0.014774    4.061637
4   0.632713    0.033585    A   0.497396    0.059228    -5.276957
5   0.332927    0.010692    B   NaN         NaN         NaN
6   0.489553    0.012511    B   0.332927    0.010692    86.105553
7   0.677083    0.011934    B   0.489553    0.012511    -325.008666
8   0.810815    0.028882    B   0.677083    0.011934    7.890725
9   0.967808    0.365916    B   0.810815    0.028882    0.465808

and you can run df.drop(['foo_prev','bar_prev'], axis =1, inplace = True) if you don't want to see the workings after you are done.

user_54783730
Related