pivot_table
Transform Excel data into pivot tables by specifying index, columns, values, and aggregation functions. Ideal for summarizing and analyzing spreadsheet information efficiently.
Instructions
Create a pivot table from Excel data.
Args:
file_path: Path to the Excel file
index: Column to use as the pivot table index
columns: Optional column to use as the pivot table columns
values: Column to use as the pivot table values
aggfunc: Aggregation function ('mean', 'sum', 'count', etc.)
sheet_name: Name of the sheet to pivot (for Excel files)
Returns:
Pivot table as string
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| aggfunc | No | mean | |
| columns | No | ||
| file_path | Yes | ||
| index | Yes | ||
| sheet_name | No | ||
| values | No |
Implementation Reference
- mcp_excel_server/server.py:412-477 (handler)The main handler function for the 'pivot_table' tool. It reads data from an Excel or compatible file, configures pivot table parameters based on input arguments, maps string aggregation functions to numpy/pandas functions, creates the pivot table using pd.pivot_table, and returns it as a string representation. The @mcp.tool() decorator automatically registers this function as an MCP tool named 'pivot_table'.@mcp.tool() def pivot_table(file_path: str, index: str, columns: Optional[str] = None, values: str = None, aggfunc: str = "mean", sheet_name: Optional[str] = None) -> str: """ Create a pivot table from Excel data. Args: file_path: Path to the Excel file index: Column to use as the pivot table index columns: Optional column to use as the pivot table columns values: Column to use as the pivot table values aggfunc: Aggregation function ('mean', 'sum', 'count', etc.) sheet_name: Name of the sheet to pivot (for Excel files) Returns: Pivot table as string """ try: # Read file _, ext = os.path.splitext(file_path) ext = ext.lower() read_params = {} if ext in ['.xlsx', '.xls', '.xlsm'] and sheet_name is not None: read_params["sheet_name"] = sheet_name if ext in ['.xlsx', '.xls', '.xlsm']: df = pd.read_excel(file_path, **read_params) elif ext == '.csv': df = pd.read_csv(file_path) elif ext == '.tsv': df = pd.read_csv(file_path, sep='\t') elif ext == '.json': df = pd.read_json(file_path) else: return f"Unsupported file extension: {ext}" # Configure pivot table params pivot_params = {"index": index} if columns: pivot_params["columns"] = columns if values: pivot_params["values"] = values # Map string aggfunc to actual function if aggfunc == "mean": pivot_params["aggfunc"] = np.mean elif aggfunc == "sum": pivot_params["aggfunc"] = np.sum elif aggfunc == "count": pivot_params["aggfunc"] = len elif aggfunc == "min": pivot_params["aggfunc"] = np.min elif aggfunc == "max": pivot_params["aggfunc"] = np.max else: return f"Unsupported aggregation function: {aggfunc}" # Create pivot table pivot = pd.pivot_table(df, **pivot_params) return pivot.to_string() except Exception as e: return f"Error creating pivot table: {str(e)}"