机器学习多场景实战（二 ）

mau = retail_data_clean.groupby('购买年月')['用户ID'].nunique().reset_index()

final['客单价'] = final['金额']/final['用户数']

retail_data_clean.groupby(['购买年月','用户类型'])['金额'].sum().reset_index()
分组统计后得到新老用户购买金额

# 统计每月激活用户数量
activation_count = retail[retail['首次购买年月'] == retail['注册年月']].groupby('注册年月')['用户ID'].count()
# 统计每月注册的用户数
regist_count = retail.groupby('注册年月')['用户ID'].count()
 
 
#按渠道统计每月不同渠道的激活用户数
activation_count = retail[retail['首次购买年月'] == retail['注册年月']].groupby(['注册年月','渠道'])['用户ID'].count()
#按渠道统计每月注册用户数
regist_count = retail.groupby(['注册年月','渠道'])['用户ID'].count()
#计算不同渠道激活率

- 组合数据的一种方法是使用“连接”（concatenation)
  - 连接是指把某行或某列追加到数据中
  - 数据被分成了多份可以使用连接把数据拼接起来
  - 把计算的结果追加到现有数据集，可以使用连接
  
import pandas as pd
df1 = pd.read_csv('concat1.csv')
df2 = pd.read_csv('concat2.csv')
 
# 可以使用concat函数将上面3个DataFrame连接起来，需将3个DataFrame放到同一个列表中
pd.concat([df1,df2])
    A    B    C    D
0   a0   b0   c0   d0
1   a1   b1   c1   d1
2   a2   b2   c2   d2
3   a3   b3   c3   d3
0   a4   b4   c4   d4
1   a5   b5   c5   d5
2   a6   b6   c6   d6
3   a7   b7   c7   d7
 
 
pd.concat([df1,df2],axis=1)
  A   B   C   D   A   B   C   D    
0  a0  b0  c0  d0  a4  b4  c4  d4 
1  a1  b1  c1  d1  a5  b5  c5  d5
2  a2  b2  c2  d2  a6  b6  c6  d6
3  a3  b3  c3  d3  a7  b7  c7  d7
 
df1.append(df2)
  A   B   C   D
0  a0  b0  c0  d0
1  a1  b1  c1  d1
2  a2  b2  c2  d2
3  a3  b3  c3  d3
0  a4  b4  c4  d4
1  a5  b5  c5  d5
2  a6  b6  c6  d6
3  a7  b7  c7  d7
 
 
genres.merge(tracks_subset, on='Id', how='left')
how参数指定连接方式
- how = ’left‘ 对应SQL中的 **left outer**  保留左侧表中的所有key
- how = ’right‘ 对应SQL中的 **right outer**  保留右侧表中的所有key
- how = 'outer' 对应SQL中的 **full outer**   保留左右两侧侧表中的所有key
- how = 'inner' 对应SQL中的 **inner**  只保留左右两侧都有的key
 
stocks_2016.join(stocks_2017, lsuffix='_2016', rsuffix='_2017', how='outer')

drop table if exists food_type;
create table food_type as
SELECT
    item_name,
    price,
    restaurant_id,
    food_category,
CASE
        WHEN food_category IN ( '鸡肉类', '羊肉类', '虾类', '咖喱菜', '鱼肉类', '主菜', '芝士菜' ) THEN
        '主菜' 
        WHEN food_category IN ( '饼', '米饭' ) THEN
        '主食' 
        WHEN food_category IN ( '饮料', '酒', '酸奶' ) THEN
        '饮料' 
        WHEN food_category IN ( '酱', '腌菜' ) THEN
        '佐料' ELSE '小食' 
    END AS food_type 
FROM
    food_category_table;

select a.`Order_Number`,a.`Order_Date`,a.restaurant_id,round(sum(a.Quantity*b.price),2) as total_amount, # 计算订单总金额
sum(case when food_type='主食' then a.Quantity*1 else 0 end) as staple_food_count, # 聚合操作，分别统计出每个菜名类别对应的人数
sum(case when food_type='主菜' then a.Quantity*1 else 0 end) as main_course_count,
sum(case when food_type='饮料' then a.Quantity*1 else 0 end) as drink_count,
sum(case when food_type='小食' then a.Quantity*0.5 else 0 end) as snack_count
from restaurant_orders a join food_type b 
on a.`Item_Name`=b.item_name and a.Restaurant_Id=b.restaurant_id # 把a表和b表合并
group by a.`Order_Number`,a.`Order_Date`,a.Restaurant_Id; # 合并后分组

select c.*,GREATEST(c.staple_food_count,c.main_course_count,c.drink_count,c.snack_count) as max_count from 
(select a.`Order_Number`,a.`Order_Date`,a.restaurant_id,round(sum(a.Quantity*b.price),2) as total_amount,
sum(case when food_type='主食' then a.Quantity*1 else 0 end) as staple_food_count,
sum(case when food_type='主菜' then a.Quantity*1 else 0 end) as main_course_count,
sum(case when food_type='饮料' then a.Quantity*1 else 0 end) as drink_count,
sum(case when food_type='小食' then a.Quantity*0.5 else 0 end) as snack_count
from restaurant_orders a join food_type b 
on a.`Item_Name`=b.item_name and a.Restaurant_Id=b.restaurant_id
group by a.`Order_Number`,a.`Order_Date`,a.Restaurant_Id) c;

floor 向下取整
select c.*,
GREATEST(floor(GREATEST(c.staple_food_count,c.main_course_count,c.drink_count,c.snack_count)),1) as customer_count from 
(select a.`Order_Number`,a.`Order_Date`,a.restaurant_id,round(sum(a.Quantity*b.price),2) as total_amount,
sum(case when food_type='主食' then a.Quantity*1 else 0 end) as staple_food_count,
sum(case when food_type='主菜' then a.Quantity*1 else 0 end) as main_course_count,
sum(case when food_type='饮料' then a.Quantity*1 else 0 end) as drink_count,
sum(case when food_type='小食' then a.Quantity*0.5 else 0 end) as snack_count
from restaurant_orders a join food_type b 
on a.`Item_Name`=b.item_name and a.Restaurant_Id=b.restaurant_id
group by a.`Order_Number`,a.`Order_Date`,a.Restaurant_Id) c;