CODEWITHSUNDEEP
pythonpandasdatetimeweekend
KieranC97
KieranC97

Reputation: 47

Python Business Days within loop

I am trying to loop through a dataframe, the dataframe has two columns, both with datetime variables inside. I am trying to loop through this data base and produce a new column with the count of business days between the two dates.I have tried using np.busdays_count < this returned errors like the following.

df_Temp['Maturity(Stlm -Report Date)'] = np.busday_count(df_Temp['Today'],df_Temp['STLMT_DTE2'])


Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "<__array_function__ internals>", line 6, in busday_count
TypeError: Iterator operand 0 dtype could not be cast from dtype('<M8[ns]') to dtype('<M8[D]') according to the rule 'safe'

i have also tried using the following function :

import datetime

def working_days(start_dt,end_dt):
    num_days = (end_dt -start_dt).days +1
    num_weeks =(num_days)//7
    a=0
    #condition 1
    if end_dt.strftime('%a')=='Sat':
        if start_dt.strftime('%a') != 'Sun':
            a= 1
    #condition 2
    if start_dt.strftime('%a')=='Sun':
        if end_dt.strftime('%a') !='Sat':
            a =1
    #condition 3
    if end_dt.strftime('%a')=='Sun':
        if start_dt.strftime('%a') not in ('Mon','Sun'):
            a =2
    #condition 4        
    if start_dt.weekday() not in (0,6):
        if (start_dt.weekday() -end_dt.weekday()) >=2:
            a =2
    working_days =num_days -(num_weeks*2)-a

    return working_days

please can you suggest another method to use or an adaptation to the working days function that will allow this to work, so far i have the below code.I hope i covered this in enough detail.

for ns in (NettingSets):

    df_Temp = dfNetY[dfNetY['ACCT_NUM'] == ns]
    df_Temp['Current Credit Exposure'] = np.where(df_Temp['All NPV Flags']==1,0,df_Temp['MTM_AMT'])
    df_Temp['Positive Current Credit Exposure'] = np.where(df_Temp['Current Credit Exposure'] > 0,df_Temp['Current Credit Exposure'],0)
    df_Temp['SupervisoryFactor'] = 0.04
    df_Temp['STLMT_DTE2'] = pd.to_datetime(df_Temp['STLMT_DTE2'].astype(str), format='%Y-%m-%d')
    df_Temp['Today'] = date1
    df_Temp['Today'] = pd.to_datetime(df_Temp['Today'].astype(str), format='%Y-%m-%d')

for rows in df_Temp:
    df_Temp['Maturity(Stlm -Report Date)'] = np.busday_count(df_Temp['Today'],df_Temp['STLMT_DTE2'])

Upvotes: 0

Views: 473

Answers (1)

KRKirov
KRKirov

Reputation: 4004

For np.busday_count to work both dates need to be cast in the 'M8[D]' format. 

import datetime
import pandas as pd
import numpy as np

# Create a toy data frame
dates_1 = pd.date_range(datetime.datetime(2018, 4, 5, 0, 
                      0), datetime.datetime(2018, 4, 20, 7, 0),freq='D')

dates_2 = pd.date_range(datetime.datetime(2019, 4, 5, 0, 
                      0), datetime.datetime(2019, 4, 20, 7, 0),freq='D')

df_Temp = pd.DataFrame({'STLMT_DTE2': dates_1, 'Today': dates_2})
df_Temp.head()

df_Temp['Maturity(Stlm-Report Date)'] = np.abs(
        np.busday_count(df_Temp['Today'].values.astype('M8[D]'),
                        df_Temp['STLMT_DTE2'].values.astype('M8[D]')))

df_Temp.head()

Output:

df_Temp.head()
Out[16]: # Before calculating business days
  STLMT_DTE2      Today
0 2018-04-05 2019-04-05
1 2018-04-06 2019-04-06
2 2018-04-07 2019-04-07
3 2018-04-08 2019-04-08
4 2018-04-09 2019-04-09

df_Temp.head()
Out[17]: # After calculating business days
  STLMT_DTE2      Today  Maturity(Stlm-Report Date)
0 2018-04-05 2019-04-05                         261
1 2018-04-06 2019-04-06                         261
2 2018-04-07 2019-04-07                         260
3 2018-04-08 2019-04-08                         260
4 2018-04-09 2019-04-09                         261

Upvotes: 3

Related Questions