Handling Columns with Special Characters in Pandas query

You might find yourself in a situation where the column names in your DataFrame contain special characters like spaces, dots, dashes, or even reserved words.

Reading such column names causes errors. In this tutorial, you will learn how to handle these columns when using Pandas query method.

 

 

The Problem with Special Characters

When importing data from external sources like CSV files or databases, you may encounter column names that contain special characters such as spaces ( ), dots (.), or dashes (-).

These characters conflict with Python’s naming conventions.

import pandas as pd
data = {
    "User ID": [101, 102, 103],
    "First-Name": ["John", "Jane", "Doe"],
    "Last.Name": ["Doe", "Doe", "Smith"]
}
df = pd.DataFrame(data)
print(df)

Output:

   User ID First-Name Last.Name
0      101       John       Doe
1      102       Jane       Doe
2      103        Doe     Smith

In this DataFrame, each column name contains at least one special character.

Attempting to access these columns using the dot notation will result in syntax errors.

# This will raise a SyntaxError
print(df.User ID)

 

Columns with Spaces

You can use backticks (` ` ) to enclose the column names, making your queries manageable.

Here’s a simple example:

filtered_data = df.query("`User ID` == 101")
print(filtered_data)

Output:

   User ID First-Name Last.Name
0      101       John       Doe

Notice how backticks are used to specify the column name, handling the space within the column name.

 

Querying Columns with Dollar Signs and Other Symbols

Column names with characters like dollar signs ($), ampersands (&), or other non-alphanumeric characters may appear in your DataFrame.

To escape a special character within a column name, use double backticks (` ` ) to enclose it.

This tells Pandas to treat the special character as a part of the column name.

Let’s extend our sample DataFrame to include a column with a dollar sign:

import pandas as pd
data = {
    "User ID": [101, 102, 103],
    "First-Name": ["John", "Jane", "Doe"],
    "Last.Name": ["Doe", "Doe", "Smith"],
    "Salary$": [50000, 60000, 70000]
}
df = pd.DataFrame(data)
print(df)

Output:

   User ID First-Name Last.Name  Salary$
0      101       John       Doe    50000
1      102       Jane       Doe    60000
2      103        Doe     Smith    70000

Now, let’s query this DataFrame to filter rows where Salary$ is greater than 55000:

filtered_data = df.query("`Salary$` > 55000")
print(filtered_data)

Output:

   User ID First-Name Last.Name  Salary$
1      102       Jane       Doe    60000
2      103        Doe     Smith    70000

 

Dealing with Reserved Words Columns

If a DataFrame column name happens to be a Python reserved word—like for, if, or and—you can still query it using the Pandas query method.

Just like with special characters, enclosing the reserved word in backticks allows you to bypass the typical naming restrictions.

Let’s add a column called for to our DataFrame to demonstrate this:

import pandas as pd
data = {
    "User ID": [101, 102, 103],
    "First-Name": ["John", "Jane", "Doe"],
    "Last.Name": ["Doe", "Doe", "Smith"],
    "Salary$": [50000, 60000, 70000],
    "for": ["A", "B", "C"]
}
df = pd.DataFrame(data)
print(df)

Output:

   User ID First-Name Last.Name  Salary$ for
0      101       John       Doe    50000   A
1      102       Jane       Doe    60000   B
2      103        Doe     Smith    70000   C

Here, the DataFrame has a column named for, which is a Python reserved word.

To query based on this column, you would use:

filtered_data = df.query("`for` == 'A'")
print(filtered_data)

Output:

   User ID First-Name Last.Name  Salary$ for
0      101       John       Doe    50000   A

 

Combining Multiple Conditions in Query

Let’s extend our DataFrame to illustrate combining multiple conditions:

import pandas as pd
data = {
    "User ID": [101, 102, 103, 104, 105],
    "First-Name": ["John", "Jane", "Doe", "Emily", "Anna"],
    "Last.Name": ["Doe", "Doe", "Smith", "Brown", "Green"],
    "Salary$": [50000, 60000, 70000, 55000, 60000]
}
df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)

Output:

Original DataFrame:
   User ID First-Name Last.Name  Salary$
0      101       John       Doe    50000
1      102       Jane       Doe    60000
2      103        Doe     Smith    70000
3      104      Emily     Brown    55000
4      105       Anna     Green    60000

Suppose you want to select rows where the Salary$ is greater than 55000, and the Last.Name is not ‘Doe’. You can use:

filtered_data = df.query("`Salary$` > 55000 and `Last.Name` != 'Doe'")
print("Filtered DataFrame:")
print(filtered_data)

Output:

Filtered DataFrame:
   User ID First-Name Last.Name  Salary$
2      103        Doe     Smith    70000
4      105       Anna     Green    60000

You can use parentheses to group conditions and make your queries even more powerful:

filtered_data = df.query("(`Salary$` > 55000 and `Last.Name` != 'Doe') or `User ID` == 101")
print("Filtered DataFrame with Grouped Conditions:")
print(filtered_data)

Output:

Filtered DataFrame with Grouped Conditions:
   User ID First-Name Last.Name  Salary$
0      101       John       Doe    50000
2      103        Doe     Smith    70000
4      105       Anna     Green    60000