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

user_16154762 0
Viewed 2600

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
flatten dataframe dictionary python
2 Answers

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
user_46902370

You can create nested dataframes from the given dictionary and then concatenate them on each other.

Here's as sample dictionary,

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

You can then make a list of dataframes for every key in the sample_dict,

df_list=[]
for key in sample_dict:
    df_list.append(pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in sample_dict[key].items()])))

Finally you concatenate the dataframes stored in the df_list,

final_df=pd.concat(df_list)
user_105915310
Related