How to get the Percentage of a Column based on a Condition? Python

user_14421448 0
Viewed 908

I want to calculate the percentage of my Products column according to the occurrences per related Country. I would greatly appreciate your help.

Here is what I did so far, I calculated my new dataframe with this code:

gb = data1.groupby(['Country', 'Products']).size()
df = gb.to_frame(name = 'ProductsCount').reset_index()
df

Which gives me something that look like this:

   Countries    Products     ProductsCount
0  Country 1     Product 1     5
1  Country 1     Product 2     31
2  Country 2     Product 1     2
3  Country 2     Product 2     1

Note: I have a couple of thousands rows of output.

My goal is to get the percentage per each products according to the country directly without calculating the ['ProductsCount'], like this:

   Countries    Products     Percentage
0  Country 1     Product 1     0.138
1  Country 1     Product 2     0.861
2  Country 2     Product 1     0.667
3  Country 2     Product 2     0.333

Otherwise If I can't get the the output to show only the %, then I would like something like this:

   Countries    Products     ProductsCount   Products%
0  Country 1     Product 1     5                0.138
1  Country 1     Product 2     31               0.861
2  Country 2     Product 1     2                0.667
3  Country 2     Product 2     1                0.333

I managed to calculate only the % according to the whole dataset using this code:

df['Products%'] = df.ProductsCount/len(df.Country)

Thank you in advance!

pandas percentage python
1 Answers

Use SeriesGroupBy.value_counts with normalize=True parameter:

df = (data1.groupby('Countries')['Products']
           .value_counts(normalize=True,sort=False)
           .reset_index(name='Percentage'))
print (df)
   Countries   Products  Percentage
0  Country 1  Product 1    0.138889
1  Country 1  Product 2    0.861111
2  Country 2  Product 1    0.666667
3  Country 2  Product 2    0.333333

EDIT:

df = (data1.groupby('Countries')['Products']
           .value_counts(sort=False)
           .reset_index(name='ProductsCount')
           .assign(Percentage = lambda x: x['ProductsCount'].div(len(x))))
print (df)
user_29010020
Related