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>

<p>If you want to decrease the memory usage, I think your best option is to iterate over the first DataFrame since it has only 1k rows. </p>

<pre><code>dfs = []
frame1 = frame1.set_index('SampleName')
for idx, row in frame1.iterrows():
    dfs.append(frame2.join(row, on='GeneID').groupby('FeatureName').sum())
pd.concat(dfs, axis=1).T
</code></pre>

<p>yields</p>

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

python pandas optimization dataframe sum