Parsing through multiple columns pandas

Question

Here is the data:

raw_data = {'first_name': ['Jason', 'Jason', 'Tina', 'Jake', 'Amy'], 
            'last_name': ['Miller', 'Miller', 'Ali', 'Milner', 'Cooze'], 
            'age': [42, 42, 36, 24, 73], 
            'preTestScore': [4, 4, 31, 2, 3],
            'postTestScore': [25, 25, 57, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'preTestScore', 'postTestScore'])

Now I want to search through the dataframes and for two column values I want to get the corresponding two column values.

Here is what I tried-

a,b=df['first_name','last_name'].where(df['age','preTestScore']==42,4)

but its getting the error

KeyError                                  Traceback (most recent call last)
E:\anaconda\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   3063             try:
-> 3064                 return self._engine.get_loc(key)
   3065             except KeyError:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: ('first_name', 'last_name')

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
<ipython-input-6-07dc94043416> in <module>()
----> 1 a,b=df['first_name','last_name'].where(df['age','preTestScore']==42,4)

E:\anaconda\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2686             return self._getitem_multilevel(key)
   2687         else:
-> 2688             return self._getitem_column(key)
   2689 
   2690     def _getitem_column(self, key):

E:\anaconda\lib\site-packages\pandas\core\frame.py in _getitem_column(self, key)
   2693         # get column
   2694         if self.columns.is_unique:
-> 2695             return self._get_item_cache(key)
   2696 
   2697         # duplicate columns & possible reduce dimensionality

E:\anaconda\lib\site-packages\pandas\core\generic.py in _get_item_cache(self, item)
   2484         res = cache.get(item)
   2485         if res is None:
-> 2486             values = self._data.get(item)
   2487             res = self._box_item_values(item, values)
   2488             cache[item] = res

E:\anaconda\lib\site-packages\pandas\core\internals.py in get(self, item, fastpath)
   4113 
   4114             if not isna(item):
-> 4115                 loc = self.items.get_loc(item)
   4116             else:
   4117                 indexer = np.arange(len(self.items))[isna(self.items)]

E:\anaconda\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   3064                 return self._engine.get_loc(key)
   3065             except KeyError:
-> 3066                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   3067 
   3068         indexer = self.get_indexer([key], method=method, tolerance=tolerance)

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: ('first_name', 'last_name')

https://stackoverflow.com/questions/11285613/selecting-columns-in-a-pandas-dataframe — nio, Jul 09 '18 at 13:10

score 2 · Accepted Answer · answered Jul 09 '18 at 13:11

2

You can use set_index to convert your index to a MultiIndex ('age', 'preTestScore'). Then use pd.DataFrame.loc with row and column labels:

df2 = df.set_index(['age', 'preTestScore'])

cols = ['first_name', 'last_name']
res = df2.loc[(42, 4), cols].values.tolist()

print(res)

[['Jason', 'Miller']]

answered Jul 09 '18 at 13:11

jpp

159,742
34
281
339

I am getting the performance issue-E:\anaconda\lib\site-packages\pandas\core\indexing.py:1472: PerformanceWarning: indexing past lexsort depth may impact performance. return self._getitem_tuple(key) – ubuntu_noob Jul 09 '18 at 14:14
@ubuntu_noob, You can also try jezrael's solution. But this is just a warning (not an error), it's not a problem *per se*. You should test both methods to see which is actually faster. – jpp Jul 09 '18 at 14:19
thanks for the suggestion...I will try that and report the result – ubuntu_noob Jul 09 '18 at 14:27
and what do I do if I want the result stored in 2 variables... I did a,b=df2.loc[(42, 4), cols].values.tolist() but it gave error – ubuntu_noob Jul 09 '18 at 14:32
@ubuntu_noob, I suggest you print `df2.loc[(42, 4), cols].values.tolist()` and see what you have. Then unpack as you like. – jpp Jul 09 '18 at 14:34
I am getting same answer as [['Jason', 'Miller']] – ubuntu_noob Jul 09 '18 at 14:38
Ok, then unpack via `a, b = res[0]` following the code in my example? – jpp Jul 09 '18 at 14:39

jezrael · Answer 2 · 2018-07-09T13:55:54.190

I think need compare both values and add all for all Trues per rows:

m = (df[['age','preTestScore']].values == np.array([42, 4])).all(axis=1)
print (m)
[ True  True False False False]

a = df.loc[m, ['first_name','last_name']].drop_duplicates()
print (a)
  first_name last_name
0      Jason    Miller

Detail:

print ((df[['age','preTestScore']].values == np.array([42, 4])))

[[ True  True]
 [ True  True]
 [False False]
 [False False]
 [False False]]

Parsing through multiple columns pandas

2 Answers2