pivot_table No numeric types to aggregate
<p>I want to make a pivot table from the following dataframe with columns <code>sales</code>, <code>rep</code>. The pivot table shows <code>sales</code> but no <code>rep</code>. When I tried with only <code>rep</code>, I got the error <code>DataError: No numeric types to aggregate</code>. How to fix this such that I see both the numeric field <code>sales</code> and the field(string) <code>rep</code></p>

<pre><code>data = {'year': ['2016', '2016', '2015', '2014', '2013'],
        'country':['uk', 'usa', 'fr','fr','uk'],
        'sales': [10, 21, 20, 10,12],
        'rep': ['john', 'john', 'claire', 'kyle','kyle']
        }

print pd.DataFrame(data).pivot_table(index='country', columns='year', values=['rep','sales'])

        sales               
year     2013 2014 2015 2016
country                     
fr        NaN   10   20  NaN
uk         12  NaN  NaN   10
usa       NaN  NaN  NaN   21


print pd.DataFrame(data).pivot_table(index='country', columns='year', values=['rep'])
DataError: No numeric types to aggregate
</code></pre>

<p>You could use <code>set_index</code> and <code>unstack</code>:</p>

<pre><code>df = pd.DataFrame(data)
df.set_index(['year','country']).unstack('year')
</code></pre>

<p>yields</p>

<pre><code>          rep                     sales                  
year     2013  2014    2015  2016  2013  2014  2015  2016
country                                                  
fr       None  kyle  claire  None   NaN  10.0  20.0   NaN
uk       kyle  None    None  john  12.0   NaN   NaN  10.0
usa      None  None    None  john   NaN   NaN   NaN  21.0
</code></pre>

<p>Or, using <code>pivot_table</code> with <code>aggfunc='first'</code>:</p>

<pre><code>df.pivot_table(index='country', columns='year', values=['rep','sales'], aggfunc='first')
</code></pre>

<p>yields</p>

<pre><code>          rep                     sales                  
year     2013  2014    2015  2016  2013  2014  2015  2016
country                                                  
fr       None  kyle  claire  None  None    10    20  None
uk       kyle  None    None  john    12  None  None    10
usa      None  None    None  john  None  None  None    21
</code></pre>

<p>With <code>aggfunc='first'</code>, each <code>(country, year, rep)</code> or <code>(country, year, sales)</code>
group is aggregrated by taking the first value found. In your case there appears to be no duplicates, so the first value is the same as the only value.</p>

python pandas