title: Pivot pandas dataframe from from wide to long
body: <p>I'm not sure if this data is properly in a wide format, but what I want to do is the following: </p>
  
  <p>Convert from the shape of <code>d1</code> to the shape <code>d2</code></p>
  
  <pre class="lang-py prettyprint-override"><code>In [26]: d1 = pd.DataFrame({'where':['x','y'],
      ...: 'p1':[3,7],
      ...: 'p2':[11,12]})
  
  In [27]: d2=pd.DataFrame({
      ...: 'where':['x','x','y','y'],
      ...: 'who':['p1','p2','p1','p2'],
      ...: 'value':[3,11,7,12]})
  </code></pre>
  
  <p>Which looks as:</p>
  
  <pre class="lang-py prettyprint-override"><code>In [10]: d1
  Out[10]:
    where  p1  p2
  0     x   3  11
  1     y   7  12
  
  In [11]: d2
  Out[11]:
    where who  value
  0     x  p1      3
  1     x  p2     11
  2     y  p1      7
  3     y  p2     12
  
  </code></pre>
  
  <p>I think this is some form of <code>d1.pivot( ... )</code>, but I can't seem to figure how how to go about it.</p>
  
  <p>So, to be clear, I want to be able to reshape the data <code>d1</code> into the structure of <code>d2</code> using pandas.</p>
  
  <h1>edit</h1>
  
  <p>The following works, but is quite a poor approach</p>
  
  <pre class="lang-py prettyprint-override"><code>d3 = d1.pivot(columns='where').T.reset_index()
  d3.columns = ['who','where','a','b']
  d3 = d3.loc[:,['where','who','a','b']]
  d3 = d3.sort_values('where')
  d3.fillna(value=0,inplace=True)
  d3['c'] = d3.a + d3.b
  d3.drop(['a','b'],axis=1,inplace=True)
  d3.columns=['where','who','value']
  
  In [43]: d3
  Out[43]:
    where who  value
  0     x  p1    3.0
  2     x  p2   11.0
  1     y  p1    7.0
  3     y  p2   12.0
  </code></pre>
  
  <h1>edit 2</h1>
  
  <p>The following works</p>
  
  <pre class="lang-py prettyprint-override"><code>In [49]: d1.melt(id_vars='where')
  Out[49]:
    where variable  value
  0     x       p1      3
  1     y       p1      7
  2     x       p2     11
  3     y       p2     12
  </code></pre>
  
  <p>I am curious if this is possible with pivot? I was under the impression that all of these operations could be done using the pivot function</p>
  
  <h1>edit 3 - example of getting the original structure back</h1>
  
  <p>Above I was referring to the <code>pivot</code> function, not the <code>pivot_table</code> function, though the following example is a way to find the original structure given the final </p>
  
  <pre class="lang-py prettyprint-override"><code>d1.melt(id_vars='where').pivot_table(values="value", index="where", columns="variable")
  
  variable    p1  p2
  where       
  x           3   11
  y           7   12
  </code></pre>