CODEWITHSUNDEEP
pythonexcelpython-3.xpandas
Nukyi
Nukyi

Reputation: 51

Calculating no. of hours for a time interval to determine if it's a weekday or weekend

Situation: Calculating number of hours that lies in a weekday and number of hours that lies in a weekend/holiday from a time interval. Data is extracted from a excel file and put into a dataframe using pandas.

Example of Dataframe Input:
                 Start                      End
      1     06-09-2018 02:00:00      08-09-2018 01:00:00 
      2     08-09-2018 02:00:00      08-09-2018 04:00:00 
      3     08-09-2018 02:00:00      10-09-2018 01:00:00 

Example of Dataframe Output at the end:
                Start                      End                 WD/WE
      1     06-09-2018 02:00:00      08-09-2018 01:00:00        WD
      2     08-09-2018 02:00:00      08-09-2018 04:00:00        WE
      3     08-09-2018 02:00:00      10-09-2018 01:00:00        WE

From 1st row, using the code, we calculate the number of hours that lies on a weekday and a weekend/holiday. Manually calculating, we will get 46 weekday hours and 1 weekend/holiday hours. Thus, weekend/holiday hours <= weekday hours, thus returning a string "WD".

I am currently trying to use workdays, pandas, datetime, openpyxl. 

data_check = pd.read_excel('C:\\excel path.xlsx',header=0)
df_check = pd.DataFrame(data_check,columns=['Start Date and Time','End Date and Time'])
#Creating dataframe
df_check['Start Date and Time'] = pd.to_datetime(df_check['Start Date and Time'])
df_check['End Date and Time'] = pd.to_datetime(df_check['End Date and Time'])

#Simplifying dataframe names
df_s= pd.DataFrame({'Start Date and Time': df_check['Start Date and Time']})
df_u= pd.DataFrame({'End Date and Time': df_check['End Date and Time']})

#Indicating holidays
holidays= ['22-08-2018','06-11-2018','25-12-2018']

I understand my code is a little messy, as I am new to Python and still trying to get things to work. I got stuck after trying to write a code to run through my dataframe to calculate no. of weekday hours and no. of weekend/holiday hours. I have searched and read up on how to detect if a day is weekday or weekend/holiday, but I have no idea on how to run it through my dataframe.

Hopefully someone is able to help.

Tested so far: Code1: Not working. AttributeError: 'DataFrame' object has no attribute 'calendar'. I might be typing the code wrongly.

df_wdwe1 = np.where(df_s.calendar.weekday(year, month, day)>=6, "Weekends","Weekdays")

Upvotes: 1

Views: 381

Answers (1)

Alexandru Martalogu
Alexandru Martalogu

Reputation: 273

Well you could use the datetime library to transform the string into date value then is just a matter of confronting the data to see the min and max, when you get these just make a subtraction and you will get the time elapsed.

You can use min() and max() to get the min and max like in this example:

Find oldest/youngest datetime object in a list

Here is an example on how to use datetime :

Converting string into datetime

For identifying the day of the week based on the date provided you could you calendar lib more specifically the function calendar.weekday(year, month, day) which returns numbers from '0' to '7' where 0 is Monday and 7 is Sunday

As for a holiday I wouldn't know, I don't think there is something as precise as that in calendar, considering holidays are country based. You could check some custom libraries on github.

Upvotes: 1

Related Questions