6sigma_results.py

# -*- coding: utf-8 -*-
"""
Created on Thu Jun 23 16:51:10 2016

@author: pawel.cwiek
"""

# ElementTree is the fastest and uses least memory for parsing xml (even better is cElementTree)

#import xml.etree.ElementTree as ET
#
#cabs_xml = ET.parse('cabs.xml')
#
#cabs = cabs_xml.findall('type')
#for item in cabs.iter('urn:schemas-microsoft-com:office:spreadsheet'):
#    print(item)

# http://docs.python-guide.org/en/latest/scenarios/xml/

# ------ using xmltodict because of particular msExcell XML format generated by 6Sigma

#import xmltodict
#
#with open('cabs.xml') as fd:
#    cabs = xmltodict.parse(fd.read())
#    
#sheet = cabs['Workbook']['Worksheet']['Table']['Row']

# xmltodict does not work well with MS xml

# ------ using xml.sax

from xml.sax import saxutils
from xml.sax import parse

# just to have neat console prints for checks/testing
import pprint
pp = pprint.PrettyPrinter(indent=1,depth=2)
printout = pp.pprint  

class ExcelHandler(saxutils.XMLGenerator):
    def __init__(self):
        saxutils.XMLGenerator.__init__(self)
        self.chars=[]
        self.cells=[]
        self.rows=[]
        self.tables=[]
        
    def characters(self, content):
        self.chars.append(content)

    def startElement(self, name, atts):
        if name=="Cell":
            self.chars=[]
        elif name=="Row":
            self.cells=[]
        elif name=="Table":
            self.rows=[]
    
    def endElement(self, name):
        if name=="Cell":
            self.cells.append(''.join(self.chars))
        elif name=="Row":
            self.rows.append(self.cells)
        elif name=="Table":
            self.tables.append(self.rows)

def parse_6sigma_xml_old(cabs_filepath,servers_filepath,cab_cols_filter,server_cols_filter):
    '''
    wrapper over xml.sax
    returns [cabs,servers] - each is a list of dicts
    '''
    import functools
    
    cabsHandler=ExcelHandler()
    serversHandler=ExcelHandler()
    
    parse(cabs_filepath, cabsHandler)
    print()
    parse(servers_filepath, serversHandler)
    print()
    
    cabs_header = cabsHandler.tables[0].pop(0)
    servers_header = serversHandler.tables[0].pop(0)
    
    # clean-up header names parsed from xml
    repls = (('\n', ''), (' ', ''))
    for no, item in enumerate(cabs_header):
        cabs_header[no] = functools.reduce(lambda a, kv: a.replace(*kv), repls,cabs_header[no])

    for no, item in enumerate(servers_header):
        servers_header[no] = functools.reduce(lambda a, kv: a.replace(*kv), repls,servers_header[no])
      
    # print(cabs_header)
    # print(servers_header)
    
    # get column index number of each relevant/needed column (out of all columns available)
    for key, value in cab_cols_filter.items():
        cab_cols_filter[key] = cabs_header.index(value)+1
        # cab_cols_filter[key] = 1+1
        
    for key, value in server_cols_filter.items():
        server_cols_filter[key] = servers_header.index(value)
    
    # get only relevant values from all rows available in xml and assign their column names via dict
    cabs = extract_values(cabsHandler.tables[0],cab_cols_filter)
    servers = extract_values(serversHandler.tables[0],server_cols_filter)   
    
    return [cabs,servers]
    
def parse_6sigma_xml(server_filepath,relevant_server_cols):
    '''
    wrapper over xml.sax
    returns [servers] - list of dicts
    '''
    import functools

    serversHandler=ExcelHandler()

    parse(server_filepath, serversHandler)
    
    servers_header = serversHandler.tables[0].pop(0)
    
    # clean-up header names parsed from xml
    repls = (('\n', ''), (' ', ''))
    for no, item in enumerate(servers_header):
        servers_header[no] = functools.reduce(lambda a, kv: a.replace(*kv), repls,servers_header[no])

    # get column index number of each relevant/needed column (out of all columns available)
    server_cols_filter = relevant_server_cols.copy()
    for key, value in server_cols_filter.items():
        server_cols_filter[key] = servers_header.index(value)
    
    # get only relevant values from all rows available in xml and assign their column names via dict
    servers = extract_values(serversHandler.tables[0],server_cols_filter)   
    
    return servers

def extract_values(unfiltered_row_list,filter_dict):
    '''
    returns list of dicts with key,value pairs only as per filter_dict
    i.e. list of cabinet instances. each instance is represented with a dict and only having keys per filter_dict
    '''
    item_list=[]
    for row in unfiltered_row_list:
        item = filter_dict.copy()
        for parameter,index in item.items():
            try:
                value = float(row[index])
            except:
                value = row[index]
            item[parameter] = value
        item_list.append(item)
    return item_list

def calc_report_old(cabs,servers,max_temp):
    from collections import OrderedDict
    
    condition = 'over_{}degC'.format(max_temp)
    
    report = OrderedDict([
              (condition+'_cabs',None),
              ('total_cabs',None),
              (condition+'_servers',None),
              ('total_servers',None),
              ('average_mean_servers_temp_in',None),
              ('max_mean_servers_temp_in',None),
              ('percent_servers_overheating',None)
              ])
    # report.update()

    report['total_cabs'] = len(cabs)  
    report[condition+'_cabs'] = len([True for item in cabs if item['mean_temp_in'] > max_temp])
    print(sorted([item['mean_temp_in'] for item in cabs if item['mean_temp_in'] > max_temp]))
    report['total_servers'] = len(servers)
    report[condition+'_servers'] = len([True for item in servers if item['mean_temp_in'] > max_temp])
    report['percent_servers_overheating'] = float("{0:.1f}".format(report[condition+'_servers']*100 / report['total_servers']))
    
    temps = [item['mean_temp_in'] for item in servers]
    report['average_mean_servers_temp_in'] = float("{0:.1f}".format(sum(temps) / len(servers)))
    
    report['max_mean_servers_temp_in'] = float("{0:.1f}".format(max(temps)))
    
    return report

def calc_report(file_path,servers,max_temp):
    from collections import OrderedDict
    import os
    
    condition = 'over_{}degC'.format(max_temp)
    
    report = OrderedDict([
              ('filename',file_path.split(os.sep)[-1]),
              (condition+'_cabs',None),
              ('total_cabs',None),
              (condition+'_servers',None),
              ('total_servers',None),
              ('average_mean_servers_temp_in',None),
              ('max_mean_servers_temp_in',None),
              ('percent_servers_overheating',None)
              ])
    
    server_temps_by_cabs = dict()
    for server in servers:
        location = server['location'].split(':')[0]
        location = location.replace(' \n','')
        try:
            server_temps_by_cabs[location] = server_temps_by_cabs[location] + [server['mean_temp_in']] * int(server['u_height'])
        
        except KeyError:
            server_temps_by_cabs[location] = [server['mean_temp_in']] * int(server['u_height'])
            
    cabs_mean_temp = {item[0]:sum(item[1])/len(item[1]) for item in server_temps_by_cabs.items()}
    cabs_mean_over_max = {key:value for key,value in cabs_mean_temp.items() if float("{0:.2f}".format(value)) > max_temp}
    
    '''
    # check of cabinet mean temp calcs
    for key,value in server_temps_by_cabs.items():
        printout('{}({}): {}'.format(key,len(value),value))
    
    import operator
    printout(sorted(cabs_mean_over_max.items(),key=operator.itemgetter(1)))
    '''    
    
    report['total_cabs'] = len(cabs_mean_temp.keys())  
    report[condition+'_cabs'] = len(cabs_mean_over_max.keys())
    
    report['total_servers'] = len(servers)
    report[condition+'_servers'] = len([True for item in servers if item['mean_temp_in'] > max_temp])
    report['percent_servers_overheating'] = float("{0:.1f}".format(report[condition+'_servers']*100 / report['total_servers']))
    
    temps = [item['mean_temp_in'] for item in servers]
    report['average_mean_servers_temp_in'] = float("{0:.1f}".format(sum(temps) / len(servers)))
    
    report['max_mean_servers_temp_in'] = float("{0:.1f}".format(max(temps)))
    
    return report

def find_xmls(dir_path):
    # returns list of filepaths including only xml files (no subfolders)
    import os
    
    files = [fn for fn in next(os.walk(dir_path))[2] if fn.count('.xml')]
    
    return [os.path.join(dir_path,fn) for fn in files]

def reports_to_csv(mydir, rows):
    import csv
    from time import strftime
    import os.path
    
    with open(os.path.join(mydir,strftime('%Y-%m-%d_%H%M%S') + '-bulk_6Sigma_results'+'.csv'), 'w') as csvfile:
        mywriter = csv.DictWriter(csvfile, rows[0].keys(), delimiter=',', lineterminator='\n', quoting=csv.QUOTE_MINIMAL, dialect='excel', extrasaction='ignore')
        mywriter.writeheader()
        for simulation in rows:        
            mywriter.writerow(simulation)
        
    return

def calc_one_file(dir_path,cab_cols_filter,server_cols_filter,max_temp):
    import os   

    cabs_filename = 'cabs.xml'
    servers_filename = 'servers.xml'

    cabs,servers = parse_6sigma_xml_old(os.path.join(dir_path,cabs_filename),os.path.join(dir_path,servers_filename),cab_cols_filter,server_cols_filter)
    report = calc_report_old(cabs,servers,max_temp) 
    return [report]
    
def calc_bulk_files(dir_path,server_cols_filter,max_temp):
    file_paths_list = find_xmls(dir_path)
    reports = []
    for file_path in file_paths_list:
        servers = parse_6sigma_xml(file_path,server_cols_filter)
        reports.append(calc_report(file_path,servers,max_temp))
    return reports
    
def gui(results):
    import tkinter as tk
    
    import tkTreeWitget
    
    headers = [key for key,value in results[0].items()]
    
    values_list = []
    for item in results:
        values = [value for key,value in item.items()]
        values_list.append(tuple(values))
    
    root = tk.Tk()
    root.wm_title("6Sigma results summary")
    mc_listbox = tkTreeWitget.McListBox(headers,values_list)
    mc_listbox.intro_string = 'costam costam costam costam costam \n costam costam costam costam '
    mc_listbox.build()
    
    return root.mainloop()
    
if __name__ == '__main__':

    import os
    # values in filter dict can not have spaces or '\n' newlines. eg. 'CamelCaseNoSpaces%'
    cab_cols_filter = {'kWe_installed':'Cabinet Power kW',
                       'mean_temp_in':'Mean Temperature In C'
                       }
    server_cols_filter = {'location':'Location ID',
                          'u_height':'Height U',
                           'name_plate_power':'Name Plate Power kW',
                           'heat_power_ratio':'Heat Power Factor %',
                           'mean_temp_in':'Mean Temperature In C'
                           }
    max_temp = 27.0
    reports = []
    dir_path = os.getcwd()
    
#    dir_path = 'C:/Python34/WinPython-32bit-3.4.3.6/_my projects/6Sigma results/_old'
#    reports.extend(calc_one_file(dir_path,cab_cols_filter,server_cols_filter,max_temp))

    dir_path = 'C:/Python34/WinPython-32bit-3.4.3.6/_my projects/6Sigma results'
    reports.extend(calc_bulk_files(dir_path,server_cols_filter,max_temp))
    
    gui(reports)
    
    # reports_to_csv(dir_path,reports)

    #dir_path = 'C:/install'
    #printout(cabs)
    #printout(servers)
    #a = calc_report(cabs,servers,27.0)    
    #b = calc_report(cabs,servers,26.0)
    
    #reports_to_csv(dir_path,[a,b])
    
    #printout(summarise(cabs,servers,27.0))