pivot_table
Create pivot tables from Excel data to summarize and analyze information by grouping, aggregating, and restructuring datasets for clearer insights.
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 |
|---|---|---|---|
| file_path | Yes | ||
| index | Yes | ||
| columns | No | ||
| values | No | ||
| aggfunc | No | mean | |
| sheet_name | No |
Implementation Reference
- mcp_excel_server/server.py:412-477 (handler)The main handler function for the 'pivot_table' tool. Decorated with @mcp.tool() which registers it as an MCP tool. Reads the Excel/CSV file, prepares pivot table parameters based on inputs, maps aggregation function strings to numpy/pandas functions, creates the pivot table using pd.pivot_table, and returns it as a formatted string.@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)}"
- mcp_excel_server/server.py:412-412 (registration)The @mcp.tool() decorator on the pivot_table function serves as the registration mechanism in FastMCP, automatically registering the function as a tool named 'pivot_table' based on its definition.@mcp.tool()
- mcp_excel_server/server.py:413-429 (schema)The function signature and docstring define the input schema (parameters with types and descriptions) and output schema for the pivot_table 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 """