pivot_table
Create pivot tables to group and aggregate data from Excel or CSV files. Use this tool to summarize information by categories and calculate totals, averages, counts, minimums, or maximums.
Instructions
Create pivot table with grouping and aggregation
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| filePath | Yes | Path to the CSV or Excel file | |
| groupBy | Yes | Column to group by | |
| aggregateColumn | Yes | Column to aggregate | |
| operation | Yes | Aggregation operation | |
| sheet | No | Sheet name for Excel files (optional) |
Implementation Reference
- src/handlers/analytics.ts:237-320 (handler)The core handler function that implements the pivot_table tool logic: reads data from file/sheet, groups by specified column, aggregates numeric values in another column using sum/average/count/min/max, sorts results descending by aggregate value, and returns structured JSON response.async pivotTable(args: ToolArgs): Promise<ToolResponse> { const { filePath, groupBy, aggregateColumn, operation, sheet } = args; const data = await readFileContent(filePath, sheet); if (data.length <= 1) { throw new Error('File has no data rows'); } const groupByIndex = isNaN(Number(groupBy)) ? data[0].indexOf(groupBy) : Number(groupBy); const aggIndex = isNaN(Number(aggregateColumn)) ? data[0].indexOf(aggregateColumn) : Number(aggregateColumn); if (groupByIndex === -1 || aggIndex === -1) { throw new Error('One or both columns not found'); } // Group data const groups: Record<string, number[]> = {}; for (let i = 1; i < data.length; i++) { const groupKey = String(data[i][groupByIndex]); const value = Number(data[i][aggIndex]); if (!groups[groupKey]) { groups[groupKey] = []; } if (!isNaN(value)) { groups[groupKey].push(value); } } // Calculate aggregations const results: Array<{ group: string, value: number, count: number }> = []; for (const [group, values] of Object.entries(groups)) { if (values.length === 0) continue; let result: number; switch (operation) { case 'sum': result = values.reduce((a: number, b: number) => a + b, 0); break; case 'average': result = values.reduce((a: number, b: number) => a + b, 0) / values.length; break; case 'count': result = values.length; break; case 'min': result = Math.min(...values); break; case 'max': result = Math.max(...values); break; default: result = 0; } results.push({ group, value: Math.round(result * 100) / 100, count: values.length }); } // Sort by value descending results.sort((a, b) => b.value - a.value); return { content: [ { type: 'text', text: JSON.stringify({ success: true, pivotTable: { groupBy: data[0][groupByIndex], aggregateColumn: data[0][aggIndex], operation, totalGroups: results.length, results } }, null, 2), }, ], }; }
- src/index.ts:362-392 (schema)The input schema definition for the pivot_table tool, defining parameters: filePath (required), groupBy, aggregateColumn, operation (sum/average/count/min/max), and optional sheet.{ name: 'pivot_table', description: 'Create pivot table with grouping and aggregation', inputSchema: { type: 'object', properties: { filePath: { type: 'string', description: 'Path to the CSV or Excel file', }, groupBy: { type: 'string', description: 'Column to group by', }, aggregateColumn: { type: 'string', description: 'Column to aggregate', }, operation: { type: 'string', description: 'Aggregation operation', enum: ['sum', 'average', 'count', 'min', 'max'], }, sheet: { type: 'string', description: 'Sheet name for Excel files (optional)', }, }, required: ['filePath', 'groupBy', 'aggregateColumn', 'operation'], }, },
- src/index.ts:1227-1228 (registration)The dispatch/registration in the main MCP server request handler switch statement, routing 'pivot_table' calls to AnalyticsHandler.pivotTable method.case 'pivot_table': return await this.analyticsHandler.pivotTable(toolArgs);