python實(shí)現(xiàn)xlsx文件分析詳解

更新時間：2018年01月02日 11:06:32 作者：水似冰

這篇文章主要為大家詳細(xì)介紹了python實(shí)現(xiàn)xlsx文件分析，具有一定的參考價值，感興趣的小伙伴們可以參考一下

python腳本實(shí)現(xiàn)xlsx文件解析，供大家參考，具體內(nèi)容如下

環(huán)境配置：

1.系統(tǒng)環(huán)境：Windows 7 64bit
2.編譯環(huán)境：Python3.4.3
3.依賴庫： os sys xlrd re
4.其他工具：none
5.前置條件：待處理的xlsx文件

腳本由來

最近的工作是做測試，而有一項(xiàng)任務(wù)呢，就是分析每天機(jī)器人巡檢時采集的數(shù)據(jù)，包括各種傳感器，CO2、O2、噪聲等等，每天的數(shù)據(jù)也有上千條，通過站控的導(dǎo)出數(shù)據(jù)功能，會把數(shù)據(jù)庫里面導(dǎo)出成xlsx文件，而這項(xiàng)任務(wù)要分析一下當(dāng)天采集的數(shù)據(jù)是否在正常范圍，要計(jì)算攝像頭的識別率和識別準(zhǔn)確率，自己傻呵呵的每天都在手動操作，突然覺得很浪費(fèi)時間，索性寫個python腳本吧，這樣每天一條命令，就能得到自己想看的數(shù)據(jù)結(jié)果。每天至少節(jié)省10分鐘！
這是要解析的xlsx文件：

一般手動就得篩選、排序、打開計(jì)算器計(jì)算 - - 繁瑣枯燥乏味
還是python大法好

代碼淺析

流程圖

腳本demo

#-*- coding:utf-8 -*-
import xlrd
import os
import sys
import logging
import re
#logging.basicConfig(level=logging.DEBUG)

xfile = sys.argv[1]

dateList = []
InspectionType = []
InspectionRresult = []

def load_data():

  CO2Type = []
  O2Type = []
  NoiseType = []
  SupwareType = []
  TowareType = []
  TemperatureType = []
  HumidityType = []
  InfraredType = []

  CO2Result = []
  O2Result = []
  NoiseResult = []
  SupwareResult = []
  TowareResult = []
  TemperatureResult = []
  HumidityResult = []
  InfraredResult = []

  logging.debug(InspectionType)
  logging.debug(InspectionRresult)


  for index, value in enumerate(InspectionType):
    if value == "二氧化碳":                   #CO2Type
      CO2Type.extend(value)
      logging.debug(index)
      logging.debug("CO2 RESULT:  "+InspectionRresult[index])
      CO2Result.append(InspectionRresult[index])

    if value == "氧氣傳感器":                  #O2Type
      O2Type.extend(value)
      O2Result.append(InspectionRresult[index])

    if value == "噪聲傳感器":                  #NoiseType
      NoiseType.extend(value)
      NoiseResult.append(InspectionRresult[index])


    if value == "局放(超聲波測量)":               #SupwareType
      SupwareType.extend(value)
      SupwareResult.append(InspectionRresult[index])

    if value == "局放(地電波測量)":               #SupwareType
      TowareType.extend(value)
      TowareResult.append(InspectionRresult[index])

    if value == "溫度傳感器":                  #TemperatureType
      TemperatureType.extend(value)
      TemperatureResult.append(InspectionRresult[index])      

    if value == "濕度傳感器":                  #TemperatureType
      HumidityType.extend(value)
      HumidityResult.append(InspectionRresult[index])

    if value == "溫度(紅外測量)":                  #TemperatureType
      InfraredType.extend(value)
      InfraredResult.append(InspectionRresult[index])      
  logging.debug(CO2Result)
  logging.debug(O2Result)
  logging.debug(NoiseResult)
  logging.debug(SupwareResult)
  logging.debug(TowareResult)
  logging.debug(TemperatureResult)
  logging.debug(HumidityResult)    
  logging.debug(InfraredResult)   
  return CO2Result,O2Result,NoiseResult,SupwareResult,TowareResult,TemperatureResult,HumidityResult,InfraredResult

def get_data_print(co2,o2,noise,supware,toware,temperature,humidity,infrared):
  co2 = list(map(eval,co2))
  o2 = list(map(eval,o2))
  noise = list(map(eval,noise))
  supware = list(map(eval,supware))
  toware = list(map(eval,toware))
  temperature = list(map(eval,temperature))
  humidity = list(map(eval,humidity))
  infrared = list(map(eval,infrared))

  co2Min = min(co2)
  co2Max = max(co2)
  logging.debug("CO2 min value :~~"+str(co2Min))
  logging.debug("CO2 max value :~~"+str(co2Max))

  o2Min = min(o2)
  o2Max = max(o2)
  noiseMin = min(noise)
  noiseMax = max(noise)

  supwareMin = min(supware)
  supwareMax = max(supware)

  towareMin = min(toware)
  towareMax = max(toware)

  temperatureMin = min(temperature)
  temperatureMax = max(temperature)

  humidityMin = min(humidity)
  humidityMax = max(humidity)

  infraredMin = min(infrared)
  infraredMax = max(infrared)

  print("CO2 values :",co2Min,'~~~~~~~',co2Max)
  print("o2 values :",o2Min,'~~~~~~~',o2Max)
  print("noise values :",noiseMin,'~~~~~~~',noiseMax)
  print("supware values :",supwareMin,'~~~~~~~',supwareMax)
  print("toware values :",towareMin,'~~~~~~~',towareMax)
  print("temperature values :",temperatureMin,'~~~~~~~',temperatureMax)
  print("humidity values :",humidityMin,'~~~~~~~',humidityMax)
  print("infrared values :",infraredMin,'~~~~~~~',infraredMax)

def cal_picture():
  result7to19List = []
  result19to7List = []
  count7to19List = []
  count19to7List = []
  count7to19Dict = {}
  count19to7Dict = {}

  failfind7to19cnt = 0
  failfind19to7cnt = 0
  photoType = []
  photoDateList = []
  allPhotoResult = []

  for index,value in enumerate(InspectionType):            #按照巡檢類型篩選出視覺類，通過索引值同步時間、巡檢結(jié)果
    if value == "開關(guān)(視覺識別)" or value == "旋鈕(視覺識別)" or \
      value == "電流表(視覺識別)" or value == "電壓表(視覺識別)":
      photoType.extend(value)
      photoDateList.append(dateList[index])
      allPhotoResult.append(InspectionRresult[index])
  for index,value in enumerate(photoDateList):
    if value[-8:] > '07:00:00' and value[-8:] < '19:00:00':
      result7to19List.append(allPhotoResult[index])
    if value[-8:] > '19:00:00' or value[-8:] < '7:00:00':
      result19to7List.append(allPhotoResult[index])

  logging.debug(result7to19List[-20:])
  logging.debug(result19to7List[:20])

  noduplicate7to19Set=set(result7to19List)              #里面無重復(fù)項(xiàng)
  for item in noduplicate7to19Set:
    count7to19List.append(result7to19List.count(item))
  logging.debug(count7to19List)
  count7to19Dict= dict(zip(list(noduplicate7to19Set),count7to19List))

  noduplicate19to7Set=set(result19to7List)              
  for item in noduplicate19to7Set:
    count19to7List.append(result19to7List.count(item))
  count19to7Dict= dict(zip(list(noduplicate19to7Set),count19to7List))

  logging.debug(count7to19Dict)

  None7to19cnt = count7to19Dict['']
  all7to19cnt = len(result7to19List)
  None19to7cnt = count19to7Dict['']
  all19to7cnt = len(result19to7List)

  logging.debug(None7to19cnt)

  for key in count7to19Dict:
    if count7to19Dict[key] == 1 :
      failfind7to19cnt = failfind7to19cnt+1
    if re.match('識別失敗:*',key):
      failfind7to19cnt = failfind7to19cnt+ count7to19Dict[key]

  for key in count19to7Dict:
    if count19to7Dict[key] == 1 :
      failfind19to7cnt = failfind19to7cnt+1 
    if re.match('識別失敗:*',key):
      failfind19to7cnt = failfind19to7cnt+count19to7Dict[key]
  logging.debug(all19to7cnt)

  print("7:00 ~~~ 19:00 識別率：",(all7to19cnt-None7to19cnt)/all7to19cnt)
  print("7:00 ~~~ 19:00 識別準(zhǔn)確率：",(all7to19cnt-None7to19cnt-failfind7to19cnt)/(all7to19cnt-None7to19cnt))
  print("19:00 ~~~ 7:00 識別率：",(all19to7cnt-None19to7cnt)/all19to7cnt)
  print("19:00 ~~~ 7:00 識別準(zhǔn)確率：",(all19to7cnt-None19to7cnt-failfind19to7cnt)/(all19to7cnt-None19to7cnt))
#讀取xlsx文件
xlsxdata=xlrd.open_workbook(xfile)
tablepage=xlsxdata.sheets()[0]
dateList.extend(tablepage.col_values(5))
InspectionType.extend(tablepage.col_values(3))
InspectionRresult.extend(tablepage.col_values(6))

cal_picture()
co2,o2,noise,supware,toware,temperature,humidity,infrared=load_data()
get_data_print(co2,o2,noise,supware,toware,temperature,humidity,infrared)

結(jié)果圖