cumsum reset based on new customer number

I have a dataset that looks like this. enter image description here

What I am trying to do is the following

  • For each customer number, sum credit amount, sum debit amounts(oldest to newest) until debit cumsum = credit amount *-1
  • Credits can apply to partial invoices
  • Return a list of document numbers (and amounts) to apply credits to for each customer

df['debcum_sum'] = df.groupby(['CustNum'])['Debits'].apply(lambda x: x.cumsum())

I was going to add a column for the cumsum but I would like to try and to this as a for loop. Any suggestions?

1 answer

  • answered 2018-01-11 20:16 coldspeed

    Use a combination of groupby + cumsum, and then pd.Series.where/mask to hide values based on the Credit column -

    v = df.groupby(['CustNum'])['Debits'].cumsum()
    df['debit_cumsum'] = v.where(v >= df['Credit'] * -1)