python - 在早上 7 点到下午 5 点从数据集中过滤掉 M-F 的更好方法？

Question

我正在尝试使用 concat 和 pandas 尝试从数据集中过滤掉周一至周五上午 7 点至下午 5 点的工作日。所以基本上剩下的唯一数据是周末所有时间和工作日晚上 6 点到早上 6 点。

整理一些数据:

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)

我唯一能想到的就是创建 3 个副本:

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

过滤掉星期一到星期五以创建所有时间的周末数据集

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

过滤掉晚上和周末

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

过滤掉早上和周末

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

然后尝试将所有数据集连接在一起。我正在试验 merge 但运气不是很好。

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

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

问题是输出看起来有点不稳定，因为我希望不要创建重复的列，而是希望将所有内容合并到一个数据集中，该数据集基于索引(时间戳)和相同的两个原始列......会是什么最佳实践？？在尝试合并时，我得到了与标记为 _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

Answer 1

你可以使用 DataFrame.between_time只保留两个特定时间和 DatetimeIndex.weekday 之间的行仅保留 numerical weeddays 小于 6(星期六)或相应周末的行，最后 concat 机器人生成的数据帧:

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