Pandas DataFrame - Transpose few elements of a row into columns and fill missing data
<p>I want to reformat a dataframe by transposing some elements of rows into columns.
To provide an example of what I meant.</p>

<p>In the below dataframe, I want all the elements in the code column to be individual columns. And the missing rows like 'JFK     10/06   XX' should be populated as 0 or nan.</p>

<p>Original DataFrame:</p>

<pre><code>loc     date    code
---     -----   ----
LGA     10/05   XX
LGA     10/06   XX
LGA     10/07   XX
LGA     10/05   YY
LGA     10/06   YY
LGA     10/07   YY
JFK     10/05   XX
###JFK      10/06   XX (missing)
JFK     10/07   XX
JFK     10/05   YY
JFK     10/06   YY
JFK     10/07   YY
</code></pre>

<p>To be formatted DataFrame:</p>

<pre><code>loc     date    XX  YY
---     -----   --  --
LGA     10/05   1   1
LGA     10/06   1   1
LGA     10/07   1   1
JFK     10/05   1   1
JFK     10/06   0   1
JFK     10/07   1   1
</code></pre>

<p>Here JFK -> 10/06 has an entry 0 in XX</p>

<p>I tried grouping by with the rest of the columns and able to verify the counts, but I couldn't format it in the expected way.</p>

<ol>
<li><code>stack</code> the dataframe</li>
<li>reset index</li>
<li>create pivot table of counts</li>
</ol>

<hr>

<pre><code>new_df = (df.set_index(keys=['loc','date'])
          .stack()
          .reset_index()
          .pivot_table(index=['loc','date'], columns=0, fill_value=0, aggfunc='size'))
</code></pre>

<hr>

<p>OUTPUT:</p>

<pre><code>0               XX  YY
loc date              
JFK 2016-10-05   1   1
    2016-10-06   0   1
    2016-10-07   1   1
LGA 2016-10-05   1   1
    2016-10-06   1   1
    2016-10-07   1   1
</code></pre>

python pandas numpy dataframe