Excel Pivot Export from Pandas Dataframe

Export Pandas Dataframe to Excel Pivot:

Objective: Create a pivot table from multiple Pandas dataframes and export it to an Excel file. Pandas Dataframes:

data1_summary = pd.DataFrame({'Header': ['L1', 'L2', 'L3'],
                               'Val1': [100, 200, 300],
                               'Val2': [400, 500, 600],
                               'Val3': [700, 800, 900]})

data2_summary = pd.DataFrame({'Header': ['L5', 'L6'],
                               'Val5': [1000, 1100],
                               'Val6': [1300, 1400]})

data3_summary = pd.DataFrame({'Header': ['L7', 'L8', 'L9', 'L10'],
                               'Val7': [1900, 2000, 2100, 2200],
                               'Val8': [2900, 2300, 2400, 2800],
                               'Val9': [3500, 3600, 3700, 3900]})

Options for Export: 1. Option for Header Removal and Default Column Names:

import pandas as pd

dfs = [data1_summary,data2_summary,data3_summary]
df = pd.concat(x.drop('Header', axis=1)
               set_axis(range(len(x.columns)-1), axis=1) for x in dfs)

writer = pd.ExcelWriter('Export_Pivot.xlsx')
df.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with default column names and no "Header" column.

2. Option for Only Default Column Names:

dfs = [data1_summary,data2_summary,data3_summary]
df = pd.concat(x.set_axis(range(len(x.columns)), axis=1) for x in dfs)

writer = pd.ExcelWriter('Export_Pivot.xlsx')
df.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with default column names. The "Header" column is retained.

3. Option Using List Comprehension with concat, rename_axis, and fillna:

import pandas as pd

dfs = [data1_summary, data2_summary, data3_summary]

df = (pd.concat((x.set_index('Header') for x in dfs), axis=1)
        .rename_axis(None)
        .fillna(0, downcast='int'))

writer = pd.ExcelWriter('Export_Pivot.xlsx')
df.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with no "Header" column, default column names, and missing values filled with 0.

4. Option for Aggregating Duplicates in "Header":

dfs = [data1_summary, data2_summary, data3_summary]

df = (pd.concat((x.set_index('Header') for x in dfs), axis=1)
        .groupby('Header', sort=False)
        .sum()
        .reset_index())

writer = pd.ExcelWriter('Export_Pivot.xlsx')
df.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with "Header" column, default column names, and duplicate values in "Header" are aggregated using the sum method.

5. Option for Using concat on axis=1 and Setting id as Index:

import pandas as pd

dfs = [data1_summary, data2_summary, data3_summary]

out = (pd.concat((d.set_index('Header') for d in dfs), axis=1)
         .rename_axis(None)
      )

writer = pd.ExcelWriter('Export_Pivot.xlsx')
out.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with no "Header" column and default column names.

6. Option for Using groupby and first (or Any Aggregation Function):

dfs = [data1_summary, data2_summary, data3_summary]

out = pd.concat(dfs).groupby('Header', sort=False).first().rename_axis(None)

writer = pd.ExcelWriter('Export_Pivot.xlsx')
out.to_excel(writer, sheet_name='Pivot_Data')
writer.save()

Result:

Excel sheet with no "Header" column and default column names. Potential duplicates in "Header" are handled using the first method (or any other aggregation function).

Conclusion: This demonstration showcases various options for exporting Pandas dataframes to Excel pivot tables, catering to different scenarios and requirements.