adding filter to pandas pivot table

Question

I would like to add a filtering condition to a pivot table, like this:

(Select the values of v2 equal to 'A')

pd.pivot_table(df,index=['v1'],columns=['v2'=='A'],values=['v3'],aggfunc='count')

Is that possible?

Solution 1

This is an extension of Grr's answer.

Using their suggestion:

pd.pivot_table(df[df.v3 == some_value], index='v1', columns='A', values='v3', aggfunc='count')

Produces an error:

"TypeError: pivot_table() got multiple values for argument 'values'"

I made a slight tweak, and it works for me:

df[df.v3 == some_value].pivot_table(index='v1', columns='A', values='v3', aggfunc='count')

For adding multiple filters: Use &, | operators with a set of () to specify the priority. Using and,or results in an error.

df[(df.v3 == some_value) & (df.v4 == some_value)].pivot_table(index='v1', columns='A', values='v3', aggfunc='count')

Solution 2

If you want to filter by columns you could just pass a single column name, or list of names. For example:

pd.pivot_table(df, index='v1', columns='A', values='v3', aggfunc='count')
pd.pivot_table(df, index='v1', columns=['A', 'B', 'C'], values='v3', aggfunc='count')    

If you want to filter by values you would just filter the DataFrame. For example:

pd.pivot_table(df[df.v3 == some_value], index='v1', columns='A', values='v3', aggfunc='count')