Python Business Days within loop

Question

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'])

why do you loop? just do `df_Temp['Maturity(Stlm -Report Date)'] = df_Temp['Today'] - df_Temp['STLMT_DTE2']` this will work on every row? — Umar.H, Dec 12 '19 at 15:08
Welcome to StackOverflow. Please read [How to ask](https://stackoverflow.com/help/how-to-ask), and post a [Minimal, Complete, Verifiable Example](https://stackoverflow.com/help/mcve). Also, instead of saying "it does not work", tell us exactly what happens when you use np.busdays_cout. But definitely post an MVCE. It's the best way to get quick help here! — Jeff Learman, Dec 12 '19 at 15:39
Hi Datanovice, that works fine, its the fact that i am trying to count only business days, however using np.busdays_count only works when not in a series.So im trying to find a way that can count business days between df_Temp['Today'] and df_Temp['STLMT_DTE2']. where there is multiple rows. hope that makes it clearer — KieranC97, Dec 12 '19 at 15:57
Don't worry about the downvotes - but please understand that it's hard to anyone to conceptualise your error without a sample amount of data and your expected output(must be text) have a read of Jeff's links and then re-edit your question. once you do i'll give it my best shot. — Umar.H, Dec 12 '19 at 16:11
You need to format both dates properly, and then this should do the trick: https://stackoverflow.com/a/59272172/11610186 — Grzegorz Skibinski, Dec 12 '19 at 16:24
Thansk Grzegorz, could you suggest how to format the dates? as i think i may be going wrong here — KieranC97, Dec 12 '19 at 16:44
That's a big improvement, posting the code. In the future, though, please make the code sample **complete**, meaning someone can copy paste it into a file and run it and it'll show the issue. For example see @KRKirov 's answer below. — Jeff Learman, Dec 13 '19 at 15:22

score 3 · Accepted Answer · answered Dec 12 '19 at 17:34

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

Python Business Days within loop

1 Answers1