automaticReport.ipynb
import pandas as pd
from datetime import datetime
data = pd.read_csv(r"order.csv", encoding='gbk', parse_dates=["成交时间"])
data.head()
| 商品ID | 类别ID | 门店编号 | 单价 | 销量 | 成交时间 | 订单ID |
---|
0 | 30006206.0 | 915000003.0 | CDNL | 25.23 | 0.328 | 2018-01-01 | 20170103CDLG000210052759 |
---|
1 | 30163281.0 | 914010000.0 | CDNL | 2.00 | 2.000 | 2018-01-02 | 20170103CDLG000210052759 |
---|
2 | 30200518.0 | 922000000.0 | CDNL | 19.62 | 0.230 | 2018-01-03 | 20170103CDLG000210052759 |
---|
3 | 29989105.0 | 922000000.0 | CDNL | 2.80 | 2.044 | 2018-01-04 | 20170103CDLG000210052759 |
---|
4 | 30179558.0 | 915000100.0 | CDNL | 47.41 | 0.226 | 2018-01-05 | 20170103CDLG000210052759 |
---|
data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3744 entries, 0 to 3743
Data columns (total 7 columns):
商品ID 3478 non-null float64
类别ID 3478 non-null float64
门店编号 3478 non-null object
单价 3478 non-null float64
销量 3478 non-null float64
成交时间 3478 non-null datetime64[ns]
订单ID 3478 non-null object
dtypes: datetime64[ns](1), float64(4), object(2)
memory usage: 204.8+ KB
This_month = data[(data["成交时间"] >= datetime(2018,2,1)) & (data["成交时间"] <= datetime(2018,2,28))]
last_month = data[(data["成交时间"] >= datetime(2018,1,1)) & (data["成交时间"] <= datetime(2018,1,31))]
same_month = data[(data["成交时间"] >= datetime(2017,2,1)) & (data["成交时间"] <= datetime(2017,2,28))]
def get_month_data(data):
sale = (data["单价"]*data["销量"]).sum()
traffic = data["订单ID"].drop_duplicates().count()
s_t = sale/traffic
return (sale, traffic, s_t)
sale_1, traffic_1, s_t_1 = get_month_data(This_month)
sale_2, traffic_2, s_t_2 = get_month_data(last_month)
sale_3, traffic_3, s_t_3 = get_month_data(same_month)
report = pd.DataFrame([[sale_1, sale_2, sale_3],
[traffic_1, traffic_2, traffic_3],
[s_t_1, s_t_2, s_t_3]],
columns = ["本月累计","上月同期","去年同期"],
index = ["销售额", "客流量", "客单价"])
report
| 本月累计 | 上月同期 | 去年同期 |
---|
销售额 | 10412.780070 | 9940.972910 | 8596.313470 |
---|
客流量 | 343.000000 | 315.000000 | 262.000000 |
---|
客单价 | 30.357959 | 31.558644 | 32.810357 |
---|
report["环比"] = report["本月累计"]/report["上月同期"] - 1
report["同比"] = report["本月累计"]/report["去年同期"] - 1
report
| 本月累计 | 上月同期 | 去年同期 | 环比 | 同比 |
---|
销售额 | 10412.780070 | 9940.972910 | 8596.313470 | 0.047461 | 0.211308 |
---|
客流量 | 343.000000 | 315.000000 | 262.000000 | 0.088889 | 0.309160 |
---|
客单价 | 30.357959 | 31.558644 | 32.810357 | -0.038046 | -0.074745 |
---|
report.to_csv(r"report.csv", encoding = "utf-8-sig")
已上传到Github:Python-automatic_report