pythonpandasdataframepivotpivot-table

Write Dictionary to Excel

I'm trying to convert a python dictionary into an excel file. But I didn't manage to format the dataframe on a way that is how I want the excel file to look like.

I have a dict in the shape as below:

data = {
    'Key 1': {
        'PP': {'A': 105.08, 'B': 9.03, 'C': 0.12, 'D': 3.18, 'E': 0.5},
        'RP': {'A': 43.35, 'B': 6.92, 'C': -0.13, 'D': 3.03, 'E': -0.1},
        'SC': {'A': 36.15, 'B': 7.3, 'C': -0.01, 'D': 2.32, 'E': 0.34}
    },
    'Key2': {
        'PP': {'A': 616.68, 'B': 11.09, 'C': 15.47, 'D': 3.82, 'E': 12.45},
        'RP': {'A': 416.92, 'B': 7.77, 'C': 6.48, 'D': 2.78, 'E': 5.25},
        'SC': {'A': 298.54, 'B': 9.57, 'C': 13.67, 'D': 3.51, 'E': 10.67}
    }
}

And I got almost what I wanted in the output using this code:

df = pd.DataFrame.from_dict(
                            {(i,j):data[i][j]
                             for i in data.keys()
                             for j in data[i]},
                             orient = 'index'
                            )

The output looks like:

output

And I want this to look like:

desired output

Solution

  • From your current approach, you can chain unstack and post-process the columns with swaplevel and sort_index:

    df = (pd.DataFrame.from_dict(
                            {(i,j):data[i][j]
                             for i in data.keys()
                             for j in data[i]},
                             orient = 'index'
                            )
        .unstack().swaplevel(axis=1).sort_index(axis=1)
     )

    Or, change the dictionary comprehension to:

    out = pd.DataFrame.from_dict({k: {(k1, k2): v for k1, d2 in d.items()
                                  for k2, v in d2.items()}
                              for k, d in data.items()}, orient='index')

    Output:

               PP                                 RP                              SC                          
            A      B      C     D      E       A     B     C     D     E       A     B      C     D      E
Key 1  105.08   9.03   0.12  3.18   0.50   43.35  6.92 -0.13  3.03 -0.10   36.15  7.30  -0.01  2.32   0.34
Key2   616.68  11.09  15.47  3.82  12.45  416.92  7.77  6.48  2.78  5.25  298.54  9.57  13.67  3.51  10.67