Transposing a subset of columns in a Pandas DataFrame while using others as grouping variable?
<p>Let's say I have a Pandas dataframe (that is already in the dataframe format):</p>

<pre><code>x = [[1,2,8,7,9],[1,3,5.6,4.5,4],[2,3,4.5,5,5]]
df = pd.DataFrame(x, columns=['id1','id2','val1','val2','val3'])

id1 id2 val1 val2 val3
1   2   8.0  7.0  9
1   3   5.6  4.5  4
2   3   4.5  5.0  5
</code></pre>

<p>I want <code>val1</code>, <code>val2</code>, and <code>val2</code> in one column, with <code>id1</code> and <code>id2</code> as grouping variables. I can use this <strong>extremely</strong> convoluted code:</p>

<pre><code>dfT = df.iloc[:,2::].T.reset_index(drop=True)
n_points = dfT.shape[0]
final = pd.DataFrame()
for i in range(0, df.shape[0]):
    data = np.asarray([[df.ix[i,'id1']]*n_points, 
                      [df.ix[i,'id2']]*n_points,
                      dfT.ix[:,i].values]).T
    temp = pd.DataFrame(data, columns=['id1','id2','val'])
    final = pd.concat([final, temp], axis=0)
</code></pre>

<p>to get my dataframe into the correct format:</p>

<pre><code>    id1 id2 val
0   1.0 2.0 8.0
1   1.0 2.0 7.0
2   1.0 2.0 9.0
0   1.0 3.0 5.6
1   1.0 3.0 4.5
2   1.0 3.0 4.0
0   2.0 3.0 4.5
1   2.0 3.0 5.0
2   2.0 3.0 5.0
</code></pre>

<p>but there must be a more efficient way of doing this, since on a large dataframe this takes way too long.</p>

<p>Suggestions?</p>

<p>There's even a simpler one which can be done using <a href="https://github.com/pydata/pandas/blob/master/pandas/core/reshape.py#L804" rel="nofollow"><code>lreshape</code></a>(Not yet documented though):</p>

<pre><code>pd.lreshape(df, {'val': ['val1', 'val2', 'val3']}).sort_values(['id1', 'id2'])
</code></pre>

<p><a href="http://i.stack.imgur.com/UAHVp.png" rel="nofollow"><img src="http://i.stack.imgur.com/UAHVp.png" alt="Image"></a></p>

python performance pandas optimization dataframe