Pandas slice rows in multindex dataframe fails

user_12285101 0
Viewed 101

I have the following dataframe, where date ,hour and code are multindex:

                                    1         2         3         4       
date           hour      code    
2020-11-01     13         A1        2.3     2.6      7.4         5.3
2020-11-01     13         A2        7.5     7.8      7.6         7.7
2020-11-01     13         A3        4.2     5.3      5.9         6.1
2020-11-02     11         A1        4.3     3.3      9.8         8.5
2020-11-02     11         A2        4.4     8.6      11.1        4.3
2020-11-02     11         A3        4.3     2.1      4.5         3.8
2020-11-02     12         A1        5.4     5.6      5.8         5.7
...

I'm trying to slice the dataframe for example to get only the 2020-11-02 hour 11, but I am failing in doing this.I have tried to do it as described here :

df.loc[(slice(None), slice('2020-11-02',11)), :]

>>>

but the retuurns empty dataframe (the date is object and the hour is int). I have also tried :

agg.loc[(slice('2020-11-01'), slice(13)), :]

but that returns error:

TypeError: '<' not supported between instances of 'datetime.date' and 'str'

In this post I saw that I could do at least select only the date like this :

df.loc[['2020-11-02']]

but that returns error as well:

KeyError: "['2020-11-01'] not in index" (thought as I mentioned, the date is object)

My end goal is to be able to chart the differnt "codes" for each date and hoour (for example the 1/11/2020 13:00 will have three lines with columns values 1,2,3,4 , etc) at the moment i'm stuck in the part of being able to slice multindex dataframe.

My goal here is to be able to slice the rows I want based on date and hour indices.

Edit: The inndex dtype is object:

agg.index.get_level_values(0).dtype
>>>dtype('O')
multi-index pandas slice filter python
1 Answers

You simply have a dtyping problem : you should change the type of your column date.

As stated in your error, your column is of dtype object containing datetime.date objects.

First convert it to a column of dtype datetime, they are far easier to work with, even allowing strings to filter dates ! From datetime.date objects, it's as simple as :

df['date'] = pd.to_datetime(df['date'])

Then use direct indexing and it will work directly :

df.loc['2020-11-02', 11]

Then, if you want to have more freedom, use pd.IndexSlice :

# All A1
df.loc[pd.IndexSlice[:, :, 'A1']]
# All A1 keeping a triple index :
df.loc[pd.IndexSlice[:, :, 'A1':'A1']]
# All 13 o'clock (keeping a triple index) :
df.loc[pd.IndexSlice[:, 13:13, :]]
user_153424070
Related