Query Dataframe Column on String Values

Question

I need to grab values where the column startswith either value 'MA', 'KP'.

I am trying to chain my dataframe query as such:

df.loc[df['REFERRAL_GRP'].str.startswith("KP")==True | df['REFERRAL_GRP'].str.startswith("MA")==True]

This doesn't seem to work because the column contains pd.nan objects (NULL values).

By themselves, the queries work, how can I merge these two queries together?

Thank you

Here is my error message:

Traceback (most recent call last): Debug Probe, prompt 40, line 1 File "c:\Python27\Lib\site-packages\pandas\core\generic.py", line 892, in __nonzero__ .format(self.__class__.__name__)) ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

So, the column name needs to start with either "MA" or "KP?" Then why are you trying to grab the values starting with "MA" or "KP"? Your question and attempt are at odds with each other. — boot-scootin
– boot-scootin, Commented Jan 3, 2017 at 17:35
is that expression being used in an if, for or some other conditional? — e4c5
– e4c5, Commented Jan 3, 2017 at 17:45

piRSquared · Accepted Answer · 2017-01-03 18:35:01Z

2

This is a question we see a lot.

df.loc[
    # 2. This returns a whole lot of `True`s or `False`s
    df['REFERRAL_GRP'].str.startswith("KP")==True
    # 1. `|` is expecting a `True` or `False`
    | 
    # 2. This returns a whole lot of `True`s or `False`s
    df['REFERRAL_GRP'].str.startswith("MA")==True
]

Fix it by wrapping conditions with parenthesis

df.loc[
    # 1. Series of `True`s or `False`s
    (df['REFERRAL_GRP'].str.startswith("KP")==True)
    # 2. `|` is now a operator on `pd.Series` and is expecting `pd.Series`
    | 
    # 1. Series of `True`s or `False`s
    (df['REFERRAL_GRP'].str.startswith("MA")==True)
]

That said, I'd do this

df.loc[df.REFERRAL_GRP.str.match('^KP|MA')]

edited Jan 3, 2017 at 18:35

answered Jan 3, 2017 at 18:06

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shijo · Accepted Answer · 2017-01-03 17:35:57Z

2

try numpy logical_or

import numpy as np
df.loc[np.logical_or(df['REFERRAL_GRP'].str.startswith("KP")==True , df['REFERRAL_GRP'].str.startswith("MA")==True)]

answered Jan 3, 2017 at 17:35

Shijo

9,7913 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

Query Dataframe Column on String Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related