CODEWITHSUNDEEP
pythonpandas
Alex
Alex

Reputation: 1381

filter pandas dataframe on one level of a multi level index

If I have a pandas dataframe with a multi level index, how can I filter by one of the levels of that index. For example:

df = pd.DataFrame({"id": [1,2,1,2], "time": [1, 1, 2, 2], "val": [1,2,3,4]})
df.set_index(keys=["id", "time"], inplace=True)

I would like to do something like:

df[df["time"] > 1]

but time is no longer a column. I could keep it as a column but I don't want to drag around copies of data.

Upvotes: 27

Views: 18535

Answers (2)

piRSquared
piRSquared

Reputation: 294358

query

df.query('time > 1')

         val
id time     
1  2       3
2  2       4

IndexSlice

DataFrame index must be lexsorted

df.sort_index().loc[pd.IndexSlice[:, 2:], :]

         val
id time     
1  2       3
2  2       4

Upvotes: 21

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210872

In [17]: df[df.index.get_level_values('time') > 1]
Out[17]:
         val
id time
1  2       3
2  2       4

@piRSquared's solution is more idiomatic though...

Upvotes: 30

Related Questions