Pandas groupby sum using two DataFrames
<p>I have two very large Pandas DataFrames and would like to use them to guide each other in a fast sum operation.  The two frames look like this:</p>

<p>Frame1:</p>

<pre><code>SampleName  Gene1   Gene2   Gene3
Sample1         1       2       3
Sample2         4       5       6
Sample3         7       8       9
</code></pre>

<p>(in reality, Frame1 is roughly 1,000 rows x ~300,000 columns)</p>

<p>Frame2:</p>

<pre><code>FeatureName GeneID
Feature1    Gene1
Feature1    Gene3
Feature2    Gene1
Feature2    Gene2
Feature2    Gene3
</code></pre>

<p>(in reality, <code>Frame2</code> is about ~350,000 rows x 2 columns, with ~17,000 unique features)</p>

<p>I would like to sum the columns of Frame1 by Frame2's groups of genes.  For example, the output of the two above frames would be:</p>

<pre><code>SampleName  Feature1    Feature2
Sample1            4           6
Sample2           10          15
Sample3           16          24
</code></pre>

<p>(in reality, the output would be ~1,000 rows x 17,000 columns)</p>

<p>Is there any way to do this with minimal memory usage?</p>

<h3>One obnoxious line</h3>

<pre><code>Frame1.set_index('SampleName') \
    .rename_axis('GeneID', axis=1) \
    .stack().rename('Value') \
    .reset_index().merge(Frame2) \
    .groupby(['SampleName', 'FeatureName']) \
    .Value.sum().unstack()
</code></pre>

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

python pandas optimization dataframe sum