title: Creating Pivot DataFrame using Multiple Columns in Pandas
body: <p>I have a pandas dataframe following the form in the example below:</p>
  
  <pre><code>data = {'id': [1,1,1,1,2,2,2,2,3,3,3], 'a': [-1,1,1,0,0,0,-1,1,-1,0,0], 'b': [1,0,0,-1,0,1,1,-1,-1,1,0]}
  df = pd.DataFrame(data)
  </code></pre>
  
  <p>Now, what I want to do is create a pivot table such that for each of the columns except the id, I will have 3 new columns corresponding to the values. That is, for column <code>a</code>, I will create <code>a_neg</code>, <code>a_zero</code> and <code>a_pos</code>. Similarly, for <code>b</code>, I will create <code>b_neg</code>, <code>b_zero</code> and <code>b_pos</code>. The values for these new columns would correspond to the number of times those values appear in the original <code>a</code> and <code>b</code> column. The final dataframe should look like this:</p>
  
  <pre><code>result = {'id': [1,2,3], 'a_neg': [1, 1, 1],
        'a_zero': [1, 2, 2], 'a_pos': [2, 1, 0],
        'b_neg': [1, 1, 1], 'b_zero': [2,1,1], 'b_pos': [1,2,1]}
  df_result = pd.DataFrame(result)
  </code></pre>
  
  <p>Now, to do this, I can do the following steps and arrive at my final answer:</p>
  
  <pre><code>by_a = df.groupby(['id', 'a']).count().reset_index().pivot('id', 'a', 'b').fillna(0).astype(int)
  by_a.columns = ['a_neg', 'a_zero', 'a_pos']
  
  by_b = df.groupby(['id', 'b']).count().reset_index().pivot('id', 'b', 'a').fillna(0).astype(int)
  by_b.columns = ['b_neg', 'b_zero', 'b_pos']
  
  df_result = by_a.join(by_b).reset_index()
  </code></pre>
  
  <p>However, I believe that that method is not optimal especially if I have a lot of original columns aside from <code>a</code> and <code>b</code>. Is there a shorter and/or more efficient solution for getting what I want to achieve here? Thanks.</p>