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

Issue

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.

Solution

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]

Answered By - Laurent