At the moment I was refactoring a SQL code for Python, I'm using pandas. The Database is Access, I found a code a little difficult to understand the logic. My goal is to get the Final Output, as in the example:
CALC GRACE IN EXCEL - EXAMPLE FORMULA: =IF(STATUS="Active";ABS((MONTH(Dat_Crom)+(YEAR(Dat_Crom)*12))-(MONTH( Last_Date)+(YEAR( Last_Date)*12))))
Calc in Inative = SQL's LastDate Business Day() function takes the last working day of the month?
My SQL code and the Output generated in excel with objective- Fictitious Data:
TABLE:
Last_Date Status Dat_Crom
0 2022-06-05 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00
1 2022-06-07 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00
2 2022-06-07 00:00:00+00:00 Inative 2022-04-04 00:00:00+00:00
QUERY SQL - MS ACCESS:
Round(( [last_date] - ( Cdate(Nz(Iif([status] = 'Active',
[date_crom],
LastDateBusinessDay()), 0))
) ) /
30, 0) AS GRACE,
SQL OUTPUT
COLUMN GRACE
2
2
4
In my Python code I'm trying to replicate this but it's going wrong. After doing the calculation, I can't use pandas' Round() method. Is it the same as Microsoft Access SQL?
There is a problem that I am not able to convert the date of the column "GRACE" in python, dtype is coming as timedelta.How do I apply SQL's Round() in Python in column GRACE?
Python Code Example
import pandas as pd
LastDateBusinessDay = '2022-06-30T00:00:00.000Z'
# To make it easier with the fictitious data I took the last business day manually.
df = pd.DataFrame({'Last_Date':['2022-06-05T00:00:00.000Z','2022-06-07T00:00:00.000Z','2022-08-08T00:00:00.000Z' ],
'Status':['Active','Active','Inative' ],
'Dat_Crom':['2022-04-04T00:00:00.000Z','2022-04-04T00:00:00.000Z','2022-04-04T00:00:00.000Z' ]
})
#Converting to datetime - IGNORE
LastDateBusinessDay = pd.to_datetime(LastDateBusinessDay)
df['Last_Date'] = pd.to_datetime(df['Last_Date'])
df['Dat_Crom'] = pd.to_datetime(df['Dat_Crom'])
# Calc Dates
df.loc[df['Status']== 'Active', 'GRACE'] = df['Last_Date']-df['Dat_Crom']
df.loc[df['Status']!= 'Active', 'GRACE'] = df['Last_Date']-LastDateBusinessDay
OUTPUT CODE PYTHON ACTUALLY: How do I apply SQL's Round() in Python?
Last_Date Status Dat_Crom GRACE
0 2022-06-05 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00 P62DT0H0M0S
1 2022-06-07 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00 P64DT0H0M0S
2 2022-08-08 00:00:00+00:00 Inative 2022-04-04 00:00:00+00:00 P39DT0H0M0S
OUTPUT EXPECTED PYTHON:
Last_Date Status Dat_Crom GRACE
0 2022-06-05 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00 2
1 2022-06-07 00:00:00+00:00 Active 2022-04-04 00:00:00+00:00 2
2 2022-08-08 00:00:00+00:00 Inative 2022-04-04 00:00:00+00:00 4