```html

AG-Grid-React-Series-#19-Pivoting-and-Aggregation-in-AG-Grid

In the world of data analytics and reporting, raw data often needs transformation to reveal meaningful insights. Two of the most powerful features AG-Grid offers for this purpose are Pivoting and Aggregation. When combined, these capabilities empower users to dynamically reshape and summarize large datasets directly within the grid, turning a flat table into a dynamic reporting tool.

This article, part of our AG-Grid-React series, will dive deep into how you can leverage pivoting and aggregation in your React applications, providing clear explanations and practical code examples.

Unlocking Powerful Data Analysis with AG-Grid's Pivoting and Aggregation

Imagine you have a dataset of sales transactions, including product, country, quarter, and sales amount. You might want to answer questions like:

• What are the total sales per product, broken down by country?
• How do quarterly sales compare across different product categories?
• Which country generated the highest average sales for a particular product?

Answering these questions manually would involve complex filtering and calculations. AG-Grid's pivoting and aggregation features automate this process, allowing users to drag-and-drop columns to instantly generate insightful reports.

Understanding Pivoting in AG-Grid

Pivoting in AG-Grid transforms your data by converting row data into column headers. Instead of listing each individual record, it summarizes data based on one or more "pivot" columns, creating new columns for each unique value in those pivot columns.

To enable pivoting, you typically need to:

1. Set enablePivot: true on the column definitions for columns that can be used as pivots.
2. Set pivot: true on the specific columns you want to pivot by.
3. Enable the tool panel (optional but highly recommended) so users can drag columns into the pivot drop zone.

When a column is pivoted, AG-Grid will generate new columns dynamically. For instance, if you pivot by a 'Country' column, you might see new columns like 'USA', 'Germany', 'France', etc., each containing aggregated values for that country.

Mastering Aggregation in AG-Grid

Aggregation is the process of calculating summary values (like sum, average, count, min, max) for groups of data. This works hand-in-hand with row grouping and pivoting.

To use aggregation, you need to:

1. Set aggFunc on the column definition for columns you want to aggregate.
2. Common built-in aggregation functions include: 'sum', 'avg', 'count', 'min', 'max'.
3. Aggregation typically applies to numeric columns.

For example, if you have a 'Sales' column and apply aggFunc: 'sum', AG-Grid will display the total sales for each group or pivot intersection.

Custom Aggregation Functions

AG-Grid also supports custom aggregation functions for more complex scenarios. You can provide your own function that takes an array of values and returns a single aggregated value. This allows for highly flexible data summarization.

The Synergy: Pivoting, Aggregation, and Row Grouping

Pivoting and aggregation truly shine when combined with Row Grouping. Row grouping allows you to group rows based on the values of one or more columns (e.g., group all sales by 'Product'). Aggregation then calculates summaries within these groups, and pivoting further transforms these groups into columns.

To enable row grouping:

1. Set enableRowGroup: true on columns that can be grouped.
2. Set rowGroup: true on specific columns you want to group by default.

The typical workflow is:

1. Define Row Groups: Drag columns into the 'Row Groups' drop zone.
2. Define Pivot Columns: Drag columns into the 'Pivot' drop zone.
3. Define Value Columns (Aggregations): Drag numeric columns into the 'Values' drop zone, and AG-Grid will apply the default aggFunc (usually 'sum') or the one you specified in columnDefs.

AG-Grid handles the complex calculations and rendering, presenting a hierarchical, summarized view of your data.

Putting It All Together: A React Code Example

Let's create a React component that demonstrates pivoting and aggregation. We'll set up column definitions to allow pivoting by 'Country', grouping by 'Product', and aggregating 'Sales' by sum and 'Quantity' by average.

import React, { useState, useMemo, useCallback, useRef } from 'react';
import { AgGridReact } from 'ag-grid-react';
import 'ag-grid-community/styles/ag-grid.css';
import 'ag-grid-community/styles/ag-theme-alpine.css';

const ExampleGrid = () => {
    const gridRef = useRef();

    const [rowData] = useState([
        { product: 'Laptop', country: 'USA', quarter: 'Q1', sales: 1200, quantity: 10 },
        { product: 'Keyboard', country: 'USA', quarter: 'Q1', sales: 150, quantity: 20 },
        { product: 'Mouse', country: 'USA', quarter: 'Q1', sales: 75, quantity: 15 },
        { product: 'Laptop', country: 'Germany', quarter: 'Q1', sales: 1100, quantity: 8 },
        { product: 'Keyboard', country: 'Germany', quarter: 'Q1', sales: 160, quantity: 25 },
        { product: 'Laptop', country: 'France', quarter: 'Q1', sales: 1300, quantity: 12 },
        { product: 'Mouse', country: 'France', quarter: 'Q1', sales: 80, quantity: 18 },

        { product: 'Laptop', country: 'USA', quarter: 'Q2', sales: 1250, quantity: 11 },
        { product: 'Keyboard', country: 'USA', quarter: 'Q2', sales: 170, quantity: 22 },
        { product: 'Laptop', country: 'Germany', quarter: 'Q2', sales: 1150, quantity: 9 },
        { product: 'Mouse', country: 'Germany', quarter: 'Q2', sales: 70, quantity: 14 },
        { product: 'Keyboard', country: 'France', quarter: 'Q2', sales: 180, quantity: 28 },
    ]);

    const [columnDefs] = useState([
        { field: 'product', rowGroup: true, enableRowGroup: true },
        { field: 'country', pivot: true, enablePivot: true }, // This column can be pivoted
        { field: 'quarter', enableRowGroup: true, enablePivot: true },
        { 
            field: 'sales', 
            aggFunc: 'sum',          // Sum sales for groups/pivots
            enableValue: true,       // Allow users to drag to 'Values' drop zone
            valueFormatter: p => '$' + p.value.toLocaleString()
        },
        { 
            field: 'quantity', 
            aggFunc: 'avg',          // Average quantity for groups/pivots
            enableValue: true        // Allow users to drag to 'Values' drop zone
        },
    ]);

    const defaultColDef = useMemo(() => ({
        flex: 1,
        minWidth: 100,
        sortable: true,
        resizable: true,
    }), []);

    // Configure the 'Auto Group Column' which displays the grouping structure
    const autoGroupColumnDef = useMemo(() => ({
        headerName: 'Category',
        minWidth: 200,
        cellRendererParams: {
            // Customize the group cell renderer if needed
        }
    }), []);

    return (
        <div style={{ width: '100%', height: '500px' }} className="ag-theme-alpine">
            <AgGridReact
                ref={gridRef}
                rowData={rowData}
                columnDefs={columnDefs}
                defaultColDef={defaultColDef}
                autoGroupColumnDef={autoGroupColumnDef}
                // Enable the grid's side bar for drag-and-drop grouping/pivoting/values
                sideBar={true} 
                // Enable pivoting and aggregation globally
                pivotMode={true}
            ></AgGridReact>
        </div>
    );
};

export default ExampleGrid;

Explanation of the Code Example

• rowData: A simple array of objects representing our sales data.
• columnDefs:
  • product: Set as rowGroup: true and enableRowGroup: true. This means the grid will group by product by default, and users can toggle this.
  • country: Set as pivot: true and enablePivot: true. This column will be used to create new columns dynamically based on unique country values.
  • sales: Defined with aggFunc: 'sum' and enableValue: true. This allows users to drag 'Sales' into the 'Values' drop zone, and it will sum the sales for each group/pivot intersection.
  • quantity: Defined with aggFunc: 'avg' and enableValue: true, similar to sales but performing an average.
• defaultColDef: Provides default properties for all columns.
• autoGroupColumnDef: This important prop customizes the special column that AG-Grid generates when row grouping is active. It's where the hierarchy of your groups (e.g., 'Laptop' > 'USA') will be displayed.
• sideBar={true}: Enables the AG-Grid tool panel, which is crucial for users to interactively drag columns into the 'Row Groups', 'Pivot', and 'Values' drop zones.
• pivotMode={true}: This global grid option simplifies enabling pivot and value functionalities. When pivotMode is true, any column with enablePivot: true can be used for pivoting, and any column with enableValue: true and an aggFunc can be used for aggregation.

With this setup, when the grid loads, you'll see sales data grouped by 'Product' and pivoted by 'Country', with the sum of 'Sales' and average of 'Quantity' for each combination.

Key Takeaways and Best Practices

• Interactive Exploration: The side panel (sideBar={true}) is vital for allowing users to dynamically configure pivots, groups, and aggregations.
• Performance: For very large datasets, be mindful of performance. AG-Grid is highly optimized, but complex aggregations and pivots on millions of rows might require server-side row models for optimal performance.
• User Guidance: For complex grids, consider adding tooltips or helper text to guide users on how to use the pivoting and aggregation features.
• Default Configurations: Set sensible defaults for rowGroup, pivot, and aggFunc in your columnDefs to provide a useful initial view.
• Data Types: Ensure your data types are appropriate. Aggregation functions like 'sum' or 'avg' require numeric values.

Conclusion

AG-Grid's pivoting and aggregation capabilities are game-changers for data-intensive applications. They transform a static data display into a dynamic analytical powerhouse, giving your users the tools to slice, dice, and summarize information with unparalleled flexibility. By understanding and implementing these features in your React applications, you can deliver a significantly richer and more insightful data experience.

```