14

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?

lcguida
  • 3,787
  • 2
  • 33
  • 56
Kemeia
  • 826
  • 2
  • 9
  • 24
  • 2
    Welcome to StackOverflow. Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on [how to ask a good question](http://stackoverflow.com/help/how-to-ask) may also be useful. – jezrael Feb 22 '17 at 08:51

6 Answers6

19

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)]
Ravishankar Sivasubramaniam
  • 771
  • 5
  • 9
3

value you are looking for is not duplicated:

poz=matrix[matrix==minv].dropna(axis=1,how='all').dropna(how='all')
value=poz.iloc[0,0]
index=poz.index.item()
column=poz.columns.item()

you can get its index and column

duplicated:

matrix=pd.DataFrame([[1,1],[1,np.NAN]],index=['q','g'],columns=['f','h'])
matrix
Out[83]: 
   f    h
q  1  1.0
g  1  NaN
poz=matrix[matrix==minv].dropna(axis=1,how='all').dropna(how='all')
index=poz.stack().index.tolist()
index
Out[87]: [('q', 'f'), ('q', 'h'), ('g', 'f')]

you will get a list

Jay
  • 738
  • 8
  • 14
3

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]) ]

Peterd
  • 31
  • 1
2

I think this question may have been asked before here. The accepted answer is pretty comprehensive and should help you find the index of a value in a column.

Edit: if the column that the value exists in is not known, then you could use:

for col in df.columns:
    df[df[col] == 'security_id'].index.tolist()
Community
  • 1
  • 1
Adam Slack
  • 518
  • 6
  • 11
  • 1
    At the given question the column is known. In my case I don't know in which column the value appears. But I agree that it gives the direction to the answer for my question – Kemeia Feb 22 '17 at 09:27
  • Ahh, apologies! you could loop over the Column's in the data frame and apply the answer linked above. `for col in df.columns: df[df[col] == 'security_id'].index.tolist()`. This would also give you all occurrences of what you're looking for. – Adam Slack Feb 22 '17 at 09:36
2

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
Ujjwal
  • 1,849
  • 2
  • 17
  • 37
  • 1
    Thanks, this seems like a solution:) Though is the only way to find the value to iterate over both the rows and columns? Would there be a more efficient method? – Kemeia Feb 22 '17 at 09:30
  • No matter what you do, iteration will always be involved. Either you will do it , else Pandas would do it. Internally iteration will always be involved. Moreover iteration stops once, you get the ID. The worst case would be when security_id is the bottom-right element of your DataFrame ( O(mn) ). If the security_id is in the top-left half of the DataFrame, it won't be costly at all. – Ujjwal Feb 22 '17 at 09:36
  • 1
    Moreover , you are asking for data cleaning. So, it is a cheap preprocessing step. Dont try to hyper-optimize everything. Premature optimization is the root of all evils. Remember. – Ujjwal Feb 22 '17 at 09:37
  • Yeah that makes sense, I thought that might be the case (iteration in any case). Thanks for the explanation. – Kemeia Feb 22 '17 at 09:38
0

Function finds the positions of a value in a dataframe 

import pandas as pd
import numpy as np

def pandasFindPositionsInDataframe(dfIn,findme):
    positions = []
    irow =0
    while ( irow < len(dfIn.index)):
        list_colPositions=dfIn.columns[dfIn.iloc[irow,:]==findme].tolist()   
        if list_colPositions != []:
            colu_iloc = dfIn.columns.get_loc(list_colPositions[0])
            positions.append([irow, colu_iloc])
        irow +=1

    return positions
tasos
  • 1