Demo entry 5862384

pareto

   

Submitted by anonymous on Aug 04, 2016 at 09:37
Language: Python 3. Code size: 4.6 kB.

import xlrd, xlwt, os
from xlwt import *
directoryName = r'C:\TEMP\SO\SO18623'
for path, dirs, files in os.walk(directoryName):
    for f in files :
        if  f.endswith(".xls"):         
            myBook = xlrd.open_workbook(directoryName + '\\' + f)

style2 = XFStyle()
style2.num_format_str = '0.00%'

#trouver le numero de la colonne test name
fail = myBook.sheet_by_index(0)

liste_nomcolonnes = []
for col in range(0,fail.ncols) :
    liste_nomcolonnes.append(str(fail.cell(0,col).value))
num_col_test = liste_nomcolonnes.index('test name')
num_col_temp = liste_nomcolonnes.index('TEMP')
num_col_testnbr = liste_nomcolonnes.index('test')
nbre_pcs_RT =0
nbre_pcs_HT = 0
nbre_pcs_CT = 0


#met toute la colone des fail dans la liste liste_erreurs
liste_erreurs = []
for a in range(1, fail.nrows) :
    tuple_passage = (str(fail.cell(a,num_col_temp).value), (fail.cell(a,num_col_testnbr).value),str(fail.cell(a,num_col_test).value))
    liste_erreurs.append(tuple_passage)
    if str(fail.cell(a,num_col_temp).value) == 'RT' :
        nbre_pcs_RT += 1
    if str(fail.cell(a,num_col_temp).value) == 'HT' :
        nbre_pcs_HT += 1 
    if str(fail.cell(a,num_col_temp).value) == 'CT' :
        nbre_pcs_CT +=1

#supprime les éléments vides
liste_erreurs = [(a,b,c) for a,b,c in liste_erreurs if c !='']




#trie les erreurs par nombre d'erreurs
pareto_RT = {}
pareto_CT = {}
pareto_HT = {}
somme_IDLE_RT = 0
somme_IDLE_CT = 0
somme_IDLE_HT = 0
somme_erreurs_RT = 0
somme_erreurs_CT = 0
somme_erreurs_HT = 0
for a,b,c in liste_erreurs:
    if a == 'RT' :
        if pareto_RT.has_key((b,c)) == False :
            pareto_RT[(b,c)] = 1
        else :
            pareto_RT[(b,c)] += 1
        somme_erreurs_RT += 1
    if a == 'CT':
        if pareto_CT.has_key((b,c)) == False :
            pareto_CT[(b,c)] = 1
        else :
            pareto_CT[(b,c)] += 1
        somme_erreurs_CT += 1
    if a == 'HT':
        if pareto_HT.has_key((b,c)) == False :
            pareto_HT[(b,c)] = 1
        else :
            pareto_HT[(b,c)] += 1
        somme_erreurs_HT += 1


#creation du nouvel excel RT  
pareto1 = xlwt.Workbook()
sheet1 = pareto1.add_sheet('pareto RT')
sheet1.write(0,0,'test number')
sheet1.write(0,1,'test name')
sheet1.write(0,2,'nombre')
sheet1.write(0,3,'pourcentage d\'erreur')
sheet1.col(0).width = 256*20
sheet1.col(1).width = 256*50
sheet1.col(2).width = 256*10
sheet1.col(3).width = 256*30
sheet1.write(0,5,'nombre total :')
sheet1.write(0,6,nbre_pcs_RT)
sheet1.write(3,4,'total d\'erreurs :')
sheet1.write(3,5,somme_erreurs_RT)
sheet1.write(3,6,float(somme_erreurs_RT) / nbre_pcs_RT, style2)
#sheet1.write(4,4,'total IDLE')
#sheet1.write(4,5,somme_IDLE)
#sheet1.write(4,6,float(somme_IDLE) / nb_pcs_RT, style2)




d = 1
for b,c in pareto_RT.keys():
    sheet1.write(d,0,b)
    sheet1.write(d,1,c)
    sheet1.write(d,2,pareto_RT[(b,c)])
    sheet1.write(d,3,float(pareto_RT[(b,c)]) / (nbre_pcs_RT),style2)
    d +=1
    
#excel CT
sheet2 = pareto1.add_sheet('pareto CT')
sheet2.write(0,0,'test number')
sheet2.write(0,1,'test name')
sheet2.write(0,2,'nombre')
sheet2.write(0,3,'pourcentage d\'erreur')
sheet2.col(0).width = 256*20
sheet2.col(1).width = 256*50
sheet2.col(2).width = 256*10
sheet2.col(3).width = 256*30
sheet2.write(0,5,'nombre total :')
sheet2.write(0,6,nbre_pcs_CT)
sheet2.write(3,4,'total d\'erreurs :')
sheet2.write(3,5,somme_erreurs_CT)
sheet2.write(3,6,float(somme_erreurs_CT) / nbre_pcs_CT, style2) 


d = 1
for b,c in pareto_CT.keys():
    sheet2.write(d,0,b)
    sheet2.write(d,1,c)
    sheet2.write(d,2,pareto_CT[(b,c)])
    sheet2.write(d,3,float(pareto_CT[(b,c)]) / (nbre_pcs_RT),style2)
    d +=1


#excel HT
sheet3 = pareto1.add_sheet('pareto HT')
sheet3.write(0,0,'test number')
sheet3.write(0,1,'test name')
sheet3.write(0,2,'nombre')
sheet3.write(0,3,'pourcentage d\'erreur')
sheet3.col(0).width = 256*20
sheet3.col(1).width = 256*50
sheet3.col(2).width = 256*10
sheet3.col(3).width = 256*30
sheet3.write(0,5,'nombre total :')
sheet3.write(0,6,nbre_pcs_CT)
sheet3.write(3,4,'total d\'erreurs :')
sheet3.write(3,5,somme_erreurs_HT)
sheet3.write(3,6,float(somme_erreurs_HT) / nbre_pcs_HT, style2) 


d = 1
for b,c in pareto_HT.keys():
    sheet3.write(d,0,b)
    sheet3.write(d,1,c)
    sheet3.write(d,2,pareto_HT[(b,c)])
    sheet3.write(d,3,float(pareto_HT[(b,c)]) / (nbre_pcs_HT),style2)
    d +=1



pareto1.save(directoryName + '\\' + 'pareto1.xls')

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).