python - How to aggregate unique count with pandas pivot_table

Python - How to aggregate unique count with pandas pivot_table

To aggregate unique counts using pivot_table in pandas, you can utilize the aggfunc parameter along with the pivot_table function. Here's how you can do it:

Example Scenario

Let's say you have a DataFrame with columns Category, Item, and Quantity. You want to create a pivot table where each row represents a unique Category, and the values are the count of unique Item entries within each Category.

Example DataFrame

import pandas as pd # Sample data data = { 'Category': ['A', 'A', 'B', 'B', 'A', 'B', 'A'], 'Item': ['Item1', 'Item2', 'Item1', 'Item3', 'Item2', 'Item3', 'Item1'] } df = pd.DataFrame(data) print("Original DataFrame:") print(df)

Output:

 Category Item 0 A Item1 1 A Item2 2 B Item1 3 B Item3 4 A Item2 5 B Item3 6 A Item1

Creating Pivot Table with Unique Counts

To create a pivot table that aggregates the unique count of Item for each Category:

# Pivot table to get unique count of items per category pivot_df = pd.pivot_table(df, values='Item', index='Category', aggfunc=pd.Series.nunique) print("\nPivot Table with Unique Count:") print(pivot_df)

Output:

 Item Category A 2 B 2

Explanation

  • pd.pivot_table: This function creates a pivot table from the DataFrame df.

    • values='Item': Specifies the column to aggregate (Item).

    • index='Category': Specifies the column to use as rows (Category).

    • aggfunc=pd.Series.nunique: Defines the aggregation function to use (nunique to count unique values).

In this example, pivot_df will contain the unique count of Item entries for each Category. Adjust the values, index, and aggfunc parameters according to your specific data and aggregation needs.

Additional Notes

  • If you have multiple columns and want to count unique combinations, you can pass multiple columns to the values parameter and use aggfunc=pd.Series.nunique to count unique combinations of those columns.

  • Ensure your data is appropriately formatted and the column names (Category, Item, etc.) match your actual DataFrame columns.

This approach should help you effectively aggregate unique counts using pivot_table in pandas for various analytical tasks.

Examples

  1. How to pivot table with unique count in pandas?

    • Description: Create a pivot table in pandas that aggregates unique counts for specified columns.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'] }) # Pivot table with unique count pivot_table = pd.pivot_table(df, index='A', columns='B', values='C', aggfunc=pd.Series.nunique) print(pivot_table)

  2. How to count unique values in pivot table pandas?

    • Description: Count unique values for each combination in a pivot table using pandas.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'] }) # Pivot table with unique count pivot_table = pd.pivot_table(df, index='A', columns='B', values='C', aggfunc=lambda x: x.nunique()) print(pivot_table)

  3. How to pivot table with multiple unique counts in pandas?

    • Description: Compute multiple unique counts in a pivot table across different columns using pandas.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Pivot table with multiple unique counts pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc=pd.Series.nunique) print(pivot_table)

  4. How to pivot table with unique count and sum in pandas?

    • Description: Combine unique count and sum operations in a pivot table using pandas.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Pivot table with unique count and sum pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc={'C': pd.Series.nunique, 'D': 'sum'}) print(pivot_table)

  5. How to pivot table with unique count and average in pandas?

    • Description: Compute unique counts and averages in a pivot table using pandas aggregation functions.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Pivot table with unique count and average pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc={'C': pd.Series.nunique, 'D': 'mean'}) print(pivot_table)

  6. How to pivot table with unique count and custom aggregation function in pandas?

    • Description: Apply a custom aggregation function along with unique count in a pandas pivot table.
    • Code:
      import pandas as pd # Custom aggregation function def custom_agg(series): return series.nunique() + series.sum() # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Pivot table with unique count and custom aggregation pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc={'C': pd.Series.nunique, 'D': custom_agg}) print(pivot_table)

  7. How to pivot table with unique count and percentage in pandas?

    • Description: Calculate percentages along with unique counts in a pivot table using pandas.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Function to calculate percentage def calc_percentage(x): return x.nunique() / len(x) * 100 # Pivot table with unique count and percentage pivot_table = pd.pivot_table(df, index='A', columns='B', values='C', aggfunc=calc_percentage) print(pivot_table)

  8. How to pivot table with unique count across multiple columns in pandas?

    • Description: Aggregate unique counts across multiple columns in a pandas pivot table.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Pivot table with unique count across multiple columns pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc=pd.Series.nunique) print(pivot_table)

  9. How to pivot table with unique count and total count in pandas?

    • Description: Include total counts alongside unique counts in a pandas pivot table.
    • Code:
      import pandas as pd # Sample DataFrame df = pd.DataFrame({ 'A': ['foo', 'foo', 'foo', 'bar', 'bar', 'baz'], 'B': ['one', 'one', 'two', 'two', 'one', 'two'], 'C': ['x', 'y', 'x', 'y', 'x', 'x'], 'D': [1, 2, 3, 4, 5, 6] }) # Function to calculate unique count and total count def unique_and_total_count(x): return x.nunique(), len(x) # Pivot table with unique count and total count pivot_table = pd.pivot_table(df, index='A', columns='B', values=['C', 'D'], aggfunc={'C': unique_and_total_count, 'D': unique_and_total_count}) print(pivot_table)

More Tags

substitution hosting rest-client swipe php google-analytics compiler-construction toolbar react-dom alter-column

More Programming Questions

More Tax and Salary Calculators

More Various Measurements Units Calculators

More Trees & Forestry Calculators

More Everyday Utility Calculators