import lead as lead import numpy as np import pandas as pd import scipy as scipy import sm as sm import statsmodels.api as sm from matplotlib import pyplot as plt import matplotlib from scipy.optimize import curve_fit matplotlib.use('TkAgg') # 1.读取数据 def read_file(file1,file2): raw_data = pd.read_excel("D:\\data\\" + file1) sfbs = pd.read_excel("D:\\data\\" + file2) # 读取省份标识文件 sfbs.rename(columns = { "客户标识" : "客户标志"}, inplace=True) data = raw_data.copy() data1 = pd.merge(sfbs, data, left_on="客户标志", right_on=" 客户标志", how="inner") # 名字不一样貌似会保留两行 data1 = data1.drop(" 客户标志", axis=1) condition = data1["规格"].str.contains("12") # 筛选条件 dat_12 = data1.loc[condition,: ].copy() # 得到12支的数据 return dat_12 if __name__ == "__main__": dat_12 = read_file("葡萄糖酸锌.xlsx","客户省份标识.xlsx") #print(dat_12) # 2进行数据清洗 def data_clear(dat_12): dat_12.dropna(axis="index", how = "any", inplace= True) # 删除缺失值 dat_12 = dat_12.loc[(dat_12["零售数量"]>0) & (dat_12["零售数量"] <100), :] dat_12 = dat_12.loc[(dat_12["进货价"]>0) & (dat_12["进货价"] <100), :] dat_12 = dat_12.loc[(dat_12["零售价"]>0) & (dat_12["零售价"] <100), :] dat_12 = dat_12.loc[(dat_12["生产日期"]> "2017-01-07") & (dat_12["生产日期"] < "2022-05-31"), :] dat_12.loc[:,"生产年月日"] = "" dat_12.loc[:,"生产年月日"] = dat_12["生产日期"].str[0:11] dat_12.loc[:,"零售年月日"] = dat_12["零售日期"].str[0:11] dat_12["生产年月日"] = pd.to_datetime(dat_12["生产年月日"]) dat_12["零售年月日"] = pd.to_datetime(dat_12["零售年月日"]) dat_12.loc[:,"库龄"] = dat_12["零售年月日"] - dat_12["生产年月日"] dat_12["库龄"] = dat_12["库龄"].map(lambda x: x.days) dat_12 = dat_12.loc[(dat_12["库龄"] > 0) & (dat_12["库龄"] <1095), : ] return dat_12 # print(dat_12) def clear(data , x): # 这里x是需要清理的列名，需为字符串 lim_up = data[x].mean() + 1.96* data[x].std() lim_low = data[x].mean() - 1.96 * data[x].std() data = data.loc[ (data[x]<lim_up) & (data[x]>lim_low),:] return data dat_12 = data_clear(dat_12) dat_12 = clear(dat_12, "零售数量") dat_12 = clear(dat_12, "零售价") dat_12 = clear(dat_12, "进货价") dat_12.to_excel("D:\\data\\cleaned_data.xlsx", index=False) # print(dat_12) # 3.计算全国范围店均零售情况 def calculate_country(dat_12): amount = sum(dat_12["零售数量"]) num_store = dat_12.groupby(["客户标志", "门店标志"]).count().shape[0] store_ave = amount / num_store zj = {"销售总量": amount, "门店总数": num_store, "店均销售": store_ave} zj = pd.DataFrame(zj, index=[0]) return zj all_country = calculate_country(dat_12) # print(all_country) # 4.计算出不同门店类型的店均售出 def calculate_diff_class(dat_12): dat1 = dat_12.groupby(["客户标志", "门店标志", "客户类型"], as_index=False)["零售数量"].sum() dat2 = dat1.groupby("客户类型", as_index=False)["零售数量"].sum() dat3 = dat1.groupby("客户类型", as_index=False)["客户标志"].count() dat5 = pd.merge(dat2, dat3, on="客户类型") dat5.rename(columns={"客户标志": "门店数量"}, inplace=True) dat5.loc[:, "店均售出"] = dat5["零售数量"] / dat5["门店数量"] dat5.rename(columns={"客户类型": "范围"}, inplace=True) return dat5 diff_class = calculate_diff_class(dat_12) # print(diff_class) # 5.计算出不同省份的店均售出 def calculate_province(dat_12): dat1 = dat_12.groupby(["客户标志", "门店标志", "省份"], as_index=False)["零售数量"].sum() dat2 = dat1.groupby("省份", as_index=False).agg({"零售数量": "sum", "门店标志": len}) dat2.rename(columns={"门店标志": "门店数量"}, inplace=True) dat2.loc[:, "店均销量"] = dat2["零售数量"] / dat2["门店数量"] dat2.rename(columns={"店均销量": "店均售出", "省份": "范围"}, inplace=True) return dat2 dif_province = calculate_province(dat_12) # print(dif_province) # 6.整理纵向数据情况，数理上，当数据不符合正态分布时，通过对数据求log def prepare_for_longitude(dat_12): dat_12.loc[:, "零售年月"] = dat_12["零售日期"].str[0:7] # 都是开区间 dat_12.loc[:, "加权成本"] = dat_12["进货价"] * dat_12["零售数量"] dat_12.loc[:, "利润率"] = (dat_12["零售价"] - dat_12["进货价"]) / dat_12["进货价"] dat_12.loc[:, "加权总价"] = dat_12["零售价"] * dat_12["零售数量"] dat_12.loc[:, "每笔利润"] = dat_12["加权总价"] - dat_12["加权成本"] dat_12.loc[:, "每笔加权利润率"] = dat_12["利润率"] * da

2个月前