CODEWITHSUNDEEP
pythondataframedictionaryflatten
genz_on_code
genz_on_code

Reputation: 429

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

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

Upvotes: 4

Views: 6364

Answers (2)

harjotspahwa
harjotspahwa

Reputation: 31

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)

Upvotes: 2

falafelocelot
falafelocelot

Reputation: 588

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

Upvotes: 4

Related Questions