栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Python

【学习记录】阿里天池SQL练习题3-python与SQL方式

Python 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

【学习记录】阿里天池SQL练习题3-python与SQL方式

练习排名和汇总统计 数据来源:https://tianchi.aliyun.com/competition/entrance/231593/information 使用Coupon Usage Data for O2O中的数据集《ccf_offline_stage1_test_revised.csv》,试分别找出在2016年7月期间,发放优惠券总金额最多和发放优惠券张数最多的商家。(这里只考虑满减的金额,不考虑打几折的优惠券)

导入数据,首先先筛选出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字符串截取后,可直接进行汇总计算

 

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/918300.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号