groupby and count aren't work correctly pandas

Question

I would like to take only product equal to "Bikes" from the table below and group the years. I need it to create a plot where i want show how the sale of bicycles has been over the years.

yearsbuy = df[['Year', 'Product_Category', 'Country']]
plot1 = yearsbuy.groupby('Year')['Product_Category'].value_counts()
plot1 = plot1['Product_Category'] =='Bikes'

When i did i get :

Year  Product_Category
2011  Bikes                2677
2012  Bikes                2677
2013  Accessories         15025
      Bikes                5710
      Clothing             3708
2014  Accessories         20035

But I want just only Bikes. Why yearsbuy['Product_Category'] == "Bikes" isn't working ?

Use this: `plot1 = plot1[plot1['Product_Category'] =='Bikes']`. — Mayank Porwal, Dec 27 '20 at 19:26
When I tired i get everytime error : KeyError: 'Product_Category' — Przemek Dabek, Dec 27 '20 at 19:34

Raghul Raj · Accepted Answer · 2020-12-27T20:31:39.243

0

When we do a value_counts(), it gives the count as a pandas.Series for selected column, making the Year and Product_Category as indices (Mutli-index).

To obtain the columns Year and Product_Category back, we've to reset the index but the generated series raises a conflict since by default the Series takes the name of the column we're working with (Product_Category). So I'm renaming that column containing count as count and resetting index to obtain the Year and Product_Category columns

plot1 = yearsbuy.groupby('Year')['Product_Category'].value_counts().rename('count').reset_index()
plot1 = plot1[plot1['Product_Category'] =='Bikes']

edited Dec 27 '20 at 20:31

answered Dec 27 '20 at 19:45

Raghul Raj

1,428
9
24

I get error : TypeError: Cannot reset_index inplace on a Series to create a DataFrame – Przemek Dabek Dec 27 '20 at 19:49
C:\Users\admin\anaconda3\lib\site-packages\pandas\core\ops\array_ops.py:253: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison res_values = method(rvalues) index Year Product_Category – Przemek Dabek Dec 27 '20 at 20:05
The updated one should work. – Raghul Raj Dec 27 '20 at 20:17
Amazing thanks ! Could you explaine me step by step what's happend after value.count() – Przemek Dabek Dec 27 '20 at 20:20
Updated my solution. – Raghul Raj Dec 27 '20 at 20:32
May I ask one more thing : If i want add "Country" : df[['Country','Year','Product_Category']] plot2 = df.groupby('Year')[['Country','Product_Category']].value_counts().rename('count').reset_index() – Przemek Dabek Dec 27 '20 at 20:40
add "Country" as in? The previous one can give you sales of Bicycles over the years. What do you intend to visualize in this case? – Raghul Raj Dec 27 '20 at 20:47
I'd like to show you how much each country has sold over the years. Because in this graph, I showed the total sum – Przemek Dabek Dec 27 '20 at 20:54
In that case, you can simply replace `Product_Category` with `country` – Raghul Raj Dec 27 '20 at 20:56
1

Yeah i did. Thank you a lot. Have a nice evening ! take care – Przemek Dabek Dec 27 '20 at 20:57

groupby and count aren't work correctly pandas

1 Answers1