better method to filter out M-F in the hours of 7AM - 5PM from dataset?

user_8372455 0
Viewed 81

I am experimenting with concat and pandas attempting to filter out weekdays Monday thru Friday in the hours of 7AM to 5PM from a data set. So basically the only data left would be weekends all hours and weekday night time hours of 6PM to 6AM.

Make up some data:

import numpy as np
import pandas as pd
np.random.seed(11)

rows,cols = 50000,2
data = np.random.rand(rows,cols) 
tidx = pd.date_range('2019-01-01', periods=rows, freq='H') 

df = pd.DataFrame(data, columns=['Temperature','Value'], index=tidx)

Only thing I can think of is to create 3 copies:

df_weekend = df.copy()
df_weeknights_AM = df.copy()
df_weeknights_PM = df.copy()

filter out monday thru friday to create a weekend dataset of all hours

df_weekend = df_weekend[
    (df_weekend.index.dayofweek > 4)
]

filter out nights and weekend

df_weeknights_AM = df_weeknights_AM[
    (df_weeknights_AM.index.dayofweek < 5)
    &
    (df_weeknights_AM.index.strftime('%H').astype('int') < 7)
]

filter out mornings and weekend

df_weeknights_PM = df_weeknights_PM[
    (df_weeknights_PM.index.dayofweek < 5)
    &
    (df_weeknights_PM.index.strftime('%H').astype('int') > 17)
]

And then attempt to concat all data sets together.. I was experimenting with the merge but not very good luck.

df2 = pd.concat([df_weekend, df_weeknights_AM], axis=1)

df3 = pd.concat([df2, df_weeknights_PM], axis=1)

The problem is the output looks a bit wonky, as I was hoping to not have duplicated columns created but to have everything merged into one dataset based on the index (timestamp) with the same two original columns... What would be the best practice?? When experimenting with merge I was getting something similar with duplicate columns labeled _x, _y...

df3


Temperature Value   Temperature Value   Temperature Value
2019-01-01 00:00:00 NaN NaN 0.180270    0.019475    NaN NaN
2019-01-01 01:00:00 NaN NaN 0.463219    0.724934    NaN NaN
2019-01-01 02:00:00 NaN NaN 0.420204    0.485427    NaN NaN
2019-01-01 03:00:00 NaN NaN 0.012781    0.487372    NaN NaN
2019-01-01 04:00:00 NaN NaN 0.941807    0.850795    NaN NaN
2019-01-01 05:00:00 NaN NaN 0.729964    0.108736    NaN NaN
2019-01-01 06:00:00 NaN NaN 0.893904    0.857154    NaN NaN
2019-01-01 18:00:00 NaN NaN NaN NaN 0.986673    0.338054
2019-01-01 19:00:00 NaN NaN NaN NaN 0.239875    0.796436
2019-01-01 20:00:00 NaN NaN NaN NaN 0.063686    0.364616
2019-01-01 21:00:00 NaN NaN NaN NaN 0.070023    0.319368
2019-01-01 22:00:00 NaN NaN NaN NaN 0.070383    0.290264
2019-01-01 23:00:00 NaN NaN NaN NaN 0.790101    0.905400
2019-01-02 00:00:00 NaN NaN 0.792621    0.561819    NaN NaN
2019-01-02 01:00:00 NaN NaN 0.616018    0.361484    NaN NaN
2019-01-02 02:00:00 NaN NaN 0.168817    0.436241    NaN NaN
2019-01-02 03:00:00 NaN NaN 0.732825    0.062888    NaN NaN
2019-01-02 04:00:00 NaN NaN 0.020733    0.770548    NaN NaN
2019-01-02 05:00:00 NaN NaN 0.299952    0.701164    NaN NaN
2019-01-02 06:00:00 NaN NaN 0.734668    0.932905    NaN NaN
pandas python
3 Answers

You could use DataFrame.between_time to keep only rows between two certain hours and DatetimeIndex.weekday to keep only rows with numerical weeddays smaller than 6 (Saturday) or weekends accordingly and finally concat bot generated dataframes:

df_1 = df.between_time('18:00', '06:00')
out = df_1[(df_1.index.weekday < 6)]
pd.concat([out, df[(df.index.weekday >= 6)]])

                     Temperature     Value
2019-01-01 00:00:00     0.180270  0.019475
2019-01-01 01:00:00     0.463219  0.724934
2019-01-01 02:00:00     0.420204  0.485427
2019-01-01 03:00:00     0.012781  0.487372
2019-01-01 04:00:00     0.941807  0.850795
...                          ...       ...
2024-09-08 19:00:00     0.949657  0.204419
2024-09-08 20:00:00     0.096483  0.823838
2024-09-08 21:00:00     0.890330  0.706198
2024-09-08 22:00:00     0.827802  0.872472
2024-09-08 23:00:00     0.095236  0.703119
user_96986840

Here's another option for filter pandas df based on occupancy or 4AM to hour 20PM Monday through Friday:

df_occ = df.between_time('04:00', '20:00')
df_occ_weekdays = df_occ[df_occ.index.weekday.isin([0,1,2,3,4])]

df_occ_weekdays.index.day_name().value_counts()
user_83724550

Method for unoccupied times to filter data outside of 4AM to hour 20PM:

df_unocc_weekday_nums = df[df.index.weekday.isin([0,1,2,3,4])]

df_unocc_am = df_unocc_weekday_nums.between_time('00:00', '04:00')
df_unocc_pm = df_unocc_weekday_nums.between_time('16:00', '23:59')
df_unocc_weekday = pd.concat([df_unocc_am, df_unocc_pm])

# check to verify days are correct
print(df_unocc_weekday.index.day_name().value_counts())

# check to verify hours are correct, they up unranked
print(df_unocc_weekday.index.hour.value_counts())

df_unocc_weekends = df[df.index.weekday.isin([5,6])]

# check to see if weekends look OK
print(df_unocc_weekends.index.day_name().value_counts())

unocc_all_data = pd.concat([df_unocc_weekday,df_unocc_weekends])
user_83724550
Related