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>You can first create <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.from_tuples.html" rel="nofollow"><code>MultiIndex.from_tuples</code></a>, then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a> columns by it and last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a>:</p>

<pre><code>#create MultiIndex from df2
cols = pd.MultiIndex.from_tuples(list(zip(df2.FeatureName, df2.GeneID)),
       names=('FeatureName','GeneID'))
print (cols)
MultiIndex(levels=[['Feature1', 'Feature2'], ['Gene1', 'Gene2', 'Gene3']],
           labels=[[0, 0, 1, 1, 1], [0, 2, 0, 1, 2]],
           names=['FeatureName', 'GeneID'])

#reindex columns by MultiIndex           
df = df1.set_index('SampleName').reindex(columns=cols, level=1)
print (df)
FeatureName Feature1       Feature2            
GeneID         Gene1 Gene3    Gene1 Gene2 Gene3
SampleName                                     
Sample1            1     3        1     2     3
Sample2            4     6        4     5     6
Sample3            7     9        7     8     9

#groupby by level 0 of columns and aggregate sum
print (df.groupby(level=0, axis=1).sum())
FeatureName  Feature1  Feature2
SampleName                     
Sample1             4         6
Sample2            10        15
Sample3            16        24
</code></pre>

python pandas optimization dataframe sum