Pandas Dataframe from nested dictionary of pandas dataframes

Question

I have a dictionary with keys of 2 levels, and values at the second level being dataframes:

my_dict = {
           'elem1':{'day1': pd.DataFrame(columns=['Col1', 'Col2']),
                    'day2': pd.DataFrame(columns=['Col1', 'Col2'])
                   },
           'elem2':{'day1': pd.DataFrame(columns=['Col1', 'Col2']),
                    'day2': pd.DataFrame(columns=['Col1', 'Col2'])
                    'day3': pd.DataFrame(columns=['Col1', 'Col2'])
                   }
          }

How do I convert this to a multi-index pandas dataframe of the form:

                 Col1    Col2
elem1    day1    ...      ...
         day2    ...      ...
elem2    day1    ...      ...
         day2    ...      ...

I have looked through these answers but am unable to stitch together a solution:

jezrael · Accepted Answer · 2020-11-03T10:47:05.383

Idea is create tuples by both keys and pass to concat, third level of MultiIndex is created from index values of original DataFrames, if necessary you can remove it:

my_dict = {
           'elem1':{'day1': pd.DataFrame(1, columns=['Col1', 'Col2'], index=[1,2]),
                    'day2': pd.DataFrame(2, columns=['Col1', 'Col2'], index=[1,2])
                   },
           'elem2':{'day1': pd.DataFrame(3, columns=['Col1', 'Col2'], index=[1,2]),
                    'day2': pd.DataFrame(4, columns=['Col1', 'Col2'], index=[1,2]),
                    'day3': pd.DataFrame(5, columns=['Col1', 'Col2'], index=[1,2])
                   }
          }

d = {(k1, k2): v2 for k1, v1 in my_dict.items() for k2, v2 in v1.items()}
print (d)
{('elem1', 'day1'):    Col1  Col2
1     1     1
2     1     1, ('elem1', 'day2'):    Col1  Col2
1     2     2
2     2     2, ('elem2', 'day1'):    Col1  Col2
1     3     3
2     3     3, ('elem2', 'day2'):    Col1  Col2
1     4     4
2     4     4, ('elem2', 'day3'):    Col1  Col2
1     5     5
2     5     5}

df = pd.concat(d, sort=False)
print (df)
              Col1  Col2
elem1 day1 1     1     1
           2     1     1
      day2 1     2     2
           2     2     2
elem2 day1 1     3     3
           2     3     3
      day2 1     4     4
           2     4     4
      day3 1     5     5
           2     5     5

df = pd.concat(d, sort=False).reset_index(level=2, drop=True)
print (df)
            Col1  Col2
elem1 day1     1     1
      day1     1     1
      day2     2     2
      day2     2     2
elem2 day1     3     3
      day1     3     3
      day2     4     4
      day2     4     4
      day3     5     5
      day3     5     5

Great answer, I just had to modify the line: `df = pd.concat(d, sort=False)` since I wanted a particular column upfront — dsForev, Nov 03 '20 at 10:42

score 1 · Answer 2 · answered Nov 03 '20 at 09:24

Try like this:

my_dict = {
           'elem1':{'day1': pd.DataFrame(columns=['Col1', 'Col2']),
                    'day2': pd.DataFrame(columns=['Col1', 'Col2'])
                   },
           'elem2':{'day1': pd.DataFrame(columns=['Col1', 'Col2']),
                    'day2': pd.DataFrame(columns=['Col1', 'Col2'])
                    'day3': pd.DataFrame(columns=['Col1', 'Col2'])
                   }
          }
nd = {}
for x in my_dict:
  nd.update(my_dict[x])
df = pd.DataFrame(nd,index=my_dict.keys())

Pandas Dataframe from nested dictionary of pandas dataframes

2 Answers2

Linked