Combining nested and and outside dictionaries to make a dataframe Pandas Python
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