How to add new columns to pivot table using pandas

Question

I'm trying to create a new column that show the weightage of every product that I has.

Let's say I have the following dataframe that I have pivot:

   PRODUCT  UNIT_TESTED AVG_YIELD 
        A       401    82.1042
        B      1512    96.0687  
        C       292    22.7806  
        D       134    37.0088

using

 pd.pivot_table(data = df, index = ['PRODUCT'], 
                  values = ("UNIT_TESTED","AVG_YIELD"), 
                  aggfunc = "sum", margins=True)\
     .fillna('')

Now, I want to add a new column WEIGHTAGE for each product.

The calculation:

WEIGHTAGE 'A' = (UNIT_TESTED 'A'/Total of UNIT_TESTED)*100

This is where I'm stuck to put into coding to create a new column.

My desired output:

PRODUCT UNIT_TESTED AVG_YIELD WEIGHTAGE
    A       401      82.1042    17.1441
    B      1512      96.0687    64.6430
    C       292      22.7806    12.4840
    D       134      37.0088    5.7289

DavideBrex · Accepted Answer · 2020-06-18 10:10:12Z

1

The last row of the pivot table that you obtained contains the sum of unit tested. So you can simply divide by that value (pivot_df.loc["All","UNIT_TESTED"]) the column UNIT_TESTED:

pivot_df = pd.pivot_table(data = df, index = ['PRODUCT'], 
                  values = ("UNIT_TESTED","AVG_YIELD"), 
                  aggfunc = "sum", margins=True)\
     .fillna('')

pivot_df["Weightage"] = round((pivot_df["UNIT_TESTED"] / pivot_df.loc["All","UNIT_TESTED"])*100,2)

print(pivot_df)

Output:

    AVG_YIELD   UNIT_TESTED Weightage
PRODUCT         
A   82.1042     401        17.14
B   96.0687     1512       64.64
C   22.7806     292        12.48
D   37.0080     134        5.73
All 237.9615    2339       100.00

edited Jun 18, 2020 at 10:10

answered Jun 18, 2020 at 10:01

DavideBrex

2,4241 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nur Atiqah Over a year ago

I have tried the code and it works. I never use All before so I was lost on how to get the grand total for the pivot table.

Nur Atiqah Over a year ago

do you have any idea on how I can further sort the column WEIGHTAGE in descending?

DavideBrex Over a year ago

pivot_df.sort_values("Weightage", ascending=False) like this?

Nur Atiqah Over a year ago

yup..after hours of researching only I found this coding...anyway thanks for the response

lil-wolf · Accepted Answer · 2020-06-18 10:00:07Z

0

suppose, your pivot table is pivot_df

pivot_df['WEIGHTAGE'] = (pivot_df['UNIT_TESTED'] * 100 ) / pivot_df['UNIT_TESTED'].sum()

answered Jun 18, 2020 at 10:00

lil-wolf

3822 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to add new columns to pivot table using pandas

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related