SharpObject - 1 year ago 77
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}
``````

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:]
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download