Facing an issue while creating the pivot table using Pandas with Data frames. tried with xlwings , but getting different error regarding number format

Question

My goal is to create Pivot table in the below format using Python.

.

We need to create pivot table in above hierarchy. I tried the below code using Pandas, but didn't find any solution to recreate the required hierarchy.
Code for reference below;

`df = pd.pivot_table(df, index=['ROC | Channel Type', 'Revised Request Type', 'Reference Number', 'ROC | Country name'], values=['Pre Production Actual', 'Pre-Prod Target Tat'])`

I used xlwings as well using Automate Excel with Python Pivot Table, but I'm getting Number Format issues. I tried using all these number formats Excel Number Formats, but this didn't help.

Error text for reference;

line 32077, in AddDataField
    ret = self._oleobj_.InvokeTypes(2122, LCID, 1, (9, 0), ((9, 1), (12, 17), (12, 17)),Field
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

My goal is to create table by adding multiple rows and values using Python.
Please let me know If I want to update anything here.

Answer 1

Issue fixed. pt_fields = [['Pre Production Actual', 'Pre Production Actual', win32c.xlSum, '0'], ['Pre-Prod Target Tat', 'Pre-Prod Target Tat', win32c.xlSum, '0']] While providing values, we need to give different names for source and target fields. pt_fields = [['Pre Production Actual', ' sum Pre Production Actual', win32c.xlSum, '0'], ['Pre-Prod Target Tat', 'sum Pre-Prod Target Tat', win32c.xlSum, '0']]. This change filxed my issue.