title: Python Pandas Long to wide
body: <p>My data is currently in a long format. Below is a sample:</p>
  <pre><code>     ID    Date             Performance Location     Attrition     
         1   2001-01-31           2.380       NYC            0
         1   2001-02-28           2.440       NYC            0
         1   2001-03-31           2.460       RVA            1
         2   2001-01-31           2.780       NYC            0
         2   2001-02-28           2.800       NYC            0
         2   2001-03-31           2.700       NYC            0
         2   2001-04-30           2.300       NYC            1
  </code></pre>
  <p>I would like to reshape it into a wide format by &quot;ID&quot; and &quot;DATE&quot; like so:</p>
  <pre><code>       ID    Date(Start)   Date(End)  Performance Attrition  Horizon   
         1   2001-01-31      2001-02-28     2.380   0          1
         1   2001-01-31      2001-03-31     2.380   1          2
         1   2001-02-28      2001-03-31     2.440   1          1
         2   2001-01-31      2001-02-28     2.380   0          1
         2   2001-01-31      2001-03-31     2.780   0          2
         2   2001-01-31      2001-04-30     2.780   1          3
         2   2001-02-28      2001-03-31     2.800   0          1
         2   2001-02-28      2001-04-30     2.800   1          2
         2   2001-03-31      2001-04-30     2.700   1          1
  </code></pre>
  <p>I tried the solution posted here <a href="https://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape">Pandas long to wide reshape, by two variables</a> and have this:</p>
  <pre><code>df['idx'] = (df.groupby(['ID', 'DATE']).cumcount()) +1)
  
  df1 = (df.pivot_table(index=['ID', 'DATE'], 
                    columns=['idx'], 
                    values=['Performance'], 
                    aggfunc='first'))
  </code></pre>
  <p>But this solution is not able to repeat the rows for the same person across each month. I also want to add a column to count for the number of changes within a month( Horizon).</p>
  <p>The solution I can think of is too add another date:</p>
  <p><code>df['Date(End)'] = df['Date'].shift(1)</code>
  and then <br />
  <code>df['idx'] = (df.groupby(['ID', 'DATE','Date(End)']).cumcount()) +1)</code></p>
  <p><code>df1 = (df.pivot_table(index=['ID', 'DATE','Date(End)'],columns=['idx'], values=['Performance'], aggfunc='first'))</code></p>
  <p>If you can help me figure out what I am doing wrong here or if there is a better solution to achieving this data transformation.</p>
  <p>Thank you in Advance.</p>