how to groupby column values

here my data below: Dataframe -- df_AW17

Product ID  Season  Division    Brand   Category    Sub Category    AW16 (Sales)    AW17 (Sales)

Blazer 1    AW17    Men's Wear  Brand 1 Top BLAZER      198

Blazer 2    AW16    Men's Wear  Brand 1 Top BLAZER  138 

Blazer 2    AW17    Men's Wear  Brand 1 Top BLAZER      270

Blazer 3    AW17    Men's Wear  Brand 1 Top BLAZER      27

Blazer 4    AW17    Men's Wear  Brand 1 Top BLAZER      192

Blazer-10   AW17    Women's Wear    Brand 1 Top BLAZER      15

Blazer-11   AW16    Women's Wear    Brand 1 Top BLAZER  10  

Blazer-11   AW17    Women's Wear    Brand 1 Top BLAZER      14

Blazer-12   AW17    Women's Wear    Brand 1 Top BLAZER      16

Blazer-13   AW17    Women's Wear    Brand 1 Top BLAZER      207

Blazer-5    AW16    Women's Wear    Brand 1 Top BLAZER  126 

Blazer-5    AW17    Women's Wear    Brand 1 Top BLAZER      200

Blazer-6    AW17    Men's Wear  Brand 1 Top BLAZER      5

Blazer-7    AW17    Women's Wear    Brand 1 Top BLAZER      299

Blazer-8    AW17    Women's Wear    Brand 1 Top BLAZER      147

Blazer-9    AW17    Men's Wear  Brand 1 Top BLAZER      23

Jacket-10   AW17    Men's Wear  Brand 1 Top JACKETS     20

Jacket-11   AW17    Men's Wear  Brand 1 Top JACKETS     5

Jacket-12   AW16    Men's Wear  Brand 1 Top JACKETS 5   

Jacket-12   AW17    Men's Wear  Brand 1 Top JACKETS     12

Jacket-13   AW16    Women's Wear    Brand 1 Top JACKETS 15

It has Product ID as value , i need to get sales growth % as new column using Season AW16, AW17 with given Sales of AW16 (Sales), AW17 (Sales). Problem is i was not able to group or put formula as columns values of Sales are in different Rows for particular Product ID.

I had tried to do.

df_AW17['Sales Growth %'] = df_AW17.groupby(['Product ID'])(((df_AW17['AW17 (Sales)'] - df_AW17['AW16 (Sales)']) / df_AW17['AW16 (Sales)']) * 100).

and result i want is (Sales growth %) from year AW16 (Sales) to AW17 (Sales) specific to Product ID.

1 answer

  • answered 2020-04-09 08:26 emiljoj

    as all rows in your dataframe seem to have unique product ids, would the simpler following creation of a new column not fulfill the purpose?

    df_AW17['Sales_growth_pct'] = (df_AW17['AW17 (Sales)'] - df_AW17['AW16 (Sales)']) / df_AW17['AW16 (Sales)']