I have a dataset as below, in this case, I want to count the number of fruits in each country and output as a column in the dataset.
I tried to use groupby, df=df.groupby('Country')['Fruits'].count(),
but in this case I am not getting the expected results as the groupby just outputs the count and not the entire dataframe/dataset.
It would be helpful if someone can suggest a better way to do this.
Dataset
Country Fruits Price Sold Weather
India Mango 200 Market sunny
India Apple 250 Shops sunny
India Banana 50 Market winter
India Grapes 150 Road sunny
Germany Apple 350 Supermarket Autumn
Germany Mango 500 Supermarket Rainy
Germany Kiwi 200 Online Spring
Japan Kaki 300 Online sunny
Japan melon 200 Supermarket sunny
Expected Output
Country Fruits Price Sold Weather Number
India Mango 200 Market sunny 4
India Apple 250 Shops sunny 4
India Banana 50 Market winter 4
India Grapes 150 Road sunny 4
Germany Apple 350 Supermarket Autumn 3
Germany Mango 500 Supermarket Rainy 3
Germany Kiwi 200 Online Spring 3
Japan Kaki 300 Online sunny 2
Japan melon 200 Supermarket sunny 2
Thank you:)
CodePudding user response:
You are looking for transform
:
df['count'] = df.groupby('Country')['Fruits'].transform('size')
Country Fruits Price Sold Weather count
0 India Mango 200 Market sunny 4
1 India Apple 250 Shops sunny 4
2 India Banana 50 Market winter 4
3 India Grapes 150 Road sunny 4
4 Germany Apple 350 Supermarket Autumn 3
5 Germany Mango 500 Supermarket Rainy 3
6 Germany Kiwi 200 Online Spring 3
7 Japan Kaki 300 Online sunny 2
8 Japan melon 200 Supermarket sunny 2