SharpObject SharpObject - 22 days ago 5
Python Question

Unstacking multi-indexed dataframe into dataframe of dictionaries

So I multiplied a dataframe of dictionaries, by another dataframe of factors. I want to know how to get the resulting stacked dataframe from that multiplication back into a dataframe of dictionaries.

Say given df, and df2:

df = pd.DataFrame({'A': [{"ab":1, "b":2, "c":3}, {'b':4, 'c':5, 'ab':6}],
'B': [{"ab":7, "b":8, "c":9}, {'b':10, 'c':11, 'ab':12}]})

A B
0 {'b': 2, 'c': 3, 'ab': 1} {'b': 8, 'c': 9, 'ab': 7}
1 {'b': 4, 'c': 5, 'ab': 6} {'b': 10, 'c': 11, 'ab': 12}

df2 = pd.DataFrame({'A': [2, 3],
'B': [3, 4]})

A B
0 2 3
1 3 4


Using this to help multiply them together

In[11]: df.stack().apply(pd.Series)
Out[11]:
ab b c
0 A 1 2 3
B 7 8 9
1 A 6 4 5
B 12 10 11


Then applied a similar function to the df2 to return the dataframe as a 1xN series

In[12]: ser = pd.Series(df2.stack().apply(pd.Series).reset_index().iloc[:, -1])
In[13]: ser
Out[13]:
0 2
1 3
2 3
3 4


Then used the function from the link to multiply a dataframe and a series

In[14]: func = lambda x: np.asarray(x) * np.asarray(ser)
In[15]: df.stack().apply(pd.Series).apply(func)
Out[15]:
ab b c
0 A 2 4 6
B 21 24 27
1 A 18 12 15
B 48 40 44


How do I 'unstack' the above dataframe back into the same format as df?

A B
0 {'b': 4, 'c': 6, 'ab': 2} {'b': 24, 'c': 27, 'ab': 21}
1 {'b': 12, 'c': 15, 'ab': 18} {'b': 40, 'c': 44, 'ab': 48}

Answer

Transformed the data into a dictionary.

In[1]: df.to_dict('r')
Out[2]: [{'ab': 2, 'b': 4, 'c': 6},
 {'ab': 21, 'b': 24, 'c': 27},
 {'ab': 18, 'b': 12, 'c': 15},
 {'ab': 24, 'b': 20, 'c': 22},
 {'ab': 48, 'b': 40, 'c': 44},
 {'ab': 48, 'b': 40, 'c': 44}]

Then zipped all the level values with their corresponding dict, which was appended to a list

list = []
for x in zip(df.index.get_level_values(0),df.index.get_level_values(1),   df.to_dict('r')):
    list.append(x)
new = pd.DataFrame(list)
new = new.pivot(index=0, columns=1, values=2)

Then reset the multi-index and got rid of the new column

new.reset_index().ix[:, 1:]