Searching specified row and return column in a dataframe-CodePudding

I would like to search the columns of a datafame on a specific row number to find an after finding the column, return another row value of the same column. In the following example, the selected row number is 2 and the value to search is 5, known as key. It will match the third column, V2. Then I want to return the third row value of V2.

V0,V1,V2,V3
4,1,1,1
6,4,5,2
2,3,6,7

In the following code, I first extract the specified row, then iterate over that like an array to find the index of the match and the, use the found index to access the original datafram column.

key = 5
row = df.iloc[1]              # row=[6,4,5,2]
for i in range(len(row)):
    if row[i] == key:
        res = df.iloc[:,i]    # i=2, res=[1,5,6]
        print(res[2])

output is 6. Is there any better to achieve that?

CodePudding user response：

Doing the eq (equal) follow by the shift slice

df.shift(-1)[df.eq(5)].stack().iloc[0]
Out[542]: 6.0

More detail : notice the first level index ,should always 1 refer to the original df

df.shift(-1)[df.eq(5)].stack()
Out[543]: 
1  V2    6.0
dtype: float64

CodePudding user response：

Here is a way using numpy's argwhere function which finds the matching index of interest.

import numpy as np

key = 5
row = 2

# use numpy argwhere function to identify the index
indices = np.argwhere(df.values[row, :] == key).flatten()

# use indices as input to DataFrame.iloc
df.iloc[row 1, indices]