Accessing Another Column By Value ,Pandas

Question

I have a dataframe as follows:

    Type      Name           Category              
    Bird      Flappy Bird    Air      
    Bird      Pigeon         Air    
    Pokemon   Jerry          Aquatic      
    Pokemon   Mudkip         Aquatic
    Animal    Lion           Terrestrial
    Bird      Pigeon         Air2  

For the given name like say "Pigeon", I need to access the corresponding value in the category Column i.e it should give me the string "Air" .
I have 2 values of Pigeon but i need to return "Air" for 2nd observation.

Please note that I am not using indexing at all so having something like 2nd observation with iloc would'nt do. I need to access it by value in the other column.

Or something like obtaining the index of "Pigeon" and using that to get the corresponding column value will do.

Solution

use loc

So for you example:

df.loc[df.Name == 'Pigeon', 'Category'] 

would give you what you want

Example:

In [17]:

import io
import pandas as pd
temp = """Type      Name           Category              
Bird      Flappy_Bird    Air      
Bird      Pigeon         Air    
Pokemon   Jerry          Aquatic      
Pokemon   Mudkip         Aquatic
Animal    Lion           Terrestrial"""

df = pd.read_csv(io.StringIO(temp), sep='\s+')
df
Out[17]:
      Type         Name     Category
0     Bird  Flappy_Bird          Air
1     Bird       Pigeon          Air
2  Pokemon        Jerry      Aquatic
3  Pokemon       Mudkip      Aquatic
4   Animal         Lion  Terrestrial

[5 rows x 3 columns]
In [19]:

df.loc[df.Name == 'Pigeon','Category']
Out[19]:
1    Air
Name: Category, dtype: object

If you have multiple values and just want the first then use idxmin:

In [24]:

df.ix[(df.Name == 'Pigeon').idxmin(),'Category']
Out[24]:
'Air'

EDIT

So for the case where we have multiple values and you want to access the individual values, you can either check the index values and directly access them:

In [23]:

df.loc[df.Name=='Pigeon','Category'].index.values
Out[23]:
array([1, 5], dtype=int64)
In [26]:

df.loc[df.Name=='Pigeon','Category'][5]
Out[26]:
'Air2'

OR if you want to loop over them then series has a iteritems() method:

In [28]:

df.loc[df.Name=='Pigeon','Category'].iteritems()
Out[28]:
[(1, 'Air'), (5, 'Air2')]

either of these should satisfy your requirements

Comments

[cwharland]

Not sure about your "not using indexing" comment since by construction every dataframe has an index...it is not possible to have one without. In such a case, doesn't df[df.Name == 'Pigeon'].Category work?

[Hypothetical Ninja]

Actually that would be better , obtain index of current "Pigeon" and use that index to obtain corresponding value in "category"

[Hypothetical Ninja]

@cwharland your solution works , just add a condition for the index part and frame this in an answer.. :)

[Hypothetical Ninja]

yeah sure.. update your answer . and lets clean the comments section