Converting dictionary to dataframe with key as column names and value of key as column values of the dataframe

Question

Converting the nested dictionary to dataframe with, dictionary keys as column names and values corresponding to those keys as column values of the dataframe.

I'm new to python and have tried several approaches but failed in achieving so, please help.

dict = {
    'sheet1': {
        'col1': ['a', 'b', 'c', 'd', 'e'],
        'col2': ['p', 'q', 'r', 's', 't'],
        'col3': ['l', 'm', 'n', 'o'],
        'col4': ['e', 'b', 'w', 't', 'b']
    },
    'sheet2': {
        'col1': ['r', 'w', 'y', 'g', 'r'],
        'col2': ['q', 'y', 'f', 'w'],
        'col3': ['w', 'g', 4, 2, 'd']
    }
}

output: 
col1    col2   col3   col4
a       p       l      e
b       q       m      b
c       r       n      w
d       s       o      t
e       t       nan    b
r       q       w      nan
w       y       g      nan
y       f       4      nan
g       w       2      nan
r      nan      d      nan

Answer 1

You can accomplish this by creating multiple dataframes from nested dictionaries, and joining them using pd.concat. For example:

>>> data = {
...     'sheet1': {'col1': [1, 2, 3, 4], 'col2': [5, 6, 7, 8]},
...     'sheet2': {'col1': [11, 12, 13, 14], 'col2': [15, 16, 17, 18]},
... }
>>> df = pd.concat([pd.DataFrame(d) for d in data.values()], ignore_index=True)
>>> df
   col1  col2
0     1     5
1     2     6
2     3     7
3     4     8
4    11    15
5    12    16
6    13    17
7    14    18