Invalid Syntax on 'column name' When Dropping Rows based on Column Values

Question

So I'm dropping rows based on column values. My df contains two specific columns that I'm filtering: ['Exclude', 'Lost Flag']. Both columns contain binary values. I dropped all rows where "Exclude" was 1 via:

df = df[df.Exclude !=1]

All is well. However, when I tried to do the same thing with "Lost Flag":

df = df[df.Lost Flag !=1]

I get SyntaxError on the column name. I've triple checked the column name, tried underscore, no space, and tried escaping the space using ``. Then I tried pd.query:

df = df.query('Lost Flag !=1')

And I got the same syntax error. I feel like I'm missing something obvious.

Lots of operations don't support names with spaces, just like the dot notation. In any case, it's probably better to use df["col name"] than the dot notation for columns — Nathan Furnal
– Nathan Furnal, Commented Jan 7, 2020 at 17:49
I would agree that it's always better to use df['col name'] for accessing columns, whether there are spaces or not. Unless you have space limitations on your script file for some unknown reason, changing to this notation can help resolve a whole lot of issues I've come across — G. Anderson
– G. Anderson, Commented Jan 7, 2020 at 18:11

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

try with a ` for space:

df.query('`Lost Flag`!=1')

From docs:

>>> df = pd.DataFrame({'A': range(1, 6),
...                    'B': range(10, 0, -2),
...                    'C C': range(10, 5, -1)})
>>> df
   A   B  C C
0  1  10   10
1  2   8    9
2  3   6    8
3  4   4    7
4  5   2    6

For columns with spaces in their name, you can use backtick quoting.

>>> df.query('B == `C C`')
   A   B  C C
0  1  10   10

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 7, 2020 at 17:46

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

3 Comments

anky Over a year ago

@awhit may be a pandas upgrade is required to 0.25.0 , i just tried df.query("C C!=10") and it works

Scott Boston Over a year ago

+1 wow.... this is why I come to SO... and why I should read completely the "What's new" in every pandas release. OI just learned about "back ticking" nice.

anky Over a year ago

@ScottBoston I am glad one of my answer helped you since many of your's helped me:) bdw, it may interest you to know that 1.0 is coming soon :)

Collectives™ on Stack Overflow

Invalid Syntax on 'column name' When Dropping Rows based on Column Values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related