Converting dictionary with tuple key (key1,key2) into dataframe, when key1 is the index and key2 ,value is columns

Question

I have the following Dictionary:dict = {('one','A'):8,('one','B'):19,('one','D'):29,('two','C'):18,('two','A'):10,('two','B'):4,('two','D'):2,('six','C'):4,('six','A'):4,('six','B'):4}

I convert it to dataFrame looking like:

         Score
one  A     8
     B    19
     D    29
two  C    18
     A    10
     B     4
     D     2
six  C     4
     A     4
     B     4

However I want to convert the dictionary to DF, where Key1 from tuple is the index, and key2 is the column names:

A B C D
one 8 19 0 29 two 10 4 18 2 six 4 4 4 0

How can I covert the dictionary to such data frame?

Answer 1

Try this:

In [31]: pd.DataFrame({'Score':list(t.values())}, index=pd.MultiIndex.from_tuples(t.keys())).reset_index().pivot_table(index='level_0', columns='leve
    ...: l_1', fill_value=0).reset_index(level=0, drop=True)
Out[31]:
        Score
level_1     A   B   C   D
0           8  19   0  29
1           4   4   4   0
2          10   4  18   2

Answer 2

You can try this: , Its updated version of previous block:

df = (pd.DataFrame({'Score':list(dict.values())}, 
             index=pd.MultiIndex.from_tuples(dict.keys())).reset_index()
             .pivot_table(index='level_0', columns='level_1', fill_value=0).reset_index())

df.columns = df.columns.droplevel(0)
df = df.rename(columns={'':'index'}).set_index('index')

Answer 3

Solution with unstack:

d = {('one','A'):8,('one','B'):19,('one','D'):29,('two','C'):18,
    ('two','A'):10,('two','B'):4,('two','D'):2,('six','C'):4,
    ('six','A'):4,('six','B'):4}

idx = pd.MultiIndex.from_tuples(d.keys())
print (pd.DataFrame(list(d.values()), index=idx, columns=['Score'])
         .unstack(fill_value=0)['Score'])

      A   B   C   D
one   8  19   0  29
six   4   4   4   0
two  10   4  18   2