Reputation: 161

Pandas iterating through index to find a value

I have a very large dataframe of experimental data. I have a Multi index that define each samples (A,B) with the data in columns x,y,z:

 A   B    x    y     z
0.1  0.1  0.1   0   -1
0.1. 0.1  0.2  -5    0
0.1  0.1  0.4 -10    0 
0.1  0.2  0.6   0   -1
0.1  0.2  0.3  -4 -0.4
0.1  0.2  0.1  -9    0
0.1  0.5  0.2   0    0
0.1  0.5  0.4  -2    0
0.1  0.5  0.3  -5    0
0.2  0.1  0.2  -1   -1
0.2  0.1  0.1  -2   -2
0.2  0.1  0.4  -3    0

...

I have a complicated condition to fill but essentially:

How do I iterate through each value of A and B, to find the max value of column y where the value of column z = 0, and then return a df of all the rows that fulfil this condition

Expected output:

 A   B    x    y     z

0.1. 0.1  0.2  -5   0
0.1  0.2  0.1  -9   0
0.1  0.5  0.2   0   0
0.2  0.1  0.4  -3   0

Thanks in advance.

Upvotes: 1

Answers (3)

potatasbravas

Reputation: 161

output=(df.loc[df['z']==0]).groupby(['A','B'])['y'].max()

Thanks to Narendra Prasath for pointing me in the right direction.

Upvotes: 0

Christian Eslabon

Reputation: 185

df[(df['z'] == 0) & (df['y'] == df['y'].max())]

Upvotes: 0

Narendra Prasath

Reputation: 1531

you can try this

df.loc[df[df["z"] == 0].groupby(["A","B"])['y'].idxmax()].reset_index(drop=True)

Upvotes: 3

Pandas iterating through index to find a value

Answers (3)

Related Questions