![v2-84acd3f92b49ccf280150bce665622b3_1440w.jpg?source=172ae18b](http://img-03.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-84acd3f92b49ccf280150bce665622b3_1440w.jpg?source=172ae18b)
利用手中一份虚拟的朝阳医院销售数据,为大家浅析一下用Python进行数据分析的过程。
![v2-66916ff0c679fc132118e80bdd939ab3_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-66916ff0c679fc132118e80bdd939ab3_b.jpg)
一、理解数据
首先导入excel文件
#导入excel文件
查看数据的基本信息
salesDf.head(3)
salesDf.shape
二、清洗数据
1.选择子集
#数据清洗
#一、选择子集
subsalesDf=salesDf.loc[:,'销售时间':'销售数量']
subsalesDf
![v2-c0622f2c528900ae98e8800f09f9c499_b.jpg](http://img-03.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-c0622f2c528900ae98e8800f09f9c499_b.jpg)
2.列名重命名
#二、列名重命名
colNameDict={'购药时间':'销售时间'} #构建一个旧列名和新列名对应关系的字典
salesDf.rename(columns=colNameDict,inplace=True) #inplace为True直接在源数据框中进行修改
salesDf
3.缺失值处理
#三、缺失值处理
print("含缺失值:",salesDf.shape)
salesDf=salesDf.dropna(subset=['销售时间','社保卡号'])
print("去掉缺失值后:",salesDf.shape)
![v2-7993e6c7592911929a5900c0a720399d_b.png](http://img-02.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-7993e6c7592911929a5900c0a720399d_b.png)
4.数据类型转换
#四、数据类型转换
salesDf['销售数量']=salesDf['销售数量'].astype(float)
salesDf['应收金额']=salesDf['应收金额'].astype(float)
salesDf['实收金额']=salesDf['实收金额'].astype(float)
salesDf['社保卡号']=salesDf['社保卡号'].astype(object)
salesDf['商品编码']=salesDf['商品编码'].astype(object)
print('转换后的数据类型',salesDf.dtypes)
#时间格式转换
#定义数据分割函数,分割字符串之前要先去重
def splitSalesTime(timecolList):
timeList=[] # timeList[]
for value in timecolList: # timecolList[]
strDate=value.split(' ')[0]
timeList.append(strDate) # timeList[].append(strDate)
timeSer=pd.Series(timeList) # timeSer=pd.Series(timeList[])
return timeSer
timeSer=salesDf.loc[:,'销售时间']
dateSer=splitSalesTime(timeSer)
print(dateSer)
salesDf.loc[:,'销售时间']=dateSer
#将字符串类型转化为日期类型
salesDf.loc[:,'销售时间']=pd.to_datetime(salesDf.loc[:,'销售时间'],format='%Y-%m-%d',errors='coerce')
salesDf.dtypes
![v2-45d1ca330c9586ef6339fc77c5f50c36_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-45d1ca330c9586ef6339fc77c5f50c36_b.jpg)
5.数据排序
#五、数据排序
salesDf=salesDf.sort_values(by='销售时间',ascending=True,na_position='first')
salesDf=salesDf.dropna(subset=['销售时间'])
salesDf=salesDf.reset_index(drop=True) # 重新设置索引
salesDf
![v2-f29c997488c5cdfdcb312ad130f61c62_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic3.zhimg.com/v2-f29c997488c5cdfdcb312ad130f61c62_b.jpg)
6.异常值处理
salesDf.describe()
queryDf=salesDf.loc[:,'销售数量']>0
salesDf=salesDf.loc[queryDf,:]
salesDf.describe()
![v2-2dc2a57abcf45ecb899726b71623cb27_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-2dc2a57abcf45ecb899726b71623cb27_b.jpg)
三、数据建模
业务指标1:月均消费次数
#业务指标1:月均消费次数,总消费次数//月份数
#总消费次数:同一天内,同一个人发生的所有消费算作一次消费
kpil_Df=salesDf.drop_duplicates(subset=['销售时间','社保卡号']) #删除重复数据
total=kpil_Df.shape[0]
#月份数:时间范围//30=月份
kpil_Df=kpil_Df.sort_values(by='销售时间',ascending=True)
kpil_Df=kpil_Df.reset_index(drop=True)
kpil_Df
#时间范围
startTime=kpil_Df.loc[0,'销售时间']
endTime=kpil_Df.loc[total-1,'销售时间']
day=(endTime-startTime).days
month=day//30
month
kpi1=total//month
kpi1
![v2-24a29d8bd5aac860017b6c822d1679fb_b.png](http://img-01.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-24a29d8bd5aac860017b6c822d1679fb_b.png)
业务指标2:月均消费金额
#业务指标2:月均消费金额,总金额//月份数
totalMoneyF=salesDf.loc[:,'实收金额'].sum()
monthMoneyF=totalMoneyF/month
monthMoneyF
![v2-75fdd13de3637b9ac820a0ee9ca6224d_b.png](http://img-02.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-75fdd13de3637b9ac820a0ee9ca6224d_b.png)
业务指标3:客单价
#业务指标3:客单价:总消费金额/总消费次数
pct=totalMoneyF/total
pct
![v2-5659efd1784bd96b81f461096ee081e9_b.png](http://img-03.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-5659efd1784bd96b81f461096ee081e9_b.png)
业务指标4:分组求和
#业务指标4:消费趋势,画图:
#分组求和
GroupBysales=salesDf
GroupBysales.index=GroupBysales['销售时间']
gb=GroupBysales.groupby(GroupBysales.index.month)
gb
gb.sum()
![v2-ed0873e934124635779571f10b353c27_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=c83559a4-1130-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-ed0873e934124635779571f10b353c27_b.jpg)