98 lines
3.4 KiB
Python
98 lines
3.4 KiB
Python
import pandas as pd
|
||
from datetime import datetime
|
||
import os
|
||
|
||
# 配置参数
|
||
CONFIG = {
|
||
'input_csv': r'C:\Users\Administrator\Desktop\European Local O&M\all-active-0519.csv.csv',
|
||
'output_dir': './reports',
|
||
'required_columns': ['car_vins', 'active_status', 'MATERIAL', 'Area']
|
||
}
|
||
|
||
|
||
def validate_data(df):
|
||
"""校验数据完整性"""
|
||
missing_cols = [col for col in CONFIG['required_columns'] if col not in df.columns]
|
||
if missing_cols:
|
||
raise ValueError(f"CSV文件中缺少必要字段: {','.join(missing_cols)}")
|
||
|
||
|
||
def generate_report():
|
||
now = datetime.now()
|
||
today_str = now.strftime("%Y-%m-%d %H:%M:%S")
|
||
filename_date = now.strftime("%Y-%m-%d")
|
||
|
||
try:
|
||
print(f"\n📅 当前时间：{today_str}")
|
||
print("正在读取原始数据...")
|
||
df = pd.read_csv(CONFIG['input_csv'], encoding='utf-8-sig')
|
||
|
||
print("校验数据结构...")
|
||
validate_data(df)
|
||
|
||
# 拆分激活与未激活
|
||
active_df = df[df['active_status'] == 'active'].copy()
|
||
inactive_df = df[df['active_status'] != 'active'].copy()
|
||
|
||
print(f"✅ 已激活车辆总数：{len(active_df)}")
|
||
print(f"❗ 未激活车辆总数：{len(inactive_df)}")
|
||
|
||
if inactive_df.empty:
|
||
print("⚠️ 所有车辆均为激活状态，无需生成未激活清单")
|
||
else:
|
||
# 处理未激活车辆数据
|
||
inactive_df.insert(0, 'ReportDate', filename_date)
|
||
inactive_df.fillna({'Area': '未分配', 'MATERIAL': '未知物料'}, inplace=True)
|
||
|
||
# 保存未激活车辆清单
|
||
os.makedirs(CONFIG['output_dir'], exist_ok=True)
|
||
output_path = f"{CONFIG['output_dir']}/Inactive_Vehicles_{filename_date}.xlsx"
|
||
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
|
||
inactive_df.to_excel(writer, index=False, sheet_name='InactiveVehicles')
|
||
print(f"\n📁 未激活车辆清单已生成：{os.path.abspath(output_path)}")
|
||
|
||
if active_df.empty:
|
||
print("⚠️ 无激活车辆，跳过分析")
|
||
return
|
||
|
||
# 分析激活车辆数据
|
||
active_df.fillna({'Area': '未分配', 'MATERIAL': '未知物料'}, inplace=True)
|
||
|
||
area_summary = (
|
||
active_df.groupby('Area')['car_vins']
|
||
.count()
|
||
.reset_index()
|
||
.rename(columns={'car_vins': 'ActiveCount'})
|
||
.sort_values(by='ActiveCount', ascending=False)
|
||
)
|
||
|
||
material_summary = (
|
||
active_df.groupby('MATERIAL')['car_vins']
|
||
.count()
|
||
.reset_index()
|
||
.rename(columns={'car_vins': 'ActiveCount'})
|
||
.sort_values(by='ActiveCount', ascending=False)
|
||
)
|
||
|
||
# 控制台输出分析结果
|
||
print("\n📊 区域分布统计（已激活车辆）：")
|
||
print(area_summary.to_string(index=False))
|
||
|
||
print("\n📦 销量前10的物料号（已激活车辆）：")
|
||
top10_materials = material_summary.head(10)
|
||
print(top10_materials.to_string(index=False))
|
||
|
||
except FileNotFoundError:
|
||
print(f"❌ 错误：未找到输入文件 {CONFIG['input_csv']}")
|
||
except Exception as e:
|
||
print(f"❌ 发生异常：{str(e)}")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
print("\n===== 奇瑞车辆日报生成器 =====")
|
||
input("请将最新csv放入daily_data文件夹后按回车键开始...")
|
||
|
||
generate_report()
|
||
|
||
input("\n处理完成！按任意键退出...")
|