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>Another solution using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.crosstab.html" rel="nofollow"><code>crosstab</code></a> which computes the frequency of occurence of the values present in the <code>columns</code> argument:</p>

<pre><code>pd.crosstab(index=[df['loc'], df['date']], columns=df['code'])  \
  .reset_index(level=1)                                         \
  .sort_index(ascending=False)
</code></pre>

<p><a href="http://i.stack.imgur.com/Hcw7r.png" rel="nofollow"><img src="http://i.stack.imgur.com/Hcw7r.png" alt="Image"></a></p>

<p>Note: It's not a good practice to name the columns as 'loc' which coincidentally also is a method used by <code>pandas</code> for performing label based location indexing. </p>

python pandas numpy dataframe