Tim Tim - 1 month ago 10
Python Question

Pandas Newbie: Sort by nth row in dataframe

I have a dataframe that looks like this:

Grouped Week Revenue Users Period CSum
2013-10-14 2013-10-14 2863.75 36 1 2863.75
2013-10-21 202.20 4 2 3065.95
2013-10-28 603.45 8 3 3669.40
2013-11-04 535.65 9 4 4205.05
2013-11-11 424.45 14 5 4629.50
2015-06-01 2015-06-01 24115.91 468 1 24115.91
2015-06-08 1634.93 32 2 25750.84
2015-06-15 2664.00 62 3 28414.84
2015-06-22 1646.05 40 4 30060.89


Trying to figure out with Pandas how to essentially provide only the 4th Period for each Grouped so that I get:

Grouped Week Revenue Users Period CSum
2013-10-14 2013-11-04 535.65 9 4 4205.05
2015-06-01 2015-06-22 1646.05 40 4 30060.89


What would be the best method to do this?

Answer

Solution with boolean indexing:

df = df[df['Period'] == 4]
print (df)
                       Revenue  Users  Period      CSum
Grouped    Week                                        
2013-10-14 2013-11-04   535.65      9       4   4205.05
2015-06-01 2015-06-22  1646.05     40       4  30060.89

Another solution with cumcount if need select each 4.th row per first level of MultiIndex and first solution cant be used:

ser = df.groupby(level=0).cumcount()
print (ser)
Grouped     Week      
2013-10-14  2013-10-14    0
            2013-10-21    1
            2013-10-28    2
            2013-11-04    3
            2013-11-11    4
2015-06-01  2015-06-01    0
            2015-06-08    1
            2015-06-15    2
            2015-06-22    3
dtype: int64

print (ser == 3)
Grouped     Week      
2013-10-14  2013-10-14    False
            2013-10-21    False
            2013-10-28    False
            2013-11-04     True
            2013-11-11    False
2015-06-01  2015-06-01    False
            2015-06-08    False
            2015-06-15    False
            2015-06-22     True
dtype: bool

print (df[ser == 3])
                       Revenue  Users  Period      CSum
Grouped    Week                                        
2013-10-14 2013-11-04   535.65      9       4   4205.05
2015-06-01 2015-06-22  1646.05     40       4  30060.89