Pandas groupby time depending on day of week

Question

I've got a df which contains several columns like DateTime, "ToConsumer" and "FromGenerator".

I would like to sum up all values where FromGen > ToCons in a certain timerange depending on the day of week. On sat-sun all values and on weekdays just the values between 17:00 and 07:00 should be summed up. Therefore I've created the column DayOfWeek yet so far but haven't mananged to find out how to groupby depending those policies?

Many thanks in advance!

EDIT: I created the column "over" now and deleted all negative values by:

df= df[df.select_dtypes(include=[np.number]).ge(0).all(1)]

Now my df looks like:

df.dtypes
df.dtypes
DateTime          datetime64[ns, Europe/Berlin]
DevWork                                 float64
FromBatToCons                           float64
FromGenToBat                            float64
FromGenToCons                           float64
FromGenToGrid                           float64
FromGridToCons                          float64
FromGrid                                float64
StateOfCharge                           float64
ToCons                                  float64
FromGen                                 float64
DayOfWeek                                 int64
over                                    float64

So now I "just" have to sum those values- my idea was to split the data into two subsets for weekend and workday and then eliminate all times which I don't need:

df_weekend = df[df["DayOfWeek"] > 4]
df_work = df[df["DayOfWeek"] < 5]

df_weekend = df_weekend.loc[(df_weekend['DateTime'] <= '7:00:00'
                    & df_weekend['DateTime'] >= '17:00:00')]

Unfortunately this doesn't work as I expected: unsupported operand type(s) for &: 'str' and 'DatetimeArray'

Unfortunately this doesn't work as I expected...

Answer 1

i managed to solve my problem by using

df_so.between_time