How to convert dictionary inside a dictionary which contains a list of tuples into a pandas dataframe

user_6928914 0
Viewed 31

I have a dictionary like this

{
  'S1' :
     {
       'w1':[  ('a',0), ('b',1), ('c',3)],
       'w2':[  ('a',1), ('b',2), ('c',5)], 
       'w3':[  ('a',1), ('b',1), ('c',4)]
     },
 'S2' :      
    {
      'w4':[  ('a',1), ('b',2), ('c',5)],
      'w5':[  ('a',0), ('b',3), ('c',4)], 
      'w6':[  ('a',3), ('b',3), ('c',6)]
    }
}

I want to convert it to a pandas dataframe like this:

enter image description here

Can someone help me with this?

pandas dataframe tuples list dictionary
1 Answers

Use nested list comprehension with merge dictionary for list of dictionaries and pass to DataFrame constructor:

L = [{**{'Col S': k,'Col W':k1}, **dict(v1)} for k, v in d.items() for k1, v1 in v.items()]

df = pd.DataFrame(L)
print (df)
  Col S Col W  a  b  c
0    S1    w1  0  1  3
1    S1    w2  1  2  5
2    S1    w3  1  1  4
3    S2    w4  1  2  5
4    S2    w5  0  3  4
5    S2    w6  3  3  6

Solution with MultiIndex:

d = {(k, k1): dict(v1) for k, v in d.items() for k1, v1 in v.items()}

df = pd.DataFrame.from_dict(d, orient='index')
print (df)
       a  b  c
S1 w1  0  1  3
   w2  1  2  5
   w3  1  1  4
S2 w4  1  2  5
   w5  0  3  4
   w6  3  3  6

And then set index names and convert MultiIndex to columns, thank you @sammywemmy:

df = df.rename_axis(index = ['Col S', 'Col W']).reset_index()
print (df)
  Col S Col W  a  b  c
0    S1    w1  0  1  3
1    S1    w2  1  2  5
2    S1    w3  1  1  4
3    S2    w4  1  2  5
4    S2    w5  0  3  4
5    S2    w6  3  3  6
user_29010020
Related