How to check if a string is part of a DataFrame Column?

Question

I have a simple dataframe with 99 columns which I would like to make a query that finds the counties that belong to regions 1 or 2, whose name starts with 'Washington', and whose POPESTIMATE2015 was greater than their POPESTIMATE 2014:

census_df = pd.read_csv('census.csv')

print(census_df[(census_df.REGION == 1) | (census_df.REGION == 2) \
   & (census_df.POPESTIMATE2015 > census_df.POPESTIMATE2014) \
   & (census_df.CTYNAME.isin(['Washington']))][['STNAME', 'CTYNAME']])

I tried different ways and filter for "Washington" is not working at all.

Quang Hoang · Accepted Answer · 2020-05-07 20:01:27Z

1

You can use .str.startswith. Also, use loc access is a better practice than chained indexing:

census_df.loc[
    census_df.REGION.isin([1,2]) &
    census_df.POPESTIMATE2015.gt(census_df.POPESTIMATE2014) &
    census_df.CTYNAME.str.startswith('Washington'),
    ['STNAME','CTYNAME']
]

answered May 7, 2020 at 20:01

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Comments

Rostyslav Shevchenko · Accepted Answer · 2020-05-07 20:21:35Z

Your filter does not work because you are using isin, which is checking whether specified values are contained in the Series. So, you are basically selecting only samples where CTYNAME = 'Washington'. Consider a toy example:

import pandas as pd

data = {'REGION': [1, 2, 3, 1, 4], 'POPESTIMATE2015': [5, 5, 5, 5, 5], 'POPESTIMATE2014': [4, 3, 3, 8, 5],
   'CTYNAME': ['Washington', 'Washington_PD', 'AD_Washington', 'Washington Something', 'Different'],
   'STNAME': ['ST1', 'ST2', 'ST3', 'ST4', 'ST5']}
df = pd.DataFrame(data=data)

With isin you will only select:

df.loc[
df.REGION.isin([1,2]) &
df.POPESTIMATE2015.gt(df.POPESTIMATE2014) &
df.CTYNAME.isin(['Washington']),
['STNAME','CTYNAME']]


0   ST1 Washington

Instead, if you want to select samples where CTYNAME starts with "Washington" you should use df.CTYNAME.str.startswith:

df.loc[
df.REGION.isin([1,2]) &
df.POPESTIMATE2015.gt(df.POPESTIMATE2014) &
df.CTYNAME.str.startswith('Washington'),
['STNAME','CTYNAME']]

Obtaining:

0   ST1 Washington
1   ST2 Washington_PD

You can also use df.CTYNAME.str.contains to select all the samples where CTYNAME contains a word "Washington"

Collectives™ on Stack Overflow

How to check if a string is part of a DataFrame Column?

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