python - Groupby brings only one key from Pandas dictionary

Question

I have a dictionary and want to group by day:

grouped_by_day = dict[k].groupby('Day')

After running the code, the groupby attribute groups only the last item of the dictionary.

Question:

• How to group by the same way all the items of the dictionary?

Dictionary looks like this (3 items):

{'ALUP11':             ALUP11  Return %   Day
 Data                              
 2020-09-02   23.81  0.548986  13.0
 2020-09-01   23.68  1.067008   1.0
 2020-08-31   23.43 -1.139241  31.0
 2020-08-28   23.70  1.455479  28.0
 2020-08-27   23.36 -0.680272  27.0
 
 [3484 rows x 3 columns],
 'CESP6':             CESP6  Return %   Day
 Data                             
 2020-09-02  29.38 -2.747435  13.0
 2020-09-01  30.21  0.365449   1.0
 2020-08-31  30.10 -2.336145  31.0
 2020-08-28  30.82  1.615562  28.0
 2020-08-27  30.33 -1.717434  27.0

 [3484 rows x 3 columns],
 'TAEE11':             TAEE11  Return %   Day
 Data                              
 2020-09-02   28.33 -0.770578  13.0
 2020-09-01   28.55  1.205246   1.0
 2020-08-31   28.21 -0.669014  31.0
 2020-08-28   28.40  0.459851  28.0
 2020-08-27   28.27  0.354988  27.0

 [3484 rows x 3 columns]}

Expected result:

Answer 1

data -

{'ALUP11': {'Data': ['2020-08-13',
   '2020-09-01',
   '2020-08-31',
   '2020-08-28',
   '2020-08-27'],
  'ALUP11': [23.81, 23.68, 23.43, 23.7, 23.36],
  'Return %': [0.548986, 1.067008, -1.139241, 1.455479, -0.680272],
  'Day': [13.0, 1.0, 31.0, 28.0, 27.0]},
 'CESP6': {'Data': ['2020-08-13',
   '2020-09-01',
   '2020-08-31',
   '2020-08-28',
   '2020-08-27'],
  'CESP6': [29.38, 30.21, 30.1, 30.82, 30.33],
  'Return %': [-2.747435, 0.365449, -2.336145, 1.615562, -1.717434],
  'Day': [13.0, 1.0, 31.0, 28.0, 27.0]}}

Now convert it to a dataframe -

# create an empty dataframe
df = pd.DataFrame()
for key in dict_of_dict.keys():
    # create a temporary dataframe
    test_df = pd.DataFrame.from_dict(dict_of_dict[key])
    # drop the data column
    test_df.drop("Data", axis=1, inplace=True)
    # concat the test dataframne along the column axis
    df = pd.concat([df,test_df], axis=1)

print(df)
    ALUP11  Return %    Day CESP6   Return %    Day
0   23.81   0.548986    13.0    29.38   -2.747435   13.0
1   23.68   1.067008    1.0 30.21   0.365449    1.0
2   23.43   -1.139241   31.0    30.10   -2.336145   31.0
3   23.70   1.455479    28.0    30.82   1.615562    28.0
4   23.36   -0.680272   27.0    30.33   -1.717434   27.0

# get the dates 
a_list = dict_of_dict["ALUP11"]["Data"]
# set the dates as the index
df.set_index([pd.Index(a_list)])

 
print(df)

            ALUP11  Return %    Day   CESP6    Return %    Day
2020-08-13  23.81   0.548986    13.0    29.38   -2.747435   13.0
2020-09-01  23.68   1.067008    1.0    30.21    0.365449    1.0
2020-08-31  23.43   -1.139241   31.0    30.10   -2.336145   31.0
2020-08-28  23.70   1.455479    28.0    30.82   1.615562    28.0
2020-08-27  23.36   -0.680272   27.0    30.33   -1.717434   27.0