Calculate the number of weekends (Saturdays and Sundays), between two dates

Question

I have a data frame with two date columns, a start and end date. How will I find the number of weekends between the start and end dates using pandas or python date-times

I know that pandas has DatetimeIndex which returns values 0 to 6 for each day of the week, starting Monday

# create a data-frame
import pandas as pd
df = pd.DataFrame({'start_date':['4/5/19','4/5/19','1/5/19','28/4/19'],
                   'end_date': ['4/5/19','5/5/19','4/5/19','5/5/19']})

# convert objects to datetime format
df['start_date'] = pd.to_datetime(df['start_date'], dayfirst=True)
df['end_date'] = pd.to_datetime(df['end_date'], dayfirst=True)

# Trying to get the date index between dates as a prelim step but fails
pd.DatetimeIndex(df['end_date'] - df['start_date']).weekday

I'm expecting the result to be this: (weekend_count includes both start and end dates)

start_date  end_date    weekend_count
4/5/2019    4/5/2019    1
4/5/2019    5/5/2019    2
1/5/2019    4/5/2019    1
28/4/2019   5/5/2019    3

Answer 1

IIUC

df['New']=[pd.date_range(x,y).weekday.isin([5,6]).sum() for x , y in zip(df.start_date,df.end_date)]
df
  start_date   end_date  New
0 2019-05-04 2019-05-04    1
1 2019-05-04 2019-05-05    2
2 2019-05-01 2019-05-04    1
3 2019-04-28 2019-05-05    3