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
| Name | Required | Description | Default |
|---|---|---|---|
| aggfunc | No | mean | |
| columns | No | ||
| file_path | Yes | ||
| index | Yes | ||
| sheet_name | No | ||
| values | No |
Input Schema (JSON Schema)
{
"properties": {
"aggfunc": {
"default": "mean",
"title": "Aggfunc",
"type": "string"
},
"columns": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"title": "Columns"
},
"file_path": {
"title": "File Path",
"type": "string"
},
"index": {
"title": "Index",
"type": "string"
},
"sheet_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"title": "Sheet Name"
},
"values": {
"default": null,
"title": "Values",
"type": "string"
}
},
"required": [
"file_path",
"index"
],
"title": "pivot_tableArguments",
"type": "object"
}
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)}"