Select rows of data frame based on true false boolean list

parishek 0
Viewed 37

I want to select rows of a dataframe based on isin calculations I did using two seperate dataframes.

Here is the code:

file = r'file path for df'
df = pd.read_csv(file, encoding='utf-16le', sep='\t')

keepcolumns = ["CookieID", "CryptID"]
df = df[keepcolumns]


file = r'file path for mappe.csv'
dfmappe = pd.read_csv(file, sep=';')

mask = (dfmappe[['CryptIDs']].isin(df[['CryptID']])).all(axis=1)
dffound = df[mask]

However in the last line I get the following error:

IndexingError                             Traceback (most recent call last)
Untitled-1.ipynb Zelle 3 in <cell line: 9>()
      6 dfmappe = pd.read_csv(file, sep=';')
      8 mask = (dfmappe[['CryptIDs']].isin(df[['CryptID']])).all(axis=1)
----> 9 dffound = df[mask]

File c:\Users\pchauh04\Anaconda3\envs\python\lib\site-packages\pandas\core\frame.py:3496, in DataFrame.__getitem__(self, key)
   3494 # Do we have a (boolean) 1d indexer?
   3495 if com.is_bool_indexer(key):
-> 3496     return self._getitem_bool_array(key)
   3498 # We are left with two options: a single key, and a collection of keys,
   3499 # We interpret tuples as collections only for non-MultiIndex
   3500 is_single_key = isinstance(key, tuple) or not is_list_like(key)

File c:\Users\pchauh04\Anaconda3\envs\python\lib\site-packages\pandas\core\frame.py:3549, in DataFrame._getitem_bool_array(self, key)
   3543     raise ValueError(
   3544         f"Item wrong length {len(key)} instead of {len(self.index)}."
   3545     )
   3547 # check_bool_indexer will throw exception if Series key cannot
   3548 # be reindexed to match DataFrame rows
-> 3549 key = check_bool_indexer(self.index, key)
   3550 indexer = key.nonzero()[0]
   3551 return self._take_with_is_copy(indexer, axis=0)
...
   2388     return result.astype(bool)._values
   2389 if is_object_dtype(key):
   2390     # key might be object-dtype bool, check_array_indexer needs bool array

IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match).

Here are the two files: dfmappe df

pandas indexing python
1 Answers

Problem is condition and filtered DataFrame has different index values:

#condition has index from dfmappe
mask = (dfmappe[['CryptIDs']].isin(df[['CryptID']])).all(axis=1)
#filtered df - both DataFrames has different indices, so raise error
dffound = df[mask]

Possible solutions - because tested one columns is removed [[]] and all(axis=1):

mask = dfmappe['CryptIDs'].isin(df['CryptID'])
#filtered dfmappe
dffound = dfmappe[mask]

Or:

#mask test df by dfmappe columns
mask = df['CryptIDs'].isin(dfmappe['CryptID'])
#filtered df
dffound = df[mask]
jezrael0
Related