How to split the days between 2 dates in the months

user_3604071 0
Viewed 407

I have a pandas DataFrame. It contains a column 'start_date' and a column 'end_date'. I would like to calculate the working days between the start and the end, split them into months and add them in the dataframe as additional columns.

For the moment this is what I coded. Is there anyway I can make this faster?

data = pd.DataFrame([
    {'start_date': '2020-03-03', 'end_date' : '2020-06-18'},
    {'start_date': '2020-06-03', 'end_date' : '2020-09-18'},
])


def days_month(df):
    days_month = pd.DatetimeIndex(pd.bdate_range(df['start_date'], df['end_date'])).month.value_counts()
    for i in range(1, 13):
        try:
            days_month[i] 
        except:
            days_month[i] = 0

    return days_month

data[['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']] = data.apply(days_month, axis=1,  result_type="expand")

print(data)
    end_date  start_date  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  \
0  2020-06-18  2020-03-03    0    0   21   22   21   14    0    0    0    0   
1  2020-09-18  2020-06-03    0    0    0    0    0   20   23   21   14    0   

   Nov  Dec  
0    0    0  
1    0    0

Update trying to implement Ethan suggestion:


def countWeekDays(df):  

    fromDate=df['PO Creation Date']
    toDate=df['PO Expected Delivery Date']

    d = np.arange(fromDate, toDate, dtype=np.datetime64)

    weekdays = d[np.is_busday(d, busdaycal=calendar())] 

    workDays = {m: np.array([i for i in weekdays if i.item().month==m]).size for m in range(1,13)}

    return workDays

def calendar(): 
    #set work week mask and optional holidays array
    return np.busdaycalendar(weekmask='1111100', holidays=['2020-01-01','2020-01-20','2020-02-17','2020-05-25','2020-07-03','2020-09-07','2020-10-12','2020-11-11','2020-11-26','2020-12-25'])


data[['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']] = data.apply(countWeekDays, axis=1,  result_type="expand")

However, it is giving me a memory error. Not sure why...

pandas python
1 Answers

You can use numpy datetime functions and some wrapping:

import numpy as np

def countWeekDays(fromDate='2020-03-03', toDate='2020-06-03'):  
    d = np.arange(fromDate, toDate, dtype=np.datetime64)

    weekdays = d[np.is_busday(d, busdaycal=calendar())] 

    workDays = [(m, np.array([i for i in weekdays if i.item().month==m]).size) for m in range(1,13)]

    return workDays

def calendar(): 
    #set work week mask and optional holidays array
    return np.busdaycalendar(weekmask='1111100', holidays=['2020-01-01','2020-01-20','2020-02-17','2020-05-25','2020-07-03','2020-09-07','2020-10-12','2020-11-11','2020-11-26','2020-12-25'])

Results:

>>> countWeekDays()
[(1, 0), (2, 0), (3, 21), (4, 22), (5, 20), (6, 2), (7, 0), (8, 0), (9, 0), (10, 0), (11, 0), (12, 0)]

Here's a modification to your code to build a data frame that works with my function to get the weekdays. I think the error you were getting was due to the way the Dataframe was being built and modified. My experience has been that dataframe are kind of a pain to modify, it's better to get your data together and then create the Dataframe from your complete data set:

def applyDays():
    data = [{'start_date': '2020-03-03', 'end_date' : '2020-06-18'},
    {'start_date': '2020-06-03', 'end_date' : '2020-09-18'}]
    return countWeekDays(data)


def countWeekDays(lst): #, result_type):  
    months = ['start_date','end_date',  'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
    data = []
    for row in lst:  
        fromDate = row['start_date'] # df['PO Creation Date']
        toDate = row['end_date'] #df['PO Expected Delivery Date']    
        d = np.arange(fromDate, toDate, dtype=np.datetime64)    
        weekdays = d[np.is_busday(d, busdaycal=calendar())] 

        data.append([fromDate, toDate] + [np.array([i for i in weekdays if i.item().month==m]).size for m in range(1,13)])
    return pd.DataFrame(data, columns=months)

Dataframe results:

applyDays()
Out[6]: 
   start_date    end_date  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  \
0  2020-03-03  2020-06-18    0    0   21   22   20   13    0    0    0    0   
1  2020-06-03  2020-09-18    0    0    0    0    0   20   22   21   12    0   

   Nov  Dec  
0    0    0  
1    0    0
user_106423640
Related