Count Unique Weekdays From Timestamp Column In Dataframe In Python

I would like to count how many unique weekdays exist in timestamp. Here's an input and I want output to be 4(since 8/5 and 8/6 are weekends). captureTime 0 8/1/2017 0:05 1

Solution 1:

Using np.is_busday:

import numpy as np
import pandas as pd
df = pd.DataFrame( {
    'captureTime':[ '8/1/2017 0:05', '8/2/2017 0:05', '8/3/2017 0:05', 
                    '8/4/2017 0:05', '8/5/2017 0:05', '8/6/2017 0:05']})
df['captureTime'] = pd.to_datetime(df['captureTime'])

print(np.is_busday(df['captureTime'].values.astype('datetime64[D]')).sum())

prints

4

---

Above, all business days are counted once. If you wish to count identical datetimes only once, you could use

np.is_busday(df['captureTime'].unique().astype('datetime64[D]')).sum()

Or, if you wish to remove datetimes that have identical date components, convert to datetime64[D] dtype before calling np.unique:

np.is_busday(np.unique(df['captureTime'].values.astype('datetime64[D]'))).sum()

Solution 2:

One way is pandas series.dt.weekday

df['captureTime'] = pd.to_datetime(df['captureTime'])
np.sum(df['captureTime'].dt.weekday.isin([0,1,2,3,4]))

It returns 4

You can use boolean indexing in case you need to capture the dates

df[df['captureTime'].dt.weekday.isin([0,1,2,3,4])]

    captureTime
0   2017-08-01 00:05:00
1   2017-08-02 00:05:00
2   2017-08-03 00:05:00
3   2017-08-04 00:05:00

Solution 3:

Convert to date time using pd.to_datetime, get the unique dayofweek list, and count all those under 5.

out = (df.captureTime.apply(pd.to_datetime).dt.dayofweek.unique() < 5).sum()
print(out)

4

df.unique removes duplicates, leaving you with a unique array of daysofweek, on which count occurrences under 5 (0 - 4 -> weekdays).

---

Output of df.dayofweek:

out = df.captureTime.apply(pd.to_datetime).dt.dayofweek
print(out)

0    1
1    2
2    3
3    4
4    5
5    6
Name: captureTime, dtype: int64

Solution 4:

Assuming you have captureTime as datetime object you can do this,

s = df['captureTime'].dt.weekday s[s >= 5].count() # 5, 6 corresponds to saturday, sunday