1

How can I use column names in pandas query or eval method when the column name has . in it. For example, the dataframe has . in its column name first.last

df = pd.DataFrame({'first.last':[10, 20, 30, 40]})

As a result I can't run the following expression df.query('first.last < 30')

Are there any ways to mitigate this or work around this ? Thanks

RTM
  • 759
  • 2
  • 9
  • 22
  • 1
    Common issue for query https://github.com/pandas-dev/pandas/issues/6508 – BENY Sep 11 '19 at 21:52
  • Thanks. Looks like there is a PR ready to merge `https://github.com/pandas-dev/pandas/pull/28215` to take care of this issue. – RTM Sep 11 '19 at 22:01
  • I used `_clean_special_characters_column_name` method available in the PR for the time being to map old column names to new column names and used the new column names in the query expression – RTM Sep 11 '19 at 22:18

2 Answers2

2

You could rename the columns. Please try:

cols = df.columns
cols = cols.map(lambda x: x.replace('.', '_') if isinstance(x, (str, unicode)) else x)
df.columns = cols
hrmnjt
  • 183
  • 7
  • Thanks @hrmnjt. But the above approach will cause collisions if there is an additional column name `first_last`. – RTM Sep 11 '19 at 22:03
  • 1
    You could choose to replace the column name with special format like `___` to avoid collision. Just trying to help here with money tricks as this is a known issue that is pending to be implemented into pandas core code. – hrmnjt Sep 11 '19 at 22:06
0

You can query the dataframe like this:

df[df['first.last'] < 30]
Jan X Marek
  • 2,464
  • 2
  • 18
  • 26
  • That's true. I am trying to use `query` or `eval` method for the step. – RTM Sep 11 '19 at 22:00
  • According to the documentation (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.eval.html#pandas.eval), the string needs to be a valid python expression, which 'first.last < 30' is not, because '.' has a special meaning. You can check this question: https://stackoverflow.com/questions/40045545/pandas-query-string-where-column-name-contains-special-characters The second answer offers a nice hack. – Jan X Marek Sep 11 '19 at 22:17