GitHub user dosubot[bot] added a comment to the discussion: superset api
<!-- Answer --> You have two main options to pivot data on the backend in Python rather than in the frontend: **Option 1: Use Post-Processing Operations (Easiest)** Superset's chart data API supports built-in [post-processing operations](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/utils/pandas_postprocessing/__init__.py) that run on the backend after the query executes. You can include a `pivot` operation in your query request: ```json { "datasource": {"id": 1, "type": "table"}, "queries": [{ "columns": ["category", "region"], "metrics": ["sales"], "post_processing": [ { "operation": "pivot", "options": { "index": ["region"], "columns": ["category"], "aggregates": { "total_sales": {"operator": "sum", "column": "sales"} } } } ] }] } ``` The pivoting is done in Python via [pandas.pivot_table()](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/utils/pandas_postprocessing/pivot.py) before returning data to the frontend. **Option 2: Create a Custom API Endpoint (More Flexible)** For fully custom logic, [create your own Flask API endpoint](https://github.com/apache/superset/discussions/37144): ```python from flask_appbuilder.api import BaseApi, expose from superset import appbuilder class MyPivotApi(BaseApi): resource_name = "my_pivot" @expose("/data", methods=["POST"]) def get_pivoted_data(self): data = request.json # Query database, apply your custom Python pivoting logic df_pivoted = your_custom_pivot_function(df) return self.response(200, data=df_pivoted.to_dict()) appbuilder.add_api(MyPivotApi) ``` This gives you complete control over the Python transformation logic and exposes it at `/api/v1/my_pivot/data`. **Note:** The existing [Pivot Table plugin does frontend pivoting](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.js) for interactivity, but there's also [backend pivot support](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/charts/client_processing.py) for report generation. If you're building a custom plugin, you can specify post-processing in your plugin's [buildQuery function](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery.ts) to have the backend handle the pivoting. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/806a1de0-d88a-4c3a-b607-caf2d8033508?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=You%20have%20two%20main%20options%20to%20pivot%20data%20on%20the%20backend%20in%20Python%20rather%20than%20in%20the%20frontend%3A%0A%0A%2A%2AOption%201%3A%20Use%20Post-Processing%20Operations%20%28Easiest%29%2A%2A%0A%0ASuperset%27s%20chart%20data%20API%20supports%20built-in%20%5Bpost-processing%20operations%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/utils/pandas_postprocessing/__init__.py%29%20that%20run%20on%20the%20backend%20after%20the%20query%20executes.%20You%20can%20include%20a%20%60pivot%60%20operation%20in%20your%20query%20request%3A%0A%0A%60%60%60json%0A%7B%0A%20%20%22datasource%22%3A%20%7B%22id%22%3A%201%2C%20%22type%22%3A%20%22table%22%7D%2C%0A%20%20%22queries%22%3A%20%5B%7B%0A%20%20%20% 20%22columns%22%3A%20%5B%22category%22%2C%20%22region%22%5D%2C%0A%20%20%20%20%22metrics%22%3A%20%5B%22sales%22%5D%2C%0A%20%20%20%20%22post_processing%22%3A%20%5B%0A%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%22operation%22%3A%20%22pivot%22%2C%0A%20%20%20%20%20%20%20%20%22options%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%22index%22%3A%20%5B%22region%22%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%22columns%22%3A%20%5B%22category%22%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%22aggregates%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22total_sales%22%3A%20%7B%22operator%22%3A%20%22sum%22%2C%20%22column%22%3A%20%22sales%22%7D%0A%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%5D%0A%20%20%7D%5D%0A%7D%0A%60%60%60%0A%0AThe%20pivoting%20is%20done%20in%20Python%20via%20%5Bpandas.pivot_table%28%29%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/utils/pandas_postprocessing/pivot.py%29%20be fore%20returning%20data%20to%20the%20frontend.%0A%0A%2A%2AOption%202%3A%20Create%20a%20Custom%20API%20Endpoint%20%28More%20Flexible%29%2A%2A%0A%0AFor%20fully%20custom%20logic%2C%20%5Bcreate%20your%20own%20Flask%20API%20endpoint%5D%28https%3A//github.com/apache/superset/discussions/37144%29%3A%0A%0A%60%60%60python%0Afrom%20flask_appbuilder.api%20import%20BaseApi%2C%20expose%0Afrom%20superset%20import%20appbuilder%0A%0Aclass%20MyPivotApi%28BaseApi%29%3A%0A%20%20%20%20resource_name%20%3D%20%22my_pivot%22%0A%20%20%20%20%0A%20%20%20%20%40expose%28%22/data%22%2C%20methods%3D%5B%22POST%22%5D%29%0A%20%20%20%20def%20get_pivoted_data%28self%29%3A%0A%20%20%20%20%20%20%20%20data%20%3D%20request.json%0A%20%20%20%20%20%20%20%20%23%20Query%20database%2C%20apply%20your%20custom%20Python%20pivoting%20logic%0A%20%20%20%20%20%20%20%20df_pivoted%20%3D%20your_custom_pivot_function%28df%29%0A%20%20%20%20%20%20%20%20return%20self.response%28200%2C%20data%3Ddf_pivoted.to_dict%28%29%29%0A%0Aappbuilder.add_a pi%28MyPivotApi%29%0A%60%60%60%0A%0AThis%20gives%20you%20complete%20control%20over%20the%20Python%20transformation%20logic%20and%20exposes%20it%20at%20%60/api/v1/my_pivot/data%60.%0A%0A%2A%2ANote%3A%2A%2A%20The%20existing%20%5BPivot%20Table%20plugin%20does%20frontend%20pivoting%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.js%29%20for%20interactivity%2C%20but%20there%27s%20also%20%5Bbackend%20pivot%20support%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/charts/client_processing.py%29%20for%20report%20generation.%20If%20you%27re%20building%20a%20custom%20plugin%2C%20you%20can%20specify%20post-processing%20in%20your%20plugin%27s%20%5BbuildQuery%20function%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset-frontend/plugins/plugin-chart-pivot-table/src/plugin/buildQuery .ts%29%20to%20have%20the%20backend%20handle%20the%20pivoting.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/38231) GitHub link: https://github.com/apache/superset/discussions/38231#discussioncomment-15918757 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
