title: Pandas DataFrame Pivot Using Dates and Counts
body: <p>I've taken a large data file and managed to use groupby and value_counts to get the dataframe below. However, I want to format it so the company is on the left, with the months on top, and each number would be the number of calls that month, the third column. </p>
  
  <p>Here is my code to sort:</p>
  
  <pre><code>data = pd.DataFrame.from_csv('MYDATA.csv')
  
  data[['recvd_dttm','CompanyName']]
  data['recvd_dttm'].value_counts()  
  
  count = data.groupby(["recvd_dttm","CompanyName"]).size()
  df = pd.DataFrame(count)
  df.pivot(index='recvd_dttm', columns='CompanyName', values='NumberCalls')
  </code></pre>
  
  <p>Here is my output df=</p>
  
  <pre><code>recvd_dttm      CompanyName                           
  1/1/2015 11:42  Company 1      1
  1/1/2015 14:29  Company 2      1
  1/1/2015 8:12   Company 4      1
  1/1/2015 9:53   Company 1      1
  1/10/2015 11:38 Company 3      1
  1/10/2015 11:31 Company 5      1
  1/10/2015 12:04 Company 2      1
  </code></pre>
  
  <p>I want</p>
  
  <pre><code>Company     Jan Feb Mar Apr May
  Company 1   10  4   45  40  34
  Company 2   2   5   56  5   57
  Company 3   3   7   71  6   53
  Company 4   4   4   38  32  2
  Company 5   20  3   3   3   29
  </code></pre>
  
  <p>I know that there is a nifty pivot function for dataframes from this documentation <a href="http://pandas.pydata.org/pandas-docs/stable/reshaping.html" rel="nofollow">http://pandas.pydata.org/pandas-docs/stable/reshaping.html</a> for pandas, so I've been trying to use df.pivot(index='recvd_dttm', columns='CompanyName', values='NumberCalls')</p>
  
  <p>One problem is that the third column doesn't have a name, so I can't use it for values = 'NumberCalls'. The second problem is figuring out how to take the datetime format in my dataframe and make it display by month only. </p>
  
  <p>Edit:
  CompanyName is the first column, recvd_dttm is the 15th column. This is my code after some more attempts:</p>
  
  <pre><code>data = pd.DataFrame.from_csv('MYDATA.csv')
  
  data[['recvd_dttm','CompanyName']]
  data['recvd_dttm'].value_counts()
  RatedCustomerCallers = data['CompanyName'].value_counts()
  
  
  count = data.groupby(["recvd_dttm","CompanyName"]).size()
  df = pd.DataFrame(count).set_index('recvd_dttm').sort_index()
  df.index = pd.to_datetime(df.index, format='%m/%d/%Y %H:%M')
  result = df.groupby([lambda idx: idx.month, 'CompanyName']).agg({df.columns[1]: sum}).reset_index()
  result.columns = ['Month', 'CompanyName', 'NumberCalls']
  
  result.pivot(index='recvd_dttm', columns='CompanyName', values='NumberCalls')
  </code></pre>
  
  <p>It is throwing this error: KeyError: 'recvd_dttm' and won't get to the result line.</p>