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>

<p>You are trying to reshape your data to wide format without a value column. One option is to use <code>pivot_table</code> and specify the <code>size</code> as the aggregate function, which will count the combinations of index and columns and fill as values. Missing values can be replaced with the <code>fill_value</code> parameter:</p>

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

#code   loc  date  XX  YY
#   0   JFK 10/05   1   1
#   1   JFK 10/06   0   1
#   2   JFK 10/07   1   1
#   3   LGA 10/05   1   1
#   4   LGA 10/06   1   1
#   5   LGA 10/07   1   1
</code></pre>

python pandas numpy dataframe