Home > database >  Excel-like "% of total rows" in Python
Excel-like "% of total rows" in Python

Time:03-29

There is an option in Excel called "% of total rows".

Is it possible to simulate it in python?

Example:

sellers seller1 seller2 seller3
sales 1 1
shoes 1 1 1
shirts 1 1
hats 1 1

With that property it can be modified and it would look like this:

sellers seller1 seller2 seller3 TOTAL
sales 50% 50% 100%
shoes 33.3% 33.3% 33.3% 100%
shirts 50% 50% 100%
hats 50% 50% 100%

I load the data from a csv

Here I was able to get the totals but I cannot generate the percentages

df.pivot_table(index="CODIGO", columns="NOMBRE", values="COPIACOD", aggfunc="count", margins = True,fill_value = "0") df2=df.pivot_table(index="CODIGO", columns="NOMBRE", values="COPIACOD", aggfunc="count", margins = True,fill_value = "0", margins_name='Total') df2

I already solved it with the suggestion you gave me

Thank you

CodePudding user response:

I know that with Excel, it's just a matter of clicks to get to that, and there surely must be a smarter solution involving pivoting the dataframe (pandas.pivot_table), but if I can suggest a simple data manipulation solution, here it is:

#Preparing the input data:
import pandas as pd
df=pd.DataFrame()
df['sellers']=pd.Series(['sales','shoes','shirts','hats'])
df['seller1']=pd.Series([1,1,1,1])
df['seller2']=pd.Series([1,1,None,1])
df['seller3']=pd.Series([None,1,1,None])

#Create a static copy of your dataframe and sum over column values (axis=1)
df0=df.copy().sum(axis=1)
# Then divide each column by the sum of its respective row
df['seller1 %']=df['seller1']/(df0)*100
df['seller2 %']=df['seller2']/(df0)*100
df['seller3 %']=df['seller3']/(df0)*100

df
  • Related