pandas select from Dataframe using startswith

Question

This works (using Pandas 12 dev)

table2=table[table['SUBDIVISION'] =='INVERNESS']

Then I realized I needed to select the field using "starts with" Since I was missing a bunch. So per the Pandas doc as near as I could follow I tried

criteria = table['SUBDIVISION'].map(lambda x: x.startswith('INVERNESS'))
table2 = table[criteria]

And got AttributeError: 'float' object has no attribute 'startswith'

So I tried an alternate syntax with the same result

table[[x.startswith('INVERNESS') for x in table['SUBDIVISION']]]

Reference http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing Section 4: List comprehensions and map method of Series can also be used to produce more complex criteria:

What am I missing?

Could you give a small example which demonstrates this, I'm surprised that the list comprehension wouldn't raise in the same way as the map... — Andy Hayden
– Andy Hayden, Commented Jul 30, 2013 at 22:21

Neil · Accepted Answer · 2020-04-12 21:31:57Z

137

You can use the str.startswith DataFrame method to give more consistent results:

In [11]: s = pd.Series(['a', 'ab', 'c', 11, np.nan])

In [12]: s
Out[12]:
0      a
1     ab
2      c
3     11
4    NaN
dtype: object

In [13]: s.str.startswith('a', na=False)
Out[13]:
0     True
1     True
2    False
3    False
4    False
dtype: bool

and the boolean indexing will work just fine (I prefer to use loc, but it works just the same without):

In [14]: s.loc[s.str.startswith('a', na=False)]
Out[14]:
0     a
1    ab
dtype: object

.

It looks least one of your elements in the Series/column is a float, which doesn't have a startswith method hence the AttributeError, the list comprehension should raise the same error...

edited Apr 12, 2020 at 21:31

Neil

8,8226 gold badges55 silver badges62 bronze badges

answered Jul 30, 2013 at 22:16

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

10 Comments

dartdog Over a year ago

Thank you for your reply...Don't seem to be getting there Tried table['SUBDIVISION']str.startswith('INVERNESS', na=False) and got table['SUBDIVISION']str.startswith('INVERNESS', na=False) ^ SyntaxError: invalid syntax Wondering if I did not import something essential? I don't get it since my straight == condition works fine

dartdog Over a year ago

And if I try table.loc[table['SUBDIVISION'].str.startswith('INVERNESS', na=False)] I get a good result!! But I still don't get what is wrong with the prior attempts?

Andy Hayden Over a year ago

@dartdog you were missing a dot. please include a small subset of the data which demonstrates the problem (it seems hard to believe :s)

dartdog Over a year ago

Sorry the data is 27 cols long, kind of unwieldy even to post a clip. I tried this> table['SUBDIVISION'].str.startswith('INVERNESS',na='False') with another '.' and the comparison came out bad (all selected false) I still don't get why my original syntax failed since I thought I was following the documentation.

Andy Hayden Over a year ago

@dartdog presumably it's only the SUBDIVISION column which is relevant, so just paste that.

|

Vinoj John Hosan · Accepted Answer · 2018-03-25 16:31:34Z

36

To retrieve all the rows which startwith required string

dataFrameOut = dataFrame[dataFrame['column name'].str.match('string')]

To retrieve all the rows which contains required string

dataFrameOut = dataFrame[dataFrame['column name'].str.contains('string')]

answered Mar 25, 2018 at 16:31

Vinoj John Hosan

6,9232 gold badges44 silver badges38 bronze badges

1 Comment

Mr. Lance E Sloan Over a year ago

Why use the str.match() function to determine whether a value starts with a specific string when you could use str.startswith()? str.match() is for matching a value against a regular expression. If you don't need a regular expression, using that function may make your code slower than necessary.

Saurabh · Accepted Answer · 2020-01-21 05:25:54Z

17

Using startswith for a particular column value

df  = df.loc[df["SUBDIVISION"].str.startswith('INVERNESS', na=False)]

answered Jan 21, 2020 at 5:25

Saurabh

7,8934 gold badges50 silver badges46 bronze badges

Comments

AleAve81 · Accepted Answer · 2019-12-22 08:18:57Z

6

You can use apply to easily apply any string matching function to your column elementwise.

table2=table[table['SUBDIVISION'].apply(lambda x: x.startswith('INVERNESS'))]

this assuming that your "SUBDIVISION" column is of the correct type (string)

Edit: fixed missing parenthesis

edited Dec 22, 2019 at 8:18

answered May 10, 2019 at 8:48

AleAve81

2853 silver badges8 bronze badges

1 Comment

avirr Over a year ago

This worked for me once I added another closing bracket table2=table[table['SUBDIVISION'].apply(lambda x: x.startswith('INVERNESS')]]

rachwa · Accepted Answer · 2022-05-09 19:54:39Z

4

This can also be achieved using query:

table.query('SUBDIVISION.str.startswith("INVERNESS").values')

answered May 9, 2022 at 19:54

rachwa

2,3901 gold badge21 silver badges20 bronze badges

Collectives™ on Stack Overflow

pandas select from Dataframe using startswith

5 Answers 5

10 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

10 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related