CODEWITHSUNDEEP
pythonpandasmulti-index
RogUE
RogUE

Reputation: 363

Pandas MultiIndex: Partial indexing on second level

I have a data-set open in Pandas with a 2-level MultiIndex. The first level of the MultiIndex is a unique ID (SID) while the second level is time (ISO_TIME). A sample of the data-set is given below.

                                     SEASON NATURE  NUMBER
SID           ISO_TIME                                  
2020138N10086 2020-05-16 12:00:00    2020     NR      26
              2020-05-16 15:00:00    2020     NR      26
              2020-05-16 18:00:00    2020     NR      26
              2020-05-16 21:00:00    2020     NR      26
              2020-05-17 00:00:00    2020     NR      26
2020155N17072 2020-06-02 18:00:00    2020     NR      30
              2020-06-02 21:00:00    2020     NR      30
              2020-06-03 00:00:00    2020     NR      30
              2020-06-03 03:00:00    2020     NR      30
              2020-06-03 06:00:00    2020     NR      30
2020327N11056 2020-11-21 18:00:00    2020     NR     103
              2020-11-21 21:00:00    2020     NR     103
              2020-11-22 00:00:00    2020     NR     103
              2020-11-22 03:00:00    2020     NR     103
              2020-11-22 06:00:00    2020     NR     103
2020329N10084 2020-11-23 12:00:00    2020     NR     104
              2020-11-23 15:00:00    2020     NR     104
              2020-11-23 18:00:00    2020     NR     104
              2020-11-23 21:00:00    2020     NR     104
              2020-11-24 00:00:00    2020     NR     104

I can do df.loc[("2020138N10086")] to select rows with SID=2020138N10086 or df.loc[("2020138N10086", "2020-05-17")] to select rows with SID=2020138N10086 and are on 2020-05-17.
What I want to do, but not able to, is to partially index using the second level of MultiIndex. That is, select all rows on 2020-05-17, irrespective of the SID.
I have read through Pandas MultiIndex / advanced indexing which explains how indexing is done with MultiIndex. But nowhere in it could I find how to do a partial indexing on the second/inner level of a Pandas MultiIndex. Either I missed it in the document or it is not explained in there.
So, is it possible to do a partial indexing in the second level of a Pandas MultiIndex?
If it is possible, how do I do it?

Upvotes: 4

Views: 2211

Answers (3)

DavidWalz
DavidWalz

Reputation: 81

Use a cross-section

df.xs('2020-05-17', level="ISO_TIME")

Upvotes: 0

cellularegg
cellularegg

Reputation: 172

you can do this with slicing. See the pandas documentation.

Example for your dataframe:

df.loc[(slice(None), '2020-05-17'), :]

Upvotes: 4

Ravi
Ravi

Reputation: 3217

df=df.reset_index()
dates_rows= df[df["ISO_TIME"]=="2020-05-17"]

If you want you can convert it back to a multi-level index again, like below

df.set_index(['SID', 'ISO_TIME'], inplace=True)

Upvotes: 0

Related Questions