0

How to create cumulative sum (new_supply)in dataframe python from demand column from table

item   Date       supply  demand
A     2018-01-01  0       10
A     2018-01-02  0       15
A     2018-01-03  100     30
A     2018-01-04  0       10
A     2018-01-05  0       40
A     2018-01-06  50      50
A     2018-01-07  0       10
B     2018-01-01  0       20
B     2018-01-02  0       30
B     2018-01-03  20      60
B     2018-01-04  0       20
B     2018-01-05  100     10
B     2018-01-06  0       20
B     2018-01-07  0       30

New Desired table from the above table

item   Date       supply  demand  new_supply
A     2018-01-01  0       10      0
A     2018-01-02  0       15      0
A     2018-01-03  100     30      55
A     2018-01-04  0       10      0
A     2018-01-05  0       40      0
A     2018-01-06  50      50      100
A     2018-01-07  0       10      0
B     2018-01-01  0       20      0
B     2018-01-02  0       30      0
B     2018-01-03  20      60      110
B     2018-01-04  0       20      0
B     2018-01-05  100     10      140
B     2018-01-06  0       20      0
B     2018-01-07  0       30      0
Naga kiran
  • 4,528
  • 1
  • 17
  • 31
Doni Suhartono
  • 23
  • 3
  • 5
    Possible duplicate of [How to create cummulative sum in dataframe python?](https://stackoverflow.com/questions/53139404/how-to-create-cummulative-sum-in-dataframe-python) – Space Impact Nov 04 '18 at 11:47
  • Just use `np.where(m, df.groupby('item')['demand'].cumsum(), 0)` from previous answer to your question. – Space Impact Nov 04 '18 at 11:48

2 Answers2

0

As the above data doesn't have any proper column which can be used to create groups, we need to create one:

df['grp_attr'] = df['supply'].clip(upper=1)
df['grp_attr'] = df[df['grp_attr'] != 0]['grp_attr'].cumsum()
df['grp_attr'] = df['grp_attr'].bfill().fillna(0).astype(int)

The df looks like this once you create grp_attr:

   item        Date  supply  demand  grp_attr
0     A  2018-01-01       0      10         1
1     A  2018-01-02       0      15         1
2     A  2018-01-03     100      30         1
3     A  2018-01-04       0      10         2
4     A  2018-01-05       0      40         2
5     A  2018-01-06      50      50         2
6     A  2018-01-07       0      10         3
7     B  2018-01-01       0      20         3
8     B  2018-01-02       0      30         3
9     B  2018-01-03      20      60         3
10    B  2018-01-04       0      20         4
11    B  2018-01-05     100      10         4
12    B  2018-01-06       0      20         0
13    B  2018-01-07       0      30         0`

Now we can groupby using grp_attr:

df['new_supply'] = df.groupby('grp_attr')['demand'].cumsum()
df.loc[df['supply'] == 0, 'new_supply'] = 0

Once you are done with your operations you can now drop the grp_attr columns from your dataframe.

df.drop(columns=['grp_attr'], inplace=True)
Mahendra Singh
  • 508
  • 2
  • 9
0

You can change condition in boolean mask and change - to 0 in np.where:

m = df['supply'] != 0

df['new'] = np.where(m, df.groupby('item')['demand'].cumsum(), 0)
print (df)
   item        Date  supply  demand  new
0     A  2018-01-01       0      10    0
1     A  2018-01-02       0      15    0
2     A  2018-01-03     100      30   55
3     A  2018-01-04       0      10    0
4     A  2018-01-05       0      40    0
5     A  2018-01-06      50      50  155
6     A  2018-01-07       0      10    0
7     B  2018-01-01       0      20    0
8     B  2018-01-02       0      30    0
9     B  2018-01-03      20      60  110
10    B  2018-01-04       0      20    0
11    B  2018-01-05     100      10  140
12    B  2018-01-06       0      20    0
13    B  2018-01-07       0      30    0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252