Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas - find index of value anywhere in DataFrame

Tags:

python

pandas

I'm new to Python & Pandas.

I want to find the index of a certain value (let's say security_id) in my pandas dataframe, because that is where the columns start. (There is an unknown number of rows with irrelevant data above the columns, as well as a number of empty 'columns' on the left side.)

As far as I see, the isin method only returns a boolean on whether the value exists, not its index.

How do I find the index of this value?

like image 653
Kemeia Avatar asked Feb 22 '17 08:02

Kemeia


People also ask

How do you find the index of a particular value in a DataFrame?

To find the indexes of the specific value that match the given condition in Pandas dataframe we will use df['Subject'] to match the given values and index. values to find an index of matched value. The result shows us that rows 0,1,2 have the value 'Math' in the Subject column.

How do I find a particular value in a Pandas DataFrame column?

You can check if a column contains/exists a particular value (string/int), list of multiple values in pandas DataFrame by using pd. series() , in operator, pandas. series. isin() , str.

What does Reset_index () do in Pandas?

Pandas DataFrame reset_index() Method The reset_index() method allows you reset the index back to the default 0, 1, 2 etc indexes. By default this method will keep the "old" idexes in a column named "index", to avoid this, use the drop parameter.


3 Answers

Get the index for rows matching search term in all columns

search = 'security_id' 
df.loc[df.isin([search]).any(axis=1)].index.tolist()

Rows filtered for matching search term in all columns

search = 'search term' 
df.loc[df.isin([search]).any(axis=1)]
like image 52
Ravishankar Sivasubramaniam Avatar answered Nov 15 '22 18:11

Ravishankar Sivasubramaniam


A oneliner solution avoiding explicit loops...

  • returning the entire row(s)

    df.iloc[np.flatnonzero((df=='security_id').values)//df.shape[1],:]

  • returning row(s) and column(s)

    df.iloc[ np.flatnonzero((df=='security_id').values)//df.shape[1], np.unique(np.flatnonzero((df=='security_id').values)%df.shape[1]) ]

like image 37
Peterd Avatar answered Nov 15 '22 17:11

Peterd


Supposing that your DataFrame looks like the following :

      0       1            2      3    4
0     a      er          tfr    sdf   34
1    rt     tyh          fgd    thy  rer
2     1       2            3      4    5
3     6       7            8      9   10
4   dsf     wew  security_id   name  age
5   dfs    bgbf          121  jason   34
6  dddp    gpot         5754   mike   37
7  fpoo  werwrw          342   jack   31

Do the following :

for row in range(df.shape[0]): # df is the DataFrame
         for col in range(df.shape[1]):
             if df.get_value(row,col) == 'security_id':
                 print(row, col)
                 break
like image 44
Ujjwal Avatar answered Nov 15 '22 19:11

Ujjwal