Generate Excel Pivot Table from JSON Data

Incoming JSON Data to Python Operations

{
  "records": [
    {
      "group": "Group A",
      "amount": 100
    },
    {
      "group": "Group A",
      "amount": 200
    },
    {
      "group": "Group B",
      "amount": 300
    }
  ]
}

import pandas as pd
from io import BytesIO
import base64

Responsedata = []

# Read incoming JSON data
data = pycode_data.get("records", [])

# Convert JSON data into a DataFrame
df = pd.DataFrame(data)

# Create a pivot table
pivot_df = pd.pivot_table(
    df,
    index=["group"],
    values=["amount"],
    aggfunc="sum"
)

# Create an in-memory Excel file
output = BytesIO()

# Write pivot table into Excel
with pd.ExcelWriter(output, engine="openpyxl") as writer:
    pivot_df.to_excel(writer, sheet_name="Pivot Table")

# Move pointer to the beginning
output.seek(0)

# Encode workbook content into Base64
file_content = base64.b64encode(output.read()).decode()

# Prepare output response
response = {
    "file_name": "Excel_Pivot_Table.xlsx",
    "file_content": file_content,
    "mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}

# Send data to output
Responsedata.append(response)

{
  "file_name": "Excel_Pivot_Table.xlsx",
  "file_content": "<Base64 Encoded Content>",
  "mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}