How to iterate over pandas multiindex dataframe using index

Question

I have a data frame df which looks like this. Date and Time are 2 multilevel index

                           observation1   observation2
date          Time                             
2012-11-02    9:15:00      79.373668      224
              9:16:00      130.841316     477
2012-11-03    9:15:00      45.312814      835
              9:16:00      123.776946     623
              9:17:00      153.76646      624
              9:18:00      463.276946     626
              9:19:00      663.176934     622
              9:20:00      763.77333      621
2012-11-04    9:15:00      115.449437     122
              9:16:00      123.776946     555
              9:17:00      153.76646      344
              9:18:00      463.276946     212

I want to run some complex process over daily data block.

Pseudo code would look like

 for count in df(level 0 index) :
     new_df = get only chunk for count
     complex_process(new_df)

So, first of all, I could not find a way to access only blocks for a date

2012-11-03    9:15:00      45.312814      835
              9:16:00      123.776946     623
              9:17:00      153.76646      624
              9:18:00      463.276946     626
              9:19:00      663.176934     622
              9:20:00      763.77333      621

and then send it for processing. I am doing this in for loop as I am not sure if there is any way to do it without mentioning exact value of level 0 column. I did some basic search and found df.index.get_level_values(0), but it returns all the values and that causes loop to run multiple times for a given day. I want to create a Dataframe per day and send it for processing.

Jean-Francois T. · Accepted Answer · 2022-05-20 06:25:05Z

160

One easy way would be to groupby the first level of the index - iterating over the groupby object will return the group keys and a subframe containing each group.

In [136]: for date, new_df in df.groupby(level=0):
     ...:     print(new_df)
     ...:     
                    observation1  observation2
date       Time                               
2012-11-02 9:15:00     79.373668           224
           9:16:00    130.841316           477

                    observation1  observation2
date       Time                               
2012-11-03 9:15:00     45.312814           835
           9:16:00    123.776946           623
           9:17:00    153.766460           624
           9:18:00    463.276946           626
           9:19:00    663.176934           622
           9:20:00    763.773330           621

                    observation1  observation2
date       Time                               
2012-11-04 9:15:00    115.449437           122
           9:16:00    123.776946           555
           9:17:00    153.766460           344
           9:18:00    463.276946           212

You can also use droplevel to remove the first index (the useless date index):

In [136]: for date, new_df in df.groupby(level=0):
     ...:     print(new_df.droplevel(0))
     ...:
         observation1  observation2
Time
9:15:00     79.373668           224
9:16:00    130.841316           477
...

edited May 20, 2022 at 6:25

Jean-Francois T.

13.3k7 gold badges82 silver badges118 bronze badges

answered Sep 19, 2014 at 13:23

chrisb

52.7k8 gold badges73 silver badges70 bronze badges

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

7 Comments

Yantraguru Over a year ago

That helps!. I was following rather roundabout way - first finding index lable and then slicing it using iloc.

rgk Over a year ago

Where has this been for the last 3 years of my life. Such a clean method thank you!

Piotr Over a year ago

This method is neat, but how to apply changes from these subframes to the main dataframe? Searching it through loc or iloc makes the computations extremely slow.

bendl Over a year ago

@Manaslu if you can wrap the changes into a function you can use df.groupby('key').apply(function)

Hunaphu Over a year ago

Great! Fast and clean way to solve the problem. This is much faster than iterating over dates and getting df.loc[date].

|

psorenson · Accepted Answer · 2015-04-26 03:11:15Z

11

What about this?

for idate in df.index.get_level_values('date'):
    complex_process(df.ix[idate], idate)

answered Apr 26, 2015 at 3:11

psorenson

2892 silver badges6 bronze badges

2 Comments

H. Brandsmeier Over a year ago

Careful with this solution, note that each value of idate can be hit multiuple times. You should be doing for idate in np.unique(df.index.get_level_values('date')): Note tha additional np.unique.

Nate Stemen Over a year ago

I think df.index.get_level_values('date').unique() may better as @melbay pointed out.

melbay · Accepted Answer · 2017-11-13 02:42:07Z

7

Tagging off of @psorenson answer, we can get unique level indices and its related data frame slices without numpy as follows:

for date in df.index.get_level_values('date').unique():
    print(df.loc[date])

answered Nov 13, 2017 at 2:42

melbay

791 silver badge1 bronze badge

Comments

sanzoghenzo · Accepted Answer · 2020-10-15 13:15:53Z

4

Late to the party, I found that the following works, too:

for date in df.index.unique("date"):
    print(df.loc[date])

It uses the level optional parameter of the Index.unique method introduced in version 0.23.0.

You can specify either the level number or label.

answered Oct 15, 2020 at 13:15

sanzoghenzo

6609 silver badges23 bronze badges

Comments

Roger V. · Accepted Answer · 2021-11-18 10:43:39Z

3

Another alternative:

for date in df.index.levels[0]:
    print(df.loc[date])

The difference with the df.index.unique("date") proposed by @sanzoghenzo is that it refers to the index level by its number rather than name.

answered Nov 18, 2021 at 10:43

Roger V.

8036 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to iterate over pandas multiindex dataframe using index

5 Answers 5

7 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related