Perform a specific calculation in a dataframe based on number of days in each month

Question

I have dataframe as shown below. Which consist of customers last 3 months number of transactions.

id     age_days    n_month3     n_month2     n_month1
1      201         60           15           30            
2      800         0            15           5            
3      800         0            0            10           
4      100         0            0            0             
5      600         0            6            5            
6      800         0            0            15           
7      500         10           10           30            
8      200         0            0            0             
9      500         0            0            0

From the above I would like to derive a column called recency as shown in the explanations

Explanation:

month3 is the current month

m3 = number of days of current month m2 = number of days of previous month m1 = number of days of the month of 2 months back from current month

if df["n_month3"] != 0:
    recency = m3 / df["n_month3"]
elif df["n_month2"] != 0:
    recency = m3 + (m2 / df["n_month2"])
elif df["n_month1"] != 0:
    recency = m3 + m2 + (m1 / df["n_month1"])
else:
    if df["age_days"] <= (m3 + m2 + m1):
        recency = df["age_days"]
    else:
        recency = (m3 + m2 + m1) + 1

Expected output:

Let say current month is April, then

m3 = 30 m2 = 31 m1 = 28

id     age_days    n_month3     n_month2     n_month1      recency
1      201         60           15           30            (m3/60) = 30/60 = 0.5
2      800         0            15           5             m3 + (m2/15) = 30 + 31/15 = 32   
3      800         0            0            10            m3 + m2 + m1/10 = 30 + 31 + 28/10         
4      100         0            0            0             m3+m2+m1+1 = 90    
5      600         0            6            5             m3 + (m2/6) = 30 + 31/6      
6      800         0            0            15            m3 + m2 + m1/15 = 30 + 31 + 28/15
7      500         10           10           30            (m3/10) = 30/10 = 3  
8      10          0            0            0             10(age_days)
9      500         0            0            0             m3+m2+m1+1 = 90

     

I am facing issue with dynamically defining m3, m2 and m1 based on the current month.

Answer 1

Here is one way to do it (as of this answer, current month is April 2022):

from calendar import monthrange
from datetime import datetime

m3, m2, m1 = (
    monthrange(datetime.now().year, datetime.now().month - i)[1] for i in range(0, 3)
)

print(m3)  # 30 [days in April]
print(m2)  # 31 [days in March]
print(m1)  # 28 [days in February]

If you need to extend to 12 months, in order to deal with years cut-off, you can do this:

current_year = datetime.now().year
current_month = datetime.now().month

m12, m11, m10, m9, m8, m7, m6, m5, m4, m3, m2, m1 = [
    monthrange(current_year, current_month - i)[1] for i in range(0, current_month)
] + [monthrange(current_year - 1, 12 - i)[1] for i in range(0, 12 - current_month)]

print(m5)  # 30 [days in September 2021]