filter a pivot table pandas

Question

I am trying to filter a pivot table based on adding filter to pandas pivot table but it doesn't work.

maintenance_part_consumptionDF[(maintenance_part_consumptionDF.Asset == 'A006104') & (maintenance_part_consumptionDF.Reason == 'R565')].pivot_table(
    values=["Quantity", "Part"],
    index=["Asset"],
    columns=["Reason"],
    aggfunc={"Quantity": np.sum, "Part": lambda x: len(x.unique())},
    fill_value=0,
)

But shows, TypeError: pivot_table() got multiple values for argument 'values'

Update

Creation of the pivot table:

import numpy as np
maintenance_part_consumption_pivottable_part = pd.pivot_table(
    maintenance_part_consumptionDF,
    values=["Quantity", "Part"],
    index=["Asset"],
    columns=["Reason"],
    aggfunc={"Quantity": np.sum, "Part": lambda x: len(x.unique())},
    fill_value=0,
)

maintenance_part_consumption_pivottable_part.head(2)

When I slice it manually:

maintenance_part_consumption_pivottable_partDF=pd.DataFrame(maintenance_part_consumption_pivottable_part)
maintenance_part_consumption_pivottable_partDF.iloc[15,[8]]

I get this output:

Reason Part R565 38 Name: A006104, dtype: int64

Which is the exact output I need. But I don't want to do this way with iloc because it's more mechanical I have to count the number of y row indexes or/and x row indexes before getting to the result "38".

Hint: If I could take the asset description itsel and also for the reason from the table like it's asked on the question below.

How many unique parts were used for the asset A006104 for the failure reason R565?

Sorry, I wanted to upload the table via an image to make it more realistic but I am not allowed.

The error is telling you that `values` is list of column labels (`values=["Quantity", "Part"]`) while it should be only a single column. Why are you passing multiple columns? It doesn't make sense. What you are trying to accomplish? What should be the expected output? When asking for help always provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) so people can easily understand and reproduce the problem. — Rodalm, Apr 15 '22 at 10:27
Thank you, I just read it: "minimal reproducible example". And I didn't catch it at the start-off but I understand part of the concern now. I should've not passed multiple columns while it asked for one. am going to add up the accomplishment to help you understand Thanks for the remark and to share your precious time for highlighting me. — Offori, Apr 17 '22 at 04:48

score 0 · Answer 1 · answered Apr 16 '22 at 02:08

If you read the documentation for DataFrame.pivot_table, the first parameter is values, so in your code:

.pivot_table(
    maintenance_part_consumptionDF,  # this is `values`
    values=["Quantity", "Part"],     # this is also `values`
    ...
)

Simply drop the furst argument:

.pivot_table(
    values=["Quantity", "Part"],
    index=["Asset"],
    columns=["Reason"],
    aggfunc={"Quantity": np.sum, "Part": lambda x: len(x.unique())},
    fill_value=0,
)

There is also a closely related function: pd.pivot_table whose first parameter is a dataframe. Don't mix up the two

filter a pivot table pandas

1 Answers1