How to get index of an element in csv whose entry matches with an entry in a text file

Question

I have a text file abc.txt consisting of ids:

301KG0KXAFQBZD5C6JXD5Y3V32D2HR
301KG0KXAFQBZD5C6JXD5Y3V32DH26
302OLP89E2C9N8P0X6CR0PPD2YZCAY
302U8RURK26C60PPXRC1CNX2MCEVN0
304QEQWK0SPEVKOLV9OP6J7HIQ70OT
306996CF7ZPUJFKUNNN3E4QSGJU1BT
306996CF7ZPUJFKUNNN3E4QSGJV1BU
306W7JMRZ13CUF4FM8WITED0UP08BK
307FVKVSZUKO92ENXOUP70BZ9DE74R
3087LXLJ7PLKP7BSW65ZJPY3QPFF0L
3087LXLJ7PLKP7BSW65ZJPY3QPHF0N

and so on

All I've to do is:

for each string in abc.txt, find and return the index number of xyz.csv whose column named HITID contains that string and assign it to index variable.

My attempt:

clm = pd.read_csv('xyz.csv')

f = open('abc.txt', 'rb')

for entry in f:
    # if clm['HITId'].str.contains(entry).any()

    index =clm[clm['HITId']==entry].index.item()
    print(index)

Getting an error that says:

  File "approve_reject_hits_fingerD.py", line 89, in <module>
    index =clm[clm['HITId']==entry].index.item()
  File "/Users/AjayB/anaconda3/envs/MyDjangoEnv/lib/python3.6/site-packages/pandas/core/base.py", line 716, in item
    return self.values.item()
ValueError: can only convert an array of size 1 to a Python scalar

and when I use the line:

index =clm[clm['HITId']==entry].index[0]

Then I come up with this:

  File "approve_reject_hits_fingerD.py", line 89, in <module>
    index =clm[clm['HITId']==entry].index[0]
  File "/Users/AjayB/anaconda3/envs/MyDjangoEnv/lib/python3.6/site-packages/pandas/core/indexes/base.py", line 3958, in __getitem__
    return getitem(key)
IndexError: index 0 is out of bounds for axis 0 with size 0

Newish to python and specifically pandas, so tried these attempts referring:

Find element's index in pandas Series

Get index of a row of a pandas dataframe as an integer

How to tackle this syntactically?

@Someprogrammerdude - it is good, [`boolean indexing`](http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#boolean-indexing) and compared values from `f` each `entry` — jezrael, Nov 04 '19 at 07:57
csv file has the column 'HITId' with similar ids like those in abc.txt file — Ajay Bhagchandani, Nov 04 '19 at 07:57

score 1 · Accepted Answer · answered Nov 04 '19 at 07:54

There is problem no matching any value, so error. Possible solution is use next with iter for return first matched value if exist else some default value, here no match:

clm = pd.read_csv('xyz.csv')

f = open('abc.txt', 'rb')

for entry in f:

    index =clm[clm['HITId']==entry].index
    print(index)
    print (next(iter(index), 'no match'))

How to get index of an element in csv whose entry matches with an entry in a text file

1 Answers1