Chipmunkafy
Chipmunkafy

Reputation: 586

Get the rolling sum of values of id from two different column?

df:

id1     id2     value1    value2
-----------------------------------
a       b       10        5
c       a       5         10
b       c       0         0
c       d       2         1
d       a       10        20
a       c       5         10

get sum of values associated with id 'a' from column ['id1','id2']:

id1     id2     a.rolling(2).sum()
-----------------------------------
a       b       NaN
c       a       20
d       a       30
a       c       25

How would I get the rolling sum of values of id 'a' from two different column with a df.groupby function?

I tried this df.groupby(['id1','id2])['value1','value2'].transform(lambda x: x.rolling(2).sum()), but that did't work.

Upvotes: 3

Views: 277

Answers (2)

BENY
BENY

Reputation: 323236

Using concat after filter

df1=df.filter(like='1')
df2=df.filter(like='2')
df2.columns=df1.columns
s=pd.concat([df1,df2]).sort_index().groupby('id1').rolling(2).sum()
s=s.loc['a']
df.loc[s.index].assign(new=s)
Out[99]: 
  id1 id2  value1  value2   new
0   a   b      10       5   NaN
1   c   a       5      10  20.0
4   d   a      10      20  30.0
5   a   c       5      10  25.0

Upvotes: 2

piRSquared
piRSquared

Reputation: 294278

Here's one way to do it

i = df.filter(like='id')
v = df.filter(like='va')

x, y = np.where(i == 'a')

df.iloc[x].assign(A=v.values[x, y]).assign(Roll=lambda d: d.A.rolling(2).sum())

  id1 id2  value1  value2   A  Roll
0   a   b      10       5  10   NaN
1   c   a       5      10  10  20.0
4   d   a      10      20  20  30.0
5   a   c       5      10   5  25.0

Upvotes: 5

Related Questions