Python简单的自动化报表生成

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()
    # 客流量，订单ID去重
    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