# -*- coding: utf-8 -*-
"""
Created on Tue Apr 30 10:52:37 2019
@author: chen_lib
"""
 
import pandas as pd
catering_sale = 'D:/Users/chen_lib/Desktop/ceshi.csv' #讀取歷史數(shù)據(jù)
datax = pd.read_csv(catering_sale) #讀取數(shù)據(jù)
#取出不是昨天的數(shù)據(jù)
data = datax.loc[datax['orderdate'] != datetime][:]
'''
import time
## yyyy-mm-dd格式
print (time.strftime("%Y-%m-%d"))
'''
#時間減一天
import datetime
datetime = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
 
 
#保存基本統(tǒng)計量，將常見的統(tǒng)計信息保存為數(shù)據(jù)框
statistics = data.describe() 
#添加行標簽 計算出每個指標的上線下線和四分位間距
statistics.loc['IQR'] = statistics.loc['75%']-statistics.loc['25%'] #四分位數(shù)間距
statistics.loc['UP'] = statistics.loc['75%'] + 1.5*statistics.loc['IQR'] #上限
statistics.loc['DAWN'] = statistics.loc['25%'] - 1.5*statistics.loc['IQR']#下限
#取出data的列名
columns = data.columns.values.tolist()
 
 
'''取出要比較的數(shù)值,放在統(tǒng)計信息表'''
a = data.loc[data['orderdate'] == datetime][columns[1]]#取出第一列
b = data.loc[data['orderdate'] == datetime][columns[2]]#取出第二列
statistics.loc['res'] = [a[1],b[1]]#取出需要比較的當天的數(shù)據(jù) 放入統(tǒng)計信息中
  
 
'''循環(huán)取出結果是否滿足要求''' 
ret = [] 
for i in range(2):
  res = statistics.loc['res'][i]
  max = statistics.loc['UP'][columns[i+1]]#最大值
  min = statistics.loc['DAWN'][columns[i+1]]#最小值
  '''
  #重建三個值的索引，以便比較大小
  res.index = ['ordernum']
  max.index = max['ordernum']
  min.index = min['ordernum']
  #判斷異常值，若大于最大值或者小于最小值則拋出結果為1
  '''
  result1 = res>max
  result2 = res<min
  if result1 =='False' or result2 == 'False':
    ret.append([columns[i+1],1])
  else: 
    ret.append([columns[i+1],0])
  df = pd.DataFrame(ret)
  
	#將文件寫入excel表中
df.to_excel("d:/Users/chen_lib/Desktop/ceshi.xlsx",sheet_name="total",index=False,header=False)