Write Pandas DataFrame into multiple tables with table names into Excel sheet-CodePudding

I have a DataFrame on the following format

 'index' |   'path'  | 'Key'  |   'Value'  

    1        L1         A          1
   1.1     L1/L11       B          2      
   1.1     L1/L11       C          2 
  1.1.2  L1/L11/L111    CA         4
   1.2     L1/L12                    
  1.2.1  L1/L12/L121    CC         6 
   ...       ...        ..        ...

I want to output these into a single sheet, where have

Excel Column A: Index
Excel Column B: Path
Excel Column C: Table consisting of Key and Value.

A table will be created for each new index. If the key or value is empty we do not create a table but simply write the path and index.

The expected output would be.

     A     |    B    |    C    |    D    |
     1          L1
                         Key      Value 
                          A         1
    1.1       L1/L11   
                         Key      Value
                          B         2
                          C         2
  1.1.2    L1/L11/L111
                         Key      Value
                         CA         4
   1.2       L1/L12 
  1.2.1   L1/L12/L121
                         Key      Value
                         CC         6 
   ...       ...         ...       ...

Has someone done something similar to this that could give some input on how this could be achieved?

Appreciate any input.

CodePudding user response：

With the help of pd.concat([df1, df2]) you are able to concatenate a dataframes before writting it to an excel file via df.to_excel().

Using your example:

import pandas as pd

df = pd.DataFrame({
    'index': ['1', '1.1', '1.1', '1.1.2', '1.2', '1.2.1'], 
    'path': ['L1', 'L1/L11', 'L1/L11', 'L1/L11/L111', 'L1/L12', 'L1/L12/L121'],
    'Key':['A', 'B', 'C', 'CA', '', 'CC'],
    'Value':['1', '2', '2', '4', '', '6'],
    })

#initialize resulting df
result = pd.DataFrame(columns = df.columns.values)

#go through each unique index 
for uniqueId in df['index'].unique():
    dfPart = df.loc[df['index'] == uniqueId]

    #append first row of columns index and path
    result = pd.concat( [result, dfPart[['index', 'path']].iloc[0:1]], ignore_index=True )

    #append columns key and value
    result = pd.concat( [result, dfPart[['Key', 'Value']]], ignore_index=True )

#write resulting df to an excel file
result.to_excel(r'test.xlsx', index=False)