0

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   
Zan
  • 159
  • 1
  • 1
  • 10
  • have you tried https://stackoverflow.com/questions/22132525/add-column-with-number-of-days-between-dates-in-dataframe-pandas – nbk Jul 26 '22 at 17:05
  • How do I use SQL round in pandas? – Zan Jul 26 '22 at 17:32
  • 1
    panda has abifg documantation so https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.round.html – nbk Jul 26 '22 at 18:09

0 Answers0