Combining nested and and outside dictionaries to make a dataframe Pandas Python-CodePudding

The code below is giving an error below, how would I be able to make a modular function that combines the outside and the inside dictionary values and makes a pandas dataframe out of it?

Code:

import pandas as pd 
import numpy as np 

a = {'Long and Short PnL': np.array([  0.        ,   0.        ,   0.        ,   0.        ,1
          0.        ,   0.        ,   0.        ,   0.        ,2
          0.        ,   0.        ,   0.        ,   0.        ,3
          0.        ,   0.        ,   0.        ,   0.        ,4
          0.        ,   0.        ,   0.        ,   0.        ,5
          0.        ,   0.        ,   0.        ,   0.        ,6
         -8.31004521,   0.        ,   0.        , 315.29767864,7
        -13.49807721, -28.64143208, -16.60131942,   0.        ]),
     'Long and Short Max Upside PnL': np.array([  0.  ,   0.  ,   0.  ,   0.  ,
          0.  ,   0.  ,   0.  ,   0.  ,
          0.  ,   0.  ,   0.  ,   0.  ,  
          0.  ,   0.  ,   0.  ,   0.  ,
          0.  ,   0.  ,   0.  ,   0.  ,  
          0.  ,   0.  ,   0.  ,   0.  ,
          0.85,   0.  ,   0.  , 602.5 , 
          25.7 ,   0.  ,  96.45, 120.8]),
     'Long and Short Compounded': {'Last Capitals': np.array([  0.        ,   0.        ,   0.        ,   0.        ,
           0.        ,   0.        ,   0.        ,   0.        ,
           0.        ,   0.        ,   0.        ,   0.        ,
           0.        ,   0.        ,   0.        ,   0.        ,
           0.        ,   0.        ,   0.        ,   0.        ,
           0.        ,   0.        ,   0.        ,   0.        ,
          91.86164693,   0.        ,   0.        , 329.05735514,
         401.83251206,  79.712739  ,  75.76562339,   0.        ]),
    'Max Drawdown': np.array([  0.        ,   0.        ,   0.        ,   0.        ,
               0.        ,   0.        ,   0.        ,   0.        ,
               0.        ,   0.        ,   0.        ,   0.        ,
               0.        ,   0.        ,   0.        ,   0.        ,
               0.        ,   0.        ,   0.        ,   0.        ,
               0.        ,   0.        ,   0.        ,   0.        ,
              91.86164693,   0.        ,   0.        ,  96.15319621,
             401.83251206,  79.712739  ,  75.76562339,   0.        ])}}

display(pd.DataFrame(a))

Error Code:

ValueError: All arrays must be of the same length

Expected Output:

CodePudding user response：

You can get your desired outcome in 3 steps:

(i) First the values of 'Long and Short PnL' keys include some integers (1-7) that don't appear in your desired DataFrame. These cause SyntaxError: invalid syntax. Remove those.

(ii) Use pd.json_normalize to normalize the json data into a flat table. This creates a single row DataFrame with 4 columns of lists.

(iii) Use explode to explode the lists in all columns. And rename the columns names that came from nested dicts to remove parts characters that appear before a dot.

df = pd.json_normalize(a)
df = df.explode(df.columns.tolist()).rename(columns={col:col.split('.')[1] for col in df.columns if '.' in col}).reset_index(drop=True)

Output:

   Long and Short PnL Long and Short Max Upside PnL Last Capitals Max Drawdown
0                 0.0                           0.0           0.0          0.0
1                 0.0                           0.0           0.0          0.0
2                 0.0                           0.0           0.0          0.0
3                 0.0                           0.0           0.0          0.0
4                 0.0                           0.0           0.0          0.0
5                 0.0                           0.0           0.0          0.0
6                 0.0                           0.0           0.0          0.0
7                 0.0                           0.0           0.0          0.0
8                 0.0                           0.0           0.0          0.0
9                 0.0                           0.0           0.0          0.0
10                0.0                           0.0           0.0          0.0
11                0.0                           0.0           0.0          0.0
12                0.0                           0.0           0.0          0.0
13                0.0                           0.0           0.0          0.0
14                0.0                           0.0           0.0          0.0
15                0.0                           0.0           0.0          0.0
16                0.0                           0.0           0.0          0.0
17                0.0                           0.0           0.0          0.0
18                0.0                           0.0           0.0          0.0
19                0.0                           0.0           0.0          0.0
20                0.0                           0.0           0.0          0.0
21                0.0                           0.0           0.0          0.0
22                0.0                           0.0           0.0          0.0
23                0.0                           0.0           0.0          0.0
24          -8.310045                          0.85     91.861647    91.861647
25                0.0                           0.0           0.0          0.0
26                0.0                           0.0           0.0          0.0
27         315.297679                         602.5    329.057355    96.153196
28         -13.498077                          25.7    401.832512   401.832512
29         -28.641432                           0.0     79.712739    79.712739
30         -16.601319                         96.45     75.765623    75.765623
31                0.0                         120.8           0.0          0.0