CODEWITHSUNDEEP
pythonpandas-groupby
Sheriff
Sheriff

Reputation: 155

python grouping and transpose

I have a dataframe - df as below :

 df = pd.DataFrame({"Customer_no": ['1', '1', '1', '2', '2', '6', '7','8','9','10'],
      "Card_no": ['111', '222', '333', '444', '555', '666', '777','888','999','000'],
      "Card_name":['AAA','AAA','BBB','CCC','AAA','DDD','EEE','BBB','CCC','CCC'],
      "Group_code":['123','123','456','678','123','434','678','365','678','987'],
      "Amount":['100','240','450','212','432','123','543','567','232','453'],
      "Category" :['Electrical','Electrical','Hardware','House','Electrical','Car','House','Toy','House','Bike123']})

Now, i need to group by Customer no and get Total Amount, Top 1,2,3 categories and their percentage against the total amount. NOTE : In my Toy dataset, i have only 2 categories , in my original data i have more, i need to select top 5 categories. My Dataframe should look like this :

 df = pd.DataFrame({"Customer_no": ['1', '1', '1', '2', '2', '6', '7','8','9','10'],
      "Card_no": ['111', '222', '333', '444', '555', '666', '777','888','999','000'],
      "Card_name":['AAA','AAA','BBB','CCC','AAA','DDD','EEE','BBB','CCC','CCC'],
      "Group_code":['123','123','456','678','123','434','678','365','678','987'],
      "Amount":['100','240','450','212','432','123','543','567','232','453'],
      "Category" :['Electrical','Electrical','Hardware','House','Electrical','Car','House','Toy','House','Bike123'],
      "Total amount" :['790','790','790','644','644','123','543','567','232','453'],
      "Top-1 Category":['Hardware','Hardware','Hardware','Electrical','Electrical','Car','House','Toy',
                        'House','Bike123'],
      "Top-1 Category %":['57','57','57','67','67','100','100','100','100','100'],
      "Top-2 Category":['Electrical','Electrical','Electrical','House','House','','','','',''] ,    
      "Top-2 Category %":['43','43','43','33','33','0','0','0','0','0']})

Request your help to achieve it.

NOTE : 1) Top Category is selected by Grouping all the Category against each customer and summing up the amount for each category customer wise. Which ever category has more amount its Top 1 category, similarly the next one is Top 2 and so on 2) Top 1 category percentage : Its the Total amount of each category divided by Total amount and multiplied with 100. This is for each customer. Similarly for Top 2 category.

Upvotes: 2

Views: 130

Answers (1)

Rudolf Morkovskyi
Rudolf Morkovskyi

Reputation: 869

Try this:

#Your data
df = pd.DataFrame({"Customer_no": ['1', '1', '1', '2', '2', '6', '7','8','9','10'],
      "Card_no": ['111', '222', '333', '444', '555', '666', '777','888','999','000'],
      "Card_name":['AAA','AAA','BBB','CCC','AAA','DDD','EEE','BBB','CCC','CCC'],
      "Group_code":['123','123','456','678','123','434','678','365','678','987'],
      "Amount":['100','240','450','212','432','123','543','567','232','453'],
      "Category" :['Electrical','Electrical','Hardware','House','Electrical','Car','House','Toy','House','Bike123']})
#Make some columns numerical
for i in ["Customer_no","Card_no","Group_code","Amount"]:
    df[i] = pd.to_numeric(df[i])

#Total sum
Total_amount = pd.DataFrame(df.groupby(["Customer_no"]).Amount.sum()).reset_index().rename(columns={'Amount':'Total amount'})

#Add some nesessery colums and grouping
Top_1_Category = pd.DataFrame(df.groupby(['Customer_no',"Category"]).Amount.sum()).reset_index().rename(columns={'Amount':'group_sum'})

df = df.merge(Total_amount,how='left',on='Customer_no')
df = df.merge(Top_1_Category,how='left',on=['Category','Customer_no'])

group_top_1 = df[['Customer_no','Category','group_sum']].loc[df.groupby('Customer_no').group_sum.agg('idxmax')].rename(columns={'Category':'Top-1 Category','group_sum':'group_sum_1'})

df = df.merge(group_top_1,how='left',on='Customer_no')

#Make columns 'Top-1 Category %'
df['Top-1 Category %'] = round(100*df['group_sum_1']/df['Total amount'],0)
#drop unnecessary columns
df.drop(['group_sum','group_sum_1'],axis=1,inplace=True)

You can add Top-2 column simular

Upvotes: 1

Related Questions