CODEWITHSUNDEEP
pandasfunction
user14784453
user14784453

Reputation:

get the sign change count from Dataframe

I have df like this

     Date            amount
 0  2021-06-18          14
 1  2021-06-19          -8
 2  2021-06-20          -8
 3  2021-06-21          17
 4  2021-07-02          -8
 5  2021-07-05          77            
 6  2021-07-06          -10
 7  2021-08-02          -78
 8  2021-08-06           77            
 9  2021-07-08          10

i went the count of sign change in amount month wise of count each month like in

count =  [{"June-2021": 2},{"July-2021" : 3},{"Aug-2021" : 1}]

Note: Last Date of each month and first date of next month is different then count as  in different count

i want a function for this

Upvotes: 0

Views: 199

Answers (1)

SeaBean
SeaBean

Reputation: 23217

You can use (x.mul(x.shift()) < 0).sum() (current entry multiply by last entry being negative indicates a sign change) to get the count of sign changes within a group of month-year, as follows:

count = (df.groupby(df['Date'].dt.strftime('%b-%Y'), sort=False)['amount']
           .agg(lambda x: (x.mul(x.shift()) < 0).sum())
           .to_dict()
        )

Result:

print(count)

{'Jun-2021': 2, 'Jul-2021': 3, 'Aug-2021': 1}

Edit

If you want list of dict, you can use:

count = (df.groupby(df['Date'].dt.strftime('%b-%Y'), sort=False)['amount']
           .agg(lambda x: (x.mul(x.shift()) < 0).sum())
           .reset_index()
           .apply(lambda x: {x['Date']: x['amount']}, axis=1)
           .to_list()
        )

Result:

print(count)

[{'Jun-2021': 2}, {'Jul-2021': 3}, {'Aug-2021': 1}]

Upvotes: 1

Related Questions