7

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?

Grr
  • 15,553
  • 7
  • 65
  • 85
progster
  • 877
  • 3
  • 15
  • 27

4 Answers4

10

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')
JJAN
  • 777
  • 1
  • 7
  • 13
7

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')
Grr
  • 15,553
  • 7
  • 65
  • 85
  • Can I do this for two separate some_values, and add them together? I have the below working correctly for "Civilian Labor", but I want to do the same for "Labor Overhead" and add them together for a new column called "Total Labor." 'Labor_Table = CJI3[CJI3['Cost Element LANT Category Name'] == "Civilian Labor"].pivot_table(values=['Charge Amount'],index=['Project Unit ID','Fiscal Year'],columns=['Cost Element LANT Category Name'],aggfunc=sum,fill_value=0,dropna=True)' – Brian Jan 10 '21 at 14:43
1

You can use a where condition as well here:

df.where([df.v3 == some_value]).pivot_table(index='v1', columns='A', values='v3', aggfunc='count')
Samuel Philipp
  • 10,631
  • 12
  • 36
  • 56
Vishnu Dhas
  • 11
  • 2
1

This could be useful if you need to filter for more than one value:

mask = df['v2'].isin(['A', 'B', 'C'])
table = df[mask].pivot_table(
        values='v3', 
        index=['v1'],
        columns=['v2'], 
        aggfunc='count', 
    )
naccode
  • 510
  • 1
  • 8
  • 18