how you use .query with pandas and numpy?

Question

From the file output_hc_v1.csv with headers :emplid pay_status count location deptid grade_desc date version

I would like via python save a new csv filtered on "pay_status=Active". with this code:

a1=a.query ('"pay_status" == ["Active"]')

=========================================

but this part is not working.

My full code:
import csv 
import pandas as pd
import numpy as np

df= pd.read_csv ("C:/Users/e1087353/Documents/CAPCO/HC/python_data/output_hc_v1.csv")
df.head()
a= pd.pivot_table(df, index =["location","deptid","grade_desc","pay_status"] ,values=["Count"],aggfunc=np.sum)
a1=a.query ('"pay_status" == ["Active"]')
a1.to_csv ("C:/Users/e1087353/Documents/CAPCO/HC/python_data/Output_final2.csv")

unutbu · Accepted Answer · 2016-11-17 13:35:53Z

1

You do not need to quote column names in the query string. Therefore you could use,

a1 = a.query('pay_status == "Active"')

answered Nov 17, 2016 at 13:35

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

Comments

Nickpick · Accepted Answer · 2016-11-17 13:35:28Z

0

Don't use query. To filter simply do:

a1=a[a['pay_status']=='Active']

If you create a pivot table you may want to reset the index before you filter: a=a.reset_index()

Alternatively, for more complicated queries, if you want to filter for items in a list use isin:

filter_list=['Active','Inactive','Something']
a1=a[a['pay_status'].isin(filter_list)]

edited Nov 17, 2016 at 13:35

answered Nov 17, 2016 at 13:29

Nickpick

6,67720 gold badges72 silver badges137 bronze badges

Collectives™ on Stack Overflow

how you use .query with pandas and numpy?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related