1

I have 2 columns with timestamp on them, I need the difference between them in seconds in a 3rd column , excluding weekends. How am I supposed to do this in Python/pandas? I want it to exclude Saturday/Sunday from the timeline. Ex - 1 . Starts at Thursday/Friday and ends at Monday/Tuesday - Calculate duration only for the time it lied between Thursday/Friday and then directly Monday/Tuesday. 2 . If it starts on Saturday and ends on Monday - Calculate only for Monday. 3 . If ex.Starts on Friday and ends on Sunday, Calculate only for Friday. 4 . If starts and ends on Saturday and Sunday - result is 0 seconds

Mahesh m bathija
  • 9
  • 2
  • See if this link helps https://stackoverflow.com/questions/54797891/business-hours-between-two-series-of-timestamps-excluding-weekends-and-holidays – moys Jan 17 '20 at 03:10
  • I checked this, but it calculates in hours, so even a 4 second difference is rounded to 1hour here. Also this excludes holidays. I just need it to exclude weekends. – Mahesh m bathija Jan 17 '20 at 03:16

1 Answers1

0

First, Convert the timestamp in both the columns to DateTime if not it's already in this format.

Second, find if the day is a weekend or a weekday and use total_seconds method to find the diff in seconds in the following way: 

def find_diff_in_secs(row):
    day_of_week = row["start"].weekday()

    if day_of_week<5:
        #          Find the diff in secs
        diff_in_secs =(row["end"]-row["start"]).total_seconds()
        return diff_in_secs
    else:
        return "NA"

df.apply(find_diff_in_secs)
Pralay Ramteke
  • 21
  • 4
  • thank you, but what if the start is Friday/thursday and end is Monday/tuesday. I will need it to calculate only working seconds and exclude saturday and sunday. Also if it starts on Saturday and ends on Monday, i still need it to calculate only the Monday seconds. If it stars on Friday and ends on Sunday too. I only need it to calculate for Friday – Mahesh m bathija Jan 20 '20 at 04:01
  • What is the start time and end time of a weekday? – Pralay Ramteke Jan 20 '20 at 09:37
  • it starts at 00:00:00 Monday to 23:59:59 friday – Mahesh m bathija Jan 20 '20 at 09:39