I want to make a pivot table from the following dataframe with columns sales
, rep
. The pivot table shows sales
but no rep
. When I tried with only rep
, I got the error DataError: No numeric types to aggregate
. How to fix this such that I see both the numeric field sales
and the field(string) rep
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
You could use set_index
and unstack
:
df = pd.DataFrame(data)
df.set_index(['year','country']).unstack('year')
yields
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
Or, using pivot_table
with aggfunc='first'
:
df.pivot_table(index='country', columns='year', values=['rep','sales'], aggfunc='first')
yields
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
With aggfunc='first'
, each (country, year, rep)
or (country, year, sales)
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.
It seems that the problem comes from the different types for column rep and sales, if you convert the sales to str
type and specify the aggfunc as sum
, it works fine:
df.sales = df.sales.astype(str)
pd.pivot_table(df, index=['country'], columns=['year'], values=['rep', 'sales'], aggfunc='sum')
# 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With