pandas:How to query multi-level column dataframe?

Question

Background:

When I do aggregate, I will sometimes end up with multi-level column dataframe, something like this:

so I need to query the data with multi-level columns.

Here is some dummy data

df=pd.DataFrame({'a':[1,2,3],'b':[4,5,6]})
columns=[('c','a'),('b','')]
df.columns=pd.MultiIndex.from_tuples(columns)
df

My question: how to query the data like b > 0, and (c,a) > 0?

I trieddf.query("b > 0 "), df.query("(c,a) > 0 "), but it returns UndefinedVariableError: name 'b' is not defined.

I also tried to search around, but didn't find things relevant, did I miss anything?

Thanks

Answer 1

You can use backticks for this:

df.query("`('c', 'a')` > 0 & `('b', '')` > 0")