0

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}
Community
  • 1
  • 1
SharpObject
  • 597
  • 5
  • 17

1 Answers1

0

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:]
SharpObject
  • 597
  • 5
  • 17