import pandas as pd
import numpy as np
# 读取数据
df = pd.read_csv('sales_data.csv')
df['订单日期'] = pd.to_datetime(df['订单日期'])
# 获取当前最新日期和去年同期的截止日期
current_date = df['订单日期'].max()
last_year_same_day = current_date - pd.DateOffset(years=1)
# 划分时间段
current_period = df[df['订单日期'] > last_year_same_day]
last_period = df[df['订单日期'] <= last_year_same_day]
def safe_division(x, y, default=0):
"""安全除法，处理除零情况"""
try:
return x / y if y != 0 else default
except:
return default
def calculate_metrics(df):
"""计算关键指标"""
sales_total = df['销售金额'].sum()
return {
'销售总额': df['实际金额'].sum(),
'订单数': len(df),
'平均单价': safe_division(df['实际金额'].sum(), len(df)),
'总折扣额': df['折扣金额'].sum(),
'平均折扣率': safe_division(df['折扣金额'].sum(), sales_total) * 100 if sales_total > 0 else 0,
}
def calculate_yoy_change(current, previous, default=0):
"""计算同比变化"""
if previous == 0:
return default if current == 0 else 100 # 如果基期为0，当期有值则为100%增长
return ((current / previous) - 1) * 100
def dimension_comparison(current_period, last_period, dimension):
"""各维度同比分析"""
# 当期维度分析
current_dim = current_period.groupby(dimension).agg({
'实际金额': 'sum',
'订单编号': 'count',
'折扣金额': 'sum'
}).round(2)
# 同期维度分析
last_dim = last_period.groupby(dimension).agg({
'实际金额': 'sum',
'订单编号': 'count',
'折扣金额': 'sum'
}).round(2)
# 合并所有唯一维度值
all_dimensions = sorted(set(current_dim.index) | set(last_dim.index))
# 初始化结果DataFrame
comparison = pd.DataFrame(index=all_dimensions)
# 填充数据，处理缺失值
comparison['当期销售额'] = current_dim.get('实际金额', pd.Series(0, index=all_dimensions))
comparison['同期销售额'] = last_dim.get('实际金额', pd.Series(0, index=all_dimensions))
comparison['当期订单数'] = current_dim.get('订单编号', pd.Series(0, index=all_dimensions))
comparison['同期订单数'] = last_dim.get('订单编号', pd.Series(0, index=all_dimensions))
# 计算同比变化
comparison['销售额同比'] = [
calculate_yoy_change(curr, prev)
for curr, prev in zip(comparison['当期销售额'], comparison['同期销售额'])
]
comparison['订单数同比'] = [
calculate_yoy_change(curr, prev)
for curr, prev in zip(comparison['当期订单数'], comparison['同期订单数'])
]
return comparison.sort_values('销售额同比', ascending=False).round(2)
# 1. 整体指标对比
current_metrics = calculate_metrics(current_period)
last_metrics = calculate_metrics(last_period)
overall_comparison = pd.DataFrame({
'指标': current_metrics.keys(),
'当期': current_metrics.values(),
'同期': last_metrics.values()
})
# 添加同比变化
overall_comparison['同比变化%'] = [
calculate_yoy_change(current_metrics[key], last_metrics[key])
for key in current_metrics.keys()
]
# 2. 产品维度对比
product_comparison = dimension_comparison(current_period, last_period, '产品名称')
# 3. 客户维度对比
customer_comparison = dimension_comparison(current_period, last_period, '客户名称')
# 4. 区域维度对比
region_comparison = dimension_comparison(current_period, last_period, '客户所在地')
# 5. 销售人员维度对比
sales_comparison = dimension_comparison(current_period, last_period, '销售人员')
# 计算各维度对总体销售差异的贡献度
def calculate_contribution(current_period, last_period, dimension):
current_sales = current_period.groupby(dimension)['实际金额'].sum()
last_sales = last_period.groupby(dimension)['实际金额'].sum()
total_diff = current_sales.sum() - last_sales.sum()
if total_diff == 0:
return pd.Series(0, index=current_sales.index)
dim_diff = current_sales - last_sales.fillna(0)
contribution = (dim_diff / abs(total_diff) * 100).round(2)
return contribution.sort_values(ascending=False)
# 打印分析结果
print("\n===== 整体指标同比分析 =====")
print(overall_comparison.round(2))
print("\n===== 产品维度同比分析 =====")
print(product_comparison)
print("\n===== 区域维度同比分析 =====")
print(region_comparison)
# 计算各维度贡献度
product_contribution = calculate_contribution(current_period, last_period, '产品名称')
region_contribution = calculate_contribution(current_period, last_period, '客户所在地')
customer_contribution = calculate_contribution(current_period, last_period, '客户名称')
print("\n===== 销售差异贡献度分析 =====")
print("\n产品维度贡献度：")
print(product_contribution)
print("\n区域维度贡献度：")
print(region_contribution)