How to multiply pandas dataframes and preserve row keys
<p>I'm struggling with multiplying dataframes together and preserving the row keys.</p>

<p>I have two files, call them say F1 and F2. F1 has a multi-part group key (g1,g2,g3), a two-part Type key (k1,k2) and some weights (r1,r2). F2 has a series of values for each Type key.</p>

<p>I'd like to join them on k1 and k2, and multiply r1 and r2 for each n.</p>

<p>I'm thinking that groupby and dataframe multiply should work but I can't see how to do it. The only thing I've got to work is merge and then multiply column by column, but it's super-slow.</p>

<pre><code>F1
g1  g2  g3  k1  k2  r1  r2
A   A   A   A   A   1   2
A   A   A   A   B   3   4
A   A   B   A   B   2   3

F2
k1  k2  n   r1  r2
A   A   1   0   1
A   A   2   1   1
A   A   3   1   0
A   B   1   3   4
A   B   2   4   4
A   B   3   4   3
A   C   1   1   1
A   C   3   4   5
A   C   2   3   4

Result
g1  g2  g3  k1  k2  n   r1  r2
A   A   A   A   A   1   0   2
A   A   A   A   A   2   1   2
A   A   A   A   A   3   1   0
A   A   A   A   B   1   9   16
A   A   A   A   B   2   12  16
A   A   A   A   B   3   12  12
A   A   B   A   B   1   6   12
A   A   B   A   B   2   8   12
A   A   B   A   B   3   8   9
</code></pre>

<p>Thanks</p>

<pre><code>mrg = F1.merge(F2, on=['k1', 'k2'])

mrg['r1'] = mrg.filter(like='r1').prod(1)
mrg['r2'] = mrg.filter(like='r2').prod(1)
drops = ['r1_x', 'r1_y', 'r2_x', 'r2_y']
mrg.drop(drops, axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/NR963.png" rel="nofollow"><img src="http://i.stack.imgur.com/NR963.png" alt="enter image description here"></a></p>

python pandas dataframe