Python Forum
Conditional Cumsum in pandas data-frame
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Conditional Cumsum in pandas data-frame
#1
I'm trying to get a cumulative total if the code is equal to 100.

df = pd.DataFrame(data = {'Code':[100,100,200,300,100],
                             'Value':[1000,1000,500,750,1000],
                             'Expected_Cum_100':[1000,2000,2000,2000,3000],})
This was my try, but I couldn't get the expected result

c = df["Value"].cumsum()
df['Cum_100'] = c.mask(df["Code"] != 100).ffill()
Output:
df Out[81]: Code Expected_Cum_100 Value Cum_100 0 100 1000 1000 1000.0 1 100 2000 1000 2000.0 2 200 2000 500 2000.0 3 300 2000 750 2000.0 4 100 3000 1000 4250.0
Appreciate it if anyone can suggest a way to get the expected cumulative total.
Reply
#2
I managed to do this by creating an intermediary column as a new value.

df['New_Value'] = df.apply(lambda x : x['Value'] if (x['Code'] == 100) else np.nan, axis=1)
c = df['New_Value'].cumsum()
df['Cum_100'] = c.mask(df["Code"] != 100).ffill()
del ['New_Value']
Output:
print(df) Code Expected_Cum_100 Value New_Value Cum_100 0 100 1000 1000 1000.0 1000.0 1 100 2000 1000 1000.0 2000.0 2 200 2000 500 NaN 2000.0 3 300 2000 750 NaN 2000.0 4 100 3000 1000 1000.0 3000.0
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Better python library to create ER Diagram by using pandas data frames as tables klllmmm 0 1,127 Oct-19-2023, 01:01 PM
Last Post: klllmmm
  Using pyodbc&pandas to load a Table data to df tester_V 3 820 Sep-09-2023, 08:55 PM
Last Post: tester_V
  how do you style data frame that has empty rows. gsaray101 0 531 Sep-08-2023, 05:20 PM
Last Post: gsaray101
  pandas : problem with conditional filling of a column Xigris 2 632 Jul-22-2023, 11:44 AM
Last Post: Xigris
  googletrans library to translate text language for using data frame is not running gcozba2023 0 1,227 Mar-06-2023, 09:50 AM
Last Post: gcozba2023
  Load multiple Jason data in one Data Frame vijays3 6 1,550 Aug-12-2022, 05:17 PM
Last Post: vijays3
  conditionals based on data frame mbrown009 1 899 Aug-12-2022, 08:18 AM
Last Post: Larz60+
  Merging two Data Frame on a special case piku9290dgp 0 1,089 Mar-02-2022, 10:43 AM
Last Post: piku9290dgp
  Save data frame to .csv df.to.csv() mcva 1 1,534 Feb-03-2022, 07:05 PM
Last Post: mcva
  Move a particular row in pandas data frame to last row klllmmm 0 3,767 Dec-27-2021, 09:11 AM
Last Post: klllmmm

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020