我有一个数据框 - df 如下:
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']})
现在,我需要按客户编号分组并获得总金额、前 1、2、3 类别及其占总金额的百分比。 注意:在我的 Toy 数据集中,我只有 2 个类别,在我的原始数据中我有更多,我需要选择前 5 个类别。 我的数据框应该是这样的:
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']})
请求您的帮助以实现它。
注意: 1) 通过针对每个客户对所有类别进行分组并明智地汇总每个类别客户的金额来选择最高类别。哪个类别的 Top 1 类别数量更多,类似地,下一个类别是 Top 2 等等 2) Top 1 类别百分比:每个类别的总金额除以总金额再乘以 100。这是针对每个客户的。对于 Top 2 类别也是如此。
请您参考如下方法:
试试这个:
#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)
您可以添加 Top-2 列模拟
