Let's create a sample dataframe having 3 columns and 4 rows. This dataframe is used for demonstration purpose.
import pandas as pd
df = pd.DataFrame({"col1" : range(1,5),
"col2" : ['A A','B B','A A','B B'],
"col3" : ['A A','A A','B B','B B']
})
Filter a value
In order to do reference of a variable in query, you need to use A A in column col2@.
Mention Value Explicitly
newdf = df.query("col2 == 'A A'")
Reference Method
myval1 = 'A A'
newdf = df.query("col2 == @myval1")
How to pass column name as a variable in query
Instead of filter value we are referring the column which we want to use for subetting or filtering.
myvar1 = 'col2'
newdf2 = df.query("{0} == 'A A'".format(myvar1))
{0} takes a value of variable myvar1.Incase you want to pass multiple columns as variables in query. Here we are using columns"{0} == 'A A'".format(myvar1)returns"col2 == 'A A'"
col2 and col3.
myvar1 = 'col2'
myvar2 = 'col3'
newdf2 = df.query("{0} == 'A A' & {1} == 'B B'".format(myvar1, myvar2))
"{0} == 'A A' & {1} == 'B B'".format(myvar1, myvar2) is equivalent to "col2 == 'A A' & col3 == 'B B'"
How to handle space in column name
Let's rename column col2 by including a space in between for illustration purpose.
df.rename(columns={'col2':'col 2'}, inplace = True)
By using backticks `` you can pass a column which contains space.
myvar1 = '`col 2`'
newdf = df.query("{0} == 'A A'".format(myvar1))
About Author:
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
Post a Comment