pandas: boolean selecting rows by index (DatetimeIndex)

Question

I have created a DataFrame for keeping energy logger data. Right now this DataFrame is only storing some random numbers. As index I use a pandas.core.indexes.datetimes.DatetimeIndex. With the weekday attribute I can figure out the corresponding weekday (0 for monday, 1 for tuesday and so on...).

I don't expect there to be any energy consumption on weekends. My correspondign code looks about:

# weekday > 4 => saturday and sunday
df.loc[df.index.weekday > 4, 'power'] = 0

This works fine. But let's say, there is no consumption on wednesday and thursday. I would expect the corresponding code to look like:

df.loc[(df.index.weekday == 2 or df.index.weekday == 3), 'power'] = 0

This doesn't work. I get the following error:

ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

Ok, pandas hints me at these methods any and all. But as far as I understand, they don't do what I need - or at least I don't know how to use them for my needs.

QUESTION:

Does anybody know, how to get such a DataFrame slice determined by some boolean condition?

Many thanks in advance!

P.S.:

I have figured out a solution - but it's very uncomfortable:

df.loc[df.index.weekday == 2, 'power'] = 0
df.loc[df.index.weekday == 3, 'power'] = 0

Just imagine, I want to do the same thing for a couple of hours or minutes instead of days. There has to be an easier way to do this.

BenB · Accepted Answer · 2020-11-23 20:19:15Z

1

Combinations of conditions in these cases have to be joined by & (AND) or | (OR) and the single conditions have to be put in parentheses.

df.loc[(df.index.weekday == 2) | (df.index.weekday == 3), 'power'] = 0

should work

Edit based on comment: This can be extended to more days by using the isin(list) method: df.loc[(df.index.weekday.isin([3,4,5])), 'power'] = 0

edited Nov 23, 2020 at 20:19

answered Nov 23, 2020 at 19:52

BenB

6583 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andre Over a year ago

But still a lot of typing, if I want to do this for more days. Is there another more elegant solution?

BenB Over a year ago

Glad to hear that. Feel free to upvote and accept my answer. Yes, you can extend this by using df.index.weekday.isin([list of days]) as your condition

Andre Over a year ago

PERFECT! :) Thank you!

Collectives™ on Stack Overflow

pandas: boolean selecting rows by index (DatetimeIndex)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related