在SQL中创建分类字典并在Python中聚合它们

Question

我有一个相当“跨平台”的问题。希望不要太笼统。你知道吗

我的一个表，比如customers，由我的客户id及其相关的人口统计信息组成。另一个表，比如transaction，包含来自各个商店的顾客的所有购买。 我对用python分析篮子组成和人口统计很感兴趣。因此，我希望将商店作为列，并将商店中给定客户的总和放在我的数据框架中

为了清楚起见

 select *
 from customer
 where id=1 or id=2

给了我

 id     age      gender
 1      35       MALE
 2      57       FEMALE

以及

 select *
 from transaction
 where id=1 or id=2

给了我

 customer_id     shop     amount
 1               2        250
 1               2        500
 2               3        100
 2               7        200
 2               11       125

它应该在（最好）数据帧中结束

 id     age      gender      shop_2     shop_3     shop_7   shop_11
 1      35       MALE        750        0          0        0   
 2      57       FEMALE      0          100        200      125

这样，最后一列就是客户的聚合篮子。你知道吗

我尝试通过以下方式在SQL中为每个客户创建一个关于购买和金额的python字典：

 select customer_id, array_agg(concat(cast(shop as varchar), ' : ', cast(amount as varchar))) as basket
 from transaction
 group by customer_id

导致

 id    basket
 1     ['2 : 250', '2 : 500']
 2     ['3 : 100', '7 : 200', '11 : 125']

可以很容易地连接到客户表上。你知道吗

但是，这个解决方案不是最优的，因为它是字符串而不是[]中的整数。因此，它需要在python中进行大量的操作和循环，以获得我想要的格式。你知道吗

有没有什么方法可以在SQL中聚合购买，从而使python更容易读取和聚合到列中？你知道吗

Answer 1

一个简单的解决方案是在pandas中使用^{}对第二个数据帧进行聚合，然后使用^{}对第一个数据帧进行聚合：

df2 = df2.pivot_table(columns='shop', values='amount', index='customer_id', aggfunc='sum', fill_value=0.0).reset_index()
df = pd.merge(df1, df2, left_on='id', right_on='customer_id')

结果数据帧：

id  age  gender   2   3   7  11
 1   35    MALE 750   0   0   0
 2   57  FEMALE   0 100 200 125