How To Create Cummulative Sum In Dataframe Python Part2?

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 1

Solution 1:

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

m=df['supply']!=0df['new']=np.where(m,df.groupby('item')['demand'].cumsum(),0)print(df)itemDatesupplydemandnew0A2018-01-01       01001A2018-01-02       01502A2018-01-03     10030553A2018-01-04       01004A2018-01-05       04005A2018-01-06      50501556A2018-01-07       01007B2018-01-01       02008B2018-01-02       03009B2018-01-03      206011010B2018-01-04       020011B2018-01-05     1001014012B2018-01-06       020013B2018-01-07       0300

Solution 2:

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
0A2018-01-0101011A2018-01-0201512A2018-01-031003013A2018-01-0401024A2018-01-0504025A2018-01-06505026A2018-01-0701037B2018-01-0102038B2018-01-0203039B2018-01-032060310B2018-01-04020411B2018-01-0510010412B2018-01-06020013B2018-01-070300`

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)