Python合并两个数据框和组

Question

我需要通过合并 df 和 df1 来导出上述预期的 df3，并且需要以下统计信息:

合并时需要注意:如果“Desc1”中没有值，则应从“Desc2”中选取值

1. 每张卡的名称和每个类别的交叉选项卡以及每个类别的支出金额百分比。 (即)各类别金额总和/各卡名金额总和
2. 根据消费金额，每张卡名称的前 2 个类别(所有这些都应按卡分组)可以帮忙吗？还建议我们可以从 df3 推断出哪些更多统计数据？

我有 2 个 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']})

第二个 DF:

df1 = pd.DataFrame({"Group_code": ['123', '123','456', '678','678', '434', '987','421'],
                 "Desc1": ['Electrical', 'Electrical','Hardware', 'House', 'House', 'Car','','Toy'],
                "Desc2":['Electricals111','Electricals123','Hardware112','House232','House112',
                        'Car','Bike','Toy']})

预期 DF:

df3 = 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','','House','Bike']})

Answer 1

df4 = pd.merge(df, df1[['Desc1','Group_code']].drop_duplicates(), how='left', on=['Group_code'])
df4=df4[['Amount','Card_name','Card_no','Desc1','Customer_no','Group_code']] # Reordering of column sequence
df4=df4.rename(columns={'Desc1':'Category'})
df4=df4.fillna({'Category':''})
df4
  Amount Card_name Card_no    Category Customer_no Group_code
0    100       AAA     111  Electrical           1        123
1    240       AAA     222  Electrical           1        123
2    450       BBB     333    Hardware           1        456
3    212       CCC     444       House           2        678
4    432       AAA     555  Electrical           2        123
5    123       DDD     666         Car           6        434
6    543       EEE     777       House           7        678
7    567       BBB     888                       8        365
8    232       CCC     999       House           9        678
9    453       CCC     000        Bike          10        987