title: pandas: how to run a pivot with a multi-index?
body: <p>I would like to run a pivot on a pandas <code>DataFrame</code>, with the index being two columns, not one. For example, one field for the year, one for the month, an 'item' field which shows 'item 1' and 'item 2' and a 'value' field with numerical values. I want the index to be year + month.</p>
  
  <p>The only way I managed to get this to work was to combine the two fields into one, then separate them again. is there a better way?</p>
  
  <p>Minimal code copied below. Thanks a lot!</p>
  
  <p>PS Yes, I am aware there are other questions with the keywords 'pivot' and 'multi-index', but I did not understand if/how they can help me with this question.</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  
  df= pd.DataFrame()
  month = np.arange(1, 13)
  values1 = np.random.randint(0, 100, 12)
  values2 = np.random.randint(200, 300, 12)
  
  
  df['month'] = np.hstack((month, month))
  df['year'] = 2004
  df['value'] = np.hstack((values1, values2))
  df['item'] = np.hstack((np.repeat('item 1', 12), np.repeat('item 2', 12)))
  
  # This doesn't work: 
  # ValueError: Wrong number of items passed 24, placement implies 2
  # mypiv = df.pivot(['year', 'month'], 'item', 'value')
  
  # This doesn't work, either:
  # df.set_index(['year', 'month'], inplace=True)
  # ValueError: cannot label index with a null key
  # mypiv = df.pivot(columns='item', values='value')
  
  # This below works but is not ideal: 
  # I have to first concatenate then separate the fields I need
  df['new field'] = df['year'] * 100 + df['month']
  
  mypiv = df.pivot('new field', 'item', 'value').reset_index()
  mypiv['year'] = mypiv['new field'].apply( lambda x: int(x) / 100)  
  mypiv['month'] = mypiv['new field'] % 100
  </code></pre>