Data_Functions.py

"""
Authors:
Dominic Keeler, keeler0@purdue.edu
Noah Strawhacker, nstrawha@purdue.edu
Eddie Sun, sun1271@purdue.edu
Sam Norwood, snorwoo@purdue.edu

Description:
The following code gathers parts data from an excel file and formats them into a series of
lists for future calculations. 


Outputs formats:

getFermentersData:
Output is a 2d list, with each entry representing a single fermenter and formatted as
[kWh/day, % of sugar converted, $ per m^3/sec of flow]

getDistillersData:
Output is a 2d list, with each entry representing a single distiller and formatted as
[kWh/day, % of alcohol in exit stream, $ per m^3/sec of flow]

getFiltsAndDHsData:
Output is a 2d list, with each entry representing a single filter/distillation unit and 
formatted as [kWh/day, % by mass of contaminant removed, $ per m^3/sec of flow]

getPumpsData:
Output is a 2d list, with each entry representing a single pump and formatted as 
[efficiency, EPR, $ per m^3/sec of flow]

getPipesData:
Output is a 2d list, with each entry representing a single pump and formatted as 
[DFF, diameter, $ per m (pipe, liquid)]

getDuctworkData:
Output is a 2d list, with each entry representing a single ductwork option and formatted as 
[friction, diameter, $ per m]

getBendsData:
Output is a 2d list, with each entry representing a single bend option and formatted as 
[angle, loss coeff., diameter, $ per bend]

getValvesData:
Output is a 2d list, with each entry representing a single valve and formatted as 
[flow coeff., diameter, $ per valve]

testAllDataFunctions:
Outputs the outputs of all functions above, for error checking
"""


"""--------------- Imports ---------------"""

import pandas as pd


"""--------------- Functions ---------------"""

def getPumpsData():
    # read the parts excel file
    pumpsData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Pumps", usecols = "B:G")
   
    # split the data into sublists
    pumpsData = str(pumpsData).split("\n")
    for i in range(len(pumpsData)):
        pumpsData[i] = pumpsData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del pumpsData[i][0]
    
    # delete the column headers and empty space
    del pumpsData[0]
    del pumpsData[0][-1]

    # convert each entry into a float
    for i in range(len(pumpsData)):
        for n in range(len(pumpsData[i])):
            pumpsData[i][n] = float(pumpsData[i][n])

    # set up a list of corresponding efficiencies
    efficiencies = pumpsData.pop(0)

    # set up a list of corresponding effective performance ratings
    EPRs = []
    for i in range(len(pumpsData)):
        EPRs.append(pumpsData[i].pop(-1))

    # initialize a list of formatted data and create a sublist for each part
    pumpsFormattedData = []
    for i in range(len(efficiencies) * len(EPRs)):
        pumpsFormattedData.append([])
    
    # collect data about each part in each sublist created above
    partIndex = 0
    for i in range(len(EPRs)):
        for n in range(len(efficiencies)):
            pumpsFormattedData[partIndex].append(efficiencies[n])
            pumpsFormattedData[partIndex].append(EPRs[i])
            pumpsFormattedData[partIndex].append(pumpsData[i][n])
            partIndex += 1

    return(pumpsFormattedData)
    """Output is a 2d list, with each entry representing a single pump and formatted as 
    [efficiency, EPR, $ per m^3/sec of flow]"""


def getPipesData():
    # read the parts excel file
    pipesData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Pipe Friction", usecols = "B:H")
   
    # split the data into sublists
    pipesData = str(pipesData).split("\n")
    for i in range(len(pipesData)):
        pipesData[i] = pipesData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del pipesData[i][0]
    
    # delete the column headers and empty space
    del pipesData[0]
    del pipesData[0][-1]

    # convert each entry into a float
    for i in range(len(pipesData)):
        for n in range(len(pipesData[i])):
            pipesData[i][n] = float(pipesData[i][n])

    # set up a list of corresponding DFFs
    DFFs = pipesData.pop(0)

    # set up a list of corresponding diameters
    diameters = []
    for i in range(len(pipesData)):
        diameters.append(pipesData[i].pop(-1))

    # initialize a list of formatted data and create a sublist for each part
    pipesFormattedData = []
    for i in range(len(DFFs) * len(diameters)):
        pipesFormattedData.append([])
    
    # collect data about each part in each sublist created above
    partIndex = 0
    for i in range(len(diameters)):
        for n in range(len(DFFs)):
            pipesFormattedData[partIndex].append(DFFs[n])
            pipesFormattedData[partIndex].append(diameters[i])
            pipesFormattedData[partIndex].append(pipesData[i][n])
            partIndex += 1

    return(pipesFormattedData)
    """Output is a 2d list, with each entry representing a single pump and formatted as 
    [DFF, diameter, $ per m]"""


def getValvesData():
    # read the parts excel file
    valvesData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Valves", usecols = "B:F")
   
    # split the data into sublists
    valvesData = str(valvesData).split("\n")
    for i in range(len(valvesData)):
        valvesData[i] = valvesData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del valvesData[i][0]
    
    # delete the column headers and empty space
    del valvesData[0]
    del valvesData[0][-1]

    # convert each entry into a float
    for i in range(len(valvesData)):
        for n in range(len(valvesData[i])):
            valvesData[i][n] = float(valvesData[i][n])

    # set up a list of corresponding flow coeffs
    flowCoeffs = valvesData.pop(0)

    # set up a list of corresponding diameters
    diameters = []
    for i in range(len(valvesData)):
        diameters.append(valvesData[i].pop(-1))

    # initialize a list of formatted data and create a sublist for each part
    valvesFormattedData = []
    for i in range(len(flowCoeffs) * len(diameters)):
        valvesFormattedData.append([])
    
    # collect data about each part in each sublist created above
    partIndex = 0
    for i in range(len(diameters)):
        for n in range(len(flowCoeffs)):
            valvesFormattedData[partIndex].append(flowCoeffs[n])
            valvesFormattedData[partIndex].append(diameters[i])
            valvesFormattedData[partIndex].append(valvesData[i][n])
            partIndex += 1

    return(valvesFormattedData)
    """Output is a 2d list, with each entry representing a single valve and formatted as 
    [flow coeff., diameter, $ per valve]"""


def getBendsData():
    # read the parts excel file
    bendsData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Bends", usecols = "B:H")
   
    # split the data into sublists
    bendsData = str(bendsData).split("\n")
    for i in range(len(bendsData)):
        bendsData[i] = bendsData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del bendsData[i][0]
    
    # delete empty space and unneeded entries
    del bendsData[0][-1]
    del bendsData[1][-1]

    # convert each entry into a float
    for i in range(len(bendsData)):
        for n in range(len(bendsData[i])):
            bendsData[i][n] = float(bendsData[i][n])

    # set up a list of corresponding bend angles
    angles = bendsData.pop(0)

    # set up a list of corresponding loss coeffs
    lossCoeffs = bendsData.pop(0)

    # set up a list of corresponding diameters
    diameters = []
    for i in range(len(bendsData)):
        diameters.append(bendsData[i].pop(-1))

    # initialize a list of formatted data and create a sublist for each part
    bendsFormattedData = []
    for i in range((len(angles)) * len(diameters)):
        bendsFormattedData.append([])
    
    # collect data about each part in each sublist created above
    partIndex = 0
    for i in range(len(diameters)):
        for n in range(len(angles)):
            bendsFormattedData[partIndex].append(angles[n])
            bendsFormattedData[partIndex].append(lossCoeffs[n])
            bendsFormattedData[partIndex].append(diameters[i])
            bendsFormattedData[partIndex].append(bendsData[i][n])
            partIndex += 1

    return(bendsFormattedData)
    """Output is a 2d list, with each entry representing a single bend option and formatted as 
    [angle, loss coeff., $ per bend]"""


def getFermentersData():
    # read the parts excel file
    fermentersData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Fermenters", usecols = "B:E")

    # split the data into sublists
    fermentersData = str(fermentersData).split("\n")
    for i in range(len(fermentersData)):
        fermentersData[i] = fermentersData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del fermentersData[i][0]

    # initialize a list of formatted data and create a sublist for each part
    fermentersFormattedData = []
    for i in range(len(fermentersData[0])):
        fermentersFormattedData.append([])

    # collect data about each part in each sublist created above
    for i in range(len(fermentersData[0])):
        for n in range(len(fermentersData)):
            # convert numbers into floats
            try:
                fermentersFormattedData[i].append(float(fermentersData[n][i]))
            
            except:
                continue

    return(fermentersFormattedData)
    """Output is a 2d list, with each entry representing a single fermenter and formatted as
    [kWh/day, % of sugar converted, $ per m^3/sec of flow]"""


def getDuctworkData():
    # read the parts excel file
    ductworkData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Ductwork", usecols = "B:C")
   
    # split the data into sublists
    ductworkData = str(ductworkData).split("\n")
    for i in range(len(ductworkData)):
        ductworkData[i] = ductworkData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del ductworkData[i][0]

    # delete column headers
    del ductworkData[0]

    # initialize a list of formatted data and create a sublist for each part
    ductworkFormattedData = []
    for i in range(len(ductworkData)):
        ductworkFormattedData.append([])

    # collect data about each part in each sublist created above
    for i in range(len(ductworkFormattedData)):
        ductworkFormattedData[i].append(0.02)
        ductworkFormattedData[i].append(ductworkData[i][-1])
        ductworkFormattedData[i].append(ductworkData[i][0])

    # convert each entry into a float
    for i in range(len(ductworkFormattedData)):
        for n in range(len(ductworkFormattedData[i])):
            ductworkFormattedData[i][n] = float(ductworkFormattedData[i][n])

    return(ductworkFormattedData)
    """Output is a 2d list, with each entry representing a single ductwork option and formatted as 
    [friction, diameter, $ per m]"""


def getFiltsAndDHsData():
    # read the parts excel file
    filtsAndDHsData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Filters and Dehydrators", usecols = "B:E")
   
    # split the data into sublists
    filtsAndDHsData = str(filtsAndDHsData).split("\n")
    for i in range(len(filtsAndDHsData)):
        filtsAndDHsData[i] = filtsAndDHsData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del filtsAndDHsData[i][0]

    # initialize a list of formatted data and create a sublist for each part
    filtsAndDHsFormattedData = []
    for i in range(len(filtsAndDHsData[0])):
        filtsAndDHsFormattedData.append([])

    # collect data about each part in each sublist created above
    for i in range(len(filtsAndDHsData[0])):
        for n in range(len(filtsAndDHsData)):
            # convert numbers into floats
            try:
                filtsAndDHsFormattedData[i].append(float(filtsAndDHsData[n][i]))
            
            except:
                continue

    return(filtsAndDHsFormattedData)
    """Output is a 2d list, with each entry representing a single filter/distillation unit and 
    formatted as [kWh/day, % by mass of contaminant removed, $ per m^3/sec of flow]"""


def getDistillersData():
    # read the parts excel file
    distillersData = pd.read_excel("Parts Catalog.xlsx", sheet_name = "Distillation Units", usecols = "B:E")
   
    # split the data into sublists
    distillersData = str(distillersData).split("\n")
    for i in range(len(distillersData)):
        distillersData[i] = distillersData[i].split()

        # remove leading characters added by pandas
        if i != 0:
            del distillersData[i][0]

    # initialize a list of formatted data and create a sublist for each part
    distillersFormattedData = []
    for i in range(len(distillersData[0])):
        distillersFormattedData.append([])

    # collect data about each part in each sublist created above
    for i in range(len(distillersData[0])):
        for n in range(len(distillersData)):
            # convert numbers into floats
            try:
                distillersFormattedData[i].append(float(distillersData[n][i]))
            
            except:
                continue

    return(distillersFormattedData)
    """Output is a 2d list, with each entry representing a single distiller and formatted as
    [kWh/day, % of alcohol in exit stream, $ per m^3/sec of flow]"""


def testAllDataFunctions():
    print("FERMENTERS DATA \n")
    print(getFermentersData())
    print("\n")

    print("DISTILLATION UNITS DATA \n")
    print(getDistillersData())
    print("\n")

    print("FILTERS AND DEHYDRATORS DATA \n")
    print(getFiltsAndDHsData())
    print("\n")

    print("PUMPS DATA \n")
    print(getPumpsData())
    print("\n")

    print("PIPE FRICTION DATA \n")
    print(getPipesData())
    print("\n")

    print("DUCTWORK DATA \n")
    print(getDuctworkData())
    print("\n")
    
    print("BENDS DATA \n")
    print(getBendsData())
    print("\n")
    
    print("VALVES DATA \n")
    print(getValvesData())

    return()