How to count frequency last month in python?

Question

i have a dataframe like this :

customer_id	trx_date	trx_amount	last_trx
CS5295	2013-02-11	35	2014-11-25
CS5295	2014-11-25	54	2014-11-25
CS5295	2013-05-22	36	2014-11-25
CS5295	2012-12-24	36	2014-11-25
CS5295	2013-03-22	43	2014-11-25
CS5295	2013-02-11	25	2014-11-25

how can i count the trx_amount, from last month in last_trx, and customer_id as a parameter?

Expected Output :

customer_id	trx_times
CS5295	1

from the last month of the date in last_trx, 2014-11-25

counting how many times did customer_id make a trx from 2014-10-25 to 2014-11-25

How does that make sense? sum(trx_amount) is 229, count(trx_amount) is 6. Where do you get 12? — Tim Roberts, Mar 15 '21 at 05:24
that's just some example, but that should be 1 because last month from the last_trx only did 1 trx, from 2014 - 10 - 25 to 2014 - 11 - 25 — Prima, Mar 15 '21 at 05:37

score 2 · Accepted Answer · answered Mar 15 '21 at 06:10

For simplicity I will call the DataFrame transact_df and assume it is a pandas dataframe. Additionally, the "last_trx" column is assumed be already be in the dataframe.

Convert the columns to DateTime if not already done: Convert Pandas Column to DateTime
Identify all rows that qualify: How would I compute exactly 30 days into the past with Python (down to the minute)?

valid_transacts_df = transact_df.loc[(transact_df['trans_date'] 
                                     > transact_df['last_trx'] - datetime.timedelta(30))]

Count/sum the values you want:

# Number of transactions in the last 30 days
trx_times = len(valid_transacts_df)

# Sum of transactions in the last 30 days
total_amount = sum(valid_transacts_df['trx_amount'])

If necessary, you can combine steps 2 & 3 to shorten your code.

Thanks!, not the exact result what im looking for, but definitely help alot. with a little change it works perfectly — Prima, Mar 16 '21 at 15:28

How to count frequency last month in python?

1 Answers1