Indexing using value from another DataFrame

Viewed 49

pandas version 1.1.3.

I am working with two dataframes (form csv's) in pandas, one containing the data I'm analysing, the other containing labels. Both contain a column with identification numbers. I have set the row indeces of dataframe 2 as the identification numbers. I am using a nlp processing library to analyse the data inside dataframe 1, which returns a boole. I want to index a value in dataframe 2, using the identification numbers form dataframe 1 in a fancy indexing method.

df1 looks like this:

  ID report

1 A1 'bla bla'
2 A1 'blo blo'
3 A1 'blo bla'
4 A2 'bla bla'
5 A2 'blo blo'
6 A3 'foo blo'

df2 looks like this:

   label hypothesis
ID
A1 0     0
A2 1     0 
A3 1     0
A4 0     0
A5 1     0
A6 0     0

This is my code:

for i, row in df1.iterrows():
    index = row['ID']
    display(index)
    report = row['report']
    doc = nlp(report)
    if boole_is_True(doc):
        df2[[index, 'hypothesis']] = 1

This is the result:

'A1'

'A1'

'A1'

'A2'

bla bla
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-22-f9ba77c1e7ea> in <module>
     17         print(report)
---> 18         df2[[index, 'hypothesis']] = 1

~\anaconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2906             if is_iterator(key):
   2907                 key = list(key)
-> 2908             indexer = self.loc._get_listlike_indexer(key, axis=1, raise_missing=True)[1]
   2909 
   2910         # take() does not accept boolean indexers

~\anaconda3\lib\site-packages\pandas\core\indexing.py in _get_listlike_indexer(self, key, axis, raise_missing)
   1252             keyarr, indexer, new_indexer = ax._reindex_non_unique(keyarr)
   1253 
-> 1254         self._validate_read_indexer(keyarr, indexer, axis, raise_missing=raise_missing)
   1255         return keyarr, indexer
   1256 

~\anaconda3\lib\site-packages\pandas\core\indexing.py in _validate_read_indexer(self, key, indexer, axis, raise_missing)
   1302             if raise_missing:
   1303                 not_found = list(set(key) - set(ax))
-> 1304                 raise KeyError(f"{not_found} not in index")
   1305 
   1306             # we skip the warning on Categorical

KeyError: "['A2'] not in index"

How do I strip the square brackets? Why are there brackets? What is a better title for this question, so it's easier to find for others?

Thanks in advance! My actual data contains medical reports, so I'm afraind I cannot show you my actual data and code.

1 Answers

When you use:

df2[[index, 'hypothesis']] = 1

pandas searches for the passed index value in the column index and is unable to find A2 among your column names. In your case, you are trying to find those values in the row index of df2, so you need to write as follows:

df2.loc[index, 'hypothesis'] = 1

.loc[] takes in row and column index values.

Related