CODEWITHSUNDEEP
pandassortingfilterpandas-groupbymulti-index
Juston Lantrip
Juston Lantrip

Reputation: 61

Pandas: filter by value, then get max value in Multiindex

I have a dataset that looks like this, where DATE and LETTER are the multi-indexed.

I want to return a list of max LETTER A VALUES per date.

So, in this instance, I want the VALUES 3 and 8 returned. 

                            VALUE                   
DATE      LETTER            
1-2002       A                1                            
             B                7
             A                3  
             B                4                                  
2-2002       A                6                   
             B                2                  
             A                8
             B                5

Upvotes: 2

Views: 2906

Answers (2)

jezrael
jezrael

Reputation: 862751

You can select withxs first and aggregate max:

df = df.xs('A', level=1).groupby(level=0).max().reset_index()
print (df)
     DATE  VALUE
0  1-2002      3
1  2-2002      8

L = df.xs('A', level=1).groupby(level=0)['VALUE'].max().tolist()
print (L)
[3, 8]

Old solution:

I think you first boolean indexing with get_level_values for filtering and then groupby by first and second index and aggregate max:

df = df[df.index.get_level_values('LETTER') == 'A']
df = df.groupby(level=[0,1]).max()
print (df)
               VALUE
DATE   LETTER       
1-2002 A           3
2-2002 A           8

df = df[df.index.get_level_values('LETTER') == 'A']
df = df.groupby(level=[0,1]).max().reset_index()
print (df)
     DATE LETTER  VALUE
0  1-2002      A      3
1  2-2002      A      8

Upvotes: 1

zipa
zipa

Reputation: 27869

So to get only max values for A:

df = df.reset_index()
df = df[(df['LETTER'] == 'A')].groupby(['DATE', 'LETTER']).max()

And to get that numbers in a list:

max_A = df[(df['LETTER'] == 'A')].groupby(['DATE', 'LETTER']).max()['VALUE'].tolist()

Upvotes: 0

Related Questions