导入数据,首先先筛选出2016年7月期间数据,这次也是从数据库直接导入
import sqlalchemy as sac
import pandas as pd
engine=sac.create_engine('mysql+pymysql://user:password@host:3306/ali_sql')
df=pd.read_sql('select * from `ccf_offline_stage1_test_revised`',engine)
先预处理数据
1、先把数据格式调整成日期型,并新增一列‘领券日期’改成%Y-%m的日期格式。这里养成习惯,在筛选日期类型数据前,先做排序再筛选(其实数据源里只有2016年7月的数据)
2、用df.str.split()的分隔字符串的方式,把优惠券金额的数据筛选出来。
3、筛选出来的数据会单独成新的DataFrame,合并到原来的数据中
4、合并后发现数据中存在None值,这边用方法df.fillna(),把None替换成0,再改变整列数据类型成int(原来数据中都是object的类型)。这里需要注意,最初用了df.str.replace替换None,然而是无效的,查询后发现None其实和NaN都同属于缺失值。那么直接用缺失值替换的方法解决
5、数据全部处理后,最终合并成新的DataFrame
df['领券日期']=pd.to_datetime(df['Date_received']).dt.strftime('%Y-%m')
df=df.sort_values(by='领券日期',ascending=True)
df1=df[df['领券日期']=='2016-07']
s1=df1['Discount_rate'].str.split(':',expand=True)
s1=pd.concat([df1,s1],axis=1).rename(columns={1:'金额'})
s1['金额']=s1['金额'].fillna(0)
s2=s1.金额.astype('int')
df1=pd.concat([s1,s2],axis=1)
最后汇总统计
数据处理干净后,汇总统计就比较简单了,df.groupby方法汇总统计
发放优惠券总金额最多=df1.groupby('Merchant_id')['金额'].sum().sort_values(by='金额',ascending=False)[:1]
print(发放优惠券总金额最多)
发放优惠券张数最多=df1.groupby('Merchant_id')[['Coupon_id']].count().sort_values(by='Coupon_id',ascending=False)[:1]
print(发放优惠券张数最多)
完整代码
#读入数据
import sqlalchemy as sac
import pandas as pd
engine=sac.create_engine('mysql+pymysql://user:password@host:3306/ali_sql')
df=pd.read_sql('select * from `ccf_offline_stage1_test_revised`',engine)
#预处理,清洗数据
df['领券日期']=pd.to_datetime(df['Date_received']).dt.strftime('%Y-%m')
df=df.sort_values(by='领券日期',ascending=True)
df1=df[df['领券日期']=='2016-07']
s1=df1['Discount_rate'].str.split(':',expand=True)
s1=pd.concat([df1,s1],axis=1).rename(columns={1:'金额'})
s1['金额']=s1['金额'].fillna(0)
s2=s1.金额.astype('int')
df1=pd.concat([s1,s2],axis=1)
#汇总统计
发放优惠券总金额最多=df1.groupby('Merchant_id')['金额'].sum().sort_values(by='金额',ascending=False)[:1]
print(发放优惠券总金额最多)
发放优惠券张数最多=df1.groupby('Merchant_id')[['Coupon_id']].count().sort_values(by='Coupon_id',ascending=False)[:1]
print(发放优惠券张数最多)
SQL代码
-- 发放优惠券总⾦额最多的商家 SELECt Merchant_id, SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount FROM ccf_offline_stage1_test_revised WHERe Date_received BETWEEN '2016-07-01' AND '2016-07-31' GROUP BY Merchant_id ORDER BY discount_amount DESC LIMIT 1 -- 发放优惠券张数最多的商家 SELECt Merchant_id,COUNT(1) AS cnt FROM ccf_offline_stage1_test_revised WHERe Date_received BETWEEN '2016-07-01' AND '2016-07-31' GROUP BY Merchant_id ORDER BY cnt DESC LIMIT 1
优惠券最多总金额
优惠券最多数量
总体来说,SQL在处理这题时候非常便捷;python的问题在于数据源里都是object类型数据,需要先做预处理,转换数据类型,清洗数据。这部分花费更多代码。
而SQL貌似没任何问题,字符串数据类型在SUBSTRING_INDEX字符串截取后,可直接进行汇总计算



