Cumulative sum with addition or subtraction depending on the condition in python

user_9637017 0
Viewed 114

In the df dataframe in the example, I want to perform cumulative sum based on the transaction type, on a per-customer basis.

If the transaction type is buy, the count column will be sum; If it is a sell, the difference will be taken by multiplying the previous value by the value in the percentage column.

In summary, while the count will be sum in the buy transaction, the percentage value of the accumulated count in the sell transaction will be sold as in the percentage column.

Thus, the final_df dataframe will be reached.

  • Here is the example dataframe:
date = ['2022-01-04', '2022-01-04', '2022-01-04', '2022-01-04',
        '2022-01-08', '2022-01-09', '2022-01-09', '2022-01-10']
customer = [1, 1, 1, 1, 1, 1, 1, 1]
trading = ['buy', 'buy', 'buy', 'sell', 'sell', 'sell', 'buy', 'sell']
count = [1200, 1200, 2100, 0, 0, 0, 70, 0]
percentage = [0.0, 0.0, 0.0, 0.1, 0.2, 0.1, 0.0, 0.5]
process = ['1200',
           '1200 + 1200 = 2400',
           '2400 + 2100 = 4500',
           '4500 - 4500*0.1 = 4050',
           '4050 - 4050*0.2 = 3240',
           '3240 - 3240*0.1 = 2916',
           '2916 + 70 = 2986',
           '2986 - 2986*0.5 = 1493']
value = [1200, 2400, 4500, 4050, 3240, 2916, 2986, 1493]

data = {'date':date,'customer':customer,'trading':trading,
        'count':count,'percentage':percentage,
        'process':process,'value':value}
df = pd.DataFrame(data)
df

| date       | customer | trading | count | percentage | process                | value|
|------------|----------|---------|-------|------------|------------------------|------|
| 2022-01-04 | 1        | buy     | 1200  | 0.0        | 1200                   | 1200 |
| 2022-01-04 | 1        | buy     | 1200  | 0.0        | 1200 + 1200 = 2400     | 2400 |
| 2022-01-04 | 1        | buy     | 2100  | 0.0        | 2400 + 2100 = 4500     | 4500 |
| 2022-01-04 | 1        | sell    | 0     | 0.1        | 4500 - 4500*0.1 = 4050 | 4050 |
| 2022-01-08 | 1        | sell    | 0     | 0.2        | 4050 - 4050*0.2 = 3240 | 3240 |
| 2022-01-09 | 1        | sell    | 0     | 0.1        | 3240 - 3240*0.1 = 2916 | 2916 |
| 2022-01-09 | 1        | buy     | 70    | 0.0        | 2916 + 70 = 2986       | 2986 |
| 2022-01-10 | 1        | sell    | 0     | 0.5        | 2986 - 2986*0.5 = 1493 | 1493 |

final_df = pd.DataFrame(df.iloc[-1:][['date','customer','value']])
final_df

| date       | customer | value |
|------------|----------|-------|
| 2022-01-10 | 1        | 1493  |
  • Here are the methods I've tried:
# Created next and previous transaction
df['t_prev'] = df.trading.shift(1)
df['t_next'] = df.trading.shift(-1)

# combining current-previous-next transaction options
# 2^3 combination(bbb,bbs,bsb....sss)
choi = [
    #1
    ((df.trading=='buy')&(df.tradingp=='buy')&(df.tradingn=='buy')),
    
    #2
    (((df.trading=='buy')&(df.tradingp=='buy')&(df.tradingn=='sell'))),
    
    #3
    (((df.trading=='buy')&(df.tradingp=='sell')&(df.tradingn=='buy'))),
    
    #4
    (((df.trading=='buy')&(df.tradingp=='sell')&(df.tradingn=='sell'))),
    
    #5
    ((df.trading=='sell')&(df.tradingp=='buy')&(df.tradingn=='buy')),
    
    #6
    (((df.trading=='sell')&(df.tradingp=='buy')&(df.tradingn=='sell'))),
    
    #7
    (((df.trading=='sell')&(df.tradingp=='sell')&(df.tradingn=='buy'))),
    
    #8
    (((df.trading=='sell')&(df.tradingp=='sell')&(df.tradingn=='sell')))
        ]

cond = [
    #1
    df.groupby(['customer'])['count'].cumsum(),
    
    #2
    df.groupby(['customer'])['count'].cumsum(),
    
    #3
    df.groupby(['customer'])['count'].cumsum(),
    
    #4
    df.groupby(['customer'])['count'].cumsum(),
    
    #5
    (
         df.groupby(['customer'])['count'].cumsum().shift(1)\
         - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
    ),
    
    #6
    (
         df.groupby(['customer'])['count'].cumsum().shift(1)\
         - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
    ),
    
    #7
    (
         (
             df.groupby(['customer'])['count'].cumsum().shift(1)\
             - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
         )\    

        -(
             df.groupby(['customer'])['count'].cumsum().shift(1)\
             - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
         )*df['percentage']
    ),
    
    #8
    (
        (
            df.groupby(['customer'])['count'].cumsum().shift(1)\
            - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
        )\
        
        -(
            df.groupby(['customer'])['count'].cumsum().shift(1)\
            - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
        )*df['percentage']
    )
        ]

df['value'] = np.select(choi,cond,df.groupby(['customer'])['count'].cumsum())
final_df = pd.DataFrame(df.iloc[-1:][['date','customer','value']])

I would be glad if you can help. Thanks in advance

pandas numpy python
1 Answers

I solved but it's not a feasible solution for me. I have lots of customer and trading rows. So I need pandas or numpy solution for speed.

Here is my code:

di = {'buy':23, 'sell':24}
df = df.replace({'trading': di})

df['t_prev'] = df.trading.shift(1).fillna(0)

top = 0
for c in df.values:
    
    if ((c[2] == 23)&(c[7] <= 23)):#c[2] trading, c[7] t_prev columns
        top = top + c[3]#count column
    elif ((c[2] == 23)&(c[7] > 23)):
        top = top + c[3]#count column
    else:
        top = top - top*c[4]#percentage column
    print(top)
user_96370170
Related