title: Python reshaping based on column names keeping ID & other rows
body: <p>What is the best way to reshape a df so it stacks columns based on a similarity in column name while keeping the unique ID portion of the column name in a new column?</p>
  
  <p>I have a df similar to the following (my actual data also includes NaN values that need to remain):</p>
  
  <pre><code>df = pandas.DataFrame({"RX_9mm": scipy.randn(5), "RY_9mm": scipy.randn(5),"TX_9mm": scipy.randn(5), "TY_9mm": scipy.randn(5), "RX_10mm": scipy.randn(5), "RY_10mm": scipy.randn(5),"TX_10mm": scipy.randn(5), "TY_10mm": scipy.randn(5), "time": range(5)})
  
      RX_9mm  RY_9mm  TX_9mm  TY_9mm  RX_10mm  RY_10mm  TX_10mm  TY_10mm  time
  0 -0.1444  2.1319  1.9665  0.1773   0.5156  -1.8461   0.9122   1.1285     0
  1  1.4831 -0.8773 -1.0112 -0.0010   1.4532  -1.3721   0.6894  -0.1781     1
  2  0.3685  0.2148 -1.2216  0.0098  -1.1427  -0.1851   0.3890   0.9552     2
  3  0.6843 -2.0279 -1.1342 -0.8869   0.2718  -2.4857  -1.0496  -0.4286     3
  4 -1.5625 -0.2733 -0.1243 -1.2248  -0.7403  -0.5840   0.1797  -0.7014     4
  </code></pre>
  
  <p>However I need it to look like this:</p>
  
  <pre><code>       RX      RY      TX      TY time   ID
  0 -0.1444  2.1319  1.9665  0.1773    0  9mm
  1  1.4831 -0.8773 -1.0112 -0.0010    1  9mm
  2  0.3685  0.2148 -1.2216  0.0098    2  9mm
  3  0.6843 -2.0279 -1.1342 -0.8869    3  9mm
  4 -1.5625 -0.2733 -0.1243 -1.2248    4  9mm
  5  0.5156 -1.8461  0.9122  1.1285    0 10mm  
  6  1.4532 -1.3721  0.6894 -0.1781    1 10mm
  7 -1.1427 -0.1851  0.3890  0.9552    2 10mm
  8  0.2718 -2.4857 -1.0496 -0.4286    3 10mm
  9 -0.7403 -0.5840  0.1797 -0.7014    4 10mm
  </code></pre>
  
  <p>I tried to use the following code from an example from 'Reshaping dataframes in pandas based on column labels' by Chang She</p>
  
  <p>However when I use the following code:</p>
  
  <pre><code>id = df.ix[:, ['time']]
  df.columns = pandas.MultiIndex.from_tuples([tuple(c.split('_')) for c in df.columns])
  pandas.merge(df.stack(0).reset_index(1), id, left_index=True, right_index=True)
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>       RX      RY      TX      TY      RX      RY      TX      TY time
        9mm     9mm     9mm     9mm    10mm    10mm    10mm    10mm  NaN
  0 -0.1444  2.1319  1.9665  0.1773  0.5156 -1.8461  0.9122  1.1285    0
  1  1.4831 -0.8773 -1.0112 -0.0010  1.4532 -1.3721  0.6894 -0.1781    1
  2  0.3685  0.2148 -1.2216  0.0098 -1.1427 -0.1851  0.3890  0.9552    2
  3  0.6843 -2.0279 -1.1342 -0.8869  0.2718 -2.4857 -1.0496 -0.4286    3
  4 -1.5625 -0.2733 -0.1243 -1.2248 -0.7403 -0.5840  0.1797 -0.7014    4
  </code></pre>
  
  <p>I understand that the new columns are multi level with the measurement (RX, RY etc.) and the ID (9mm, 10mm) levels, but I don't understand how to stack the measurements into the same columns while keeping the ID as a new column.</p>
  
  <p>If anyone could explain what I am doing wrong to get this output and not the stacked columns, I would really appreciate it. </p>
  
  <p>Thank you</p>