Home > Blockchain >  Finding a Way to Pull Information From an Excel Table Based on User Inputs Quicker Using Python
Finding a Way to Pull Information From an Excel Table Based on User Inputs Quicker Using Python

Time:11-13

I am going to try and explain this the best way I can, but I expect that I will have to reword a few times.

So what I have is a list of diagrams that have certain specs. I have built a table with the various diagrams and their specs in Excel. I have built a GUI using PySimpleGui for the users to interact with. There are drop down boxes with the specs in them that they will use as inputs for the code to use to search for these diagrams. Here is the code for the GUI

#To create the GUI, first the layout of the window must be set up.
#Set the theme
psg.theme('LightBlue')

#Create a list that will be used for the listbox
diag_num = []
Torq = []

#Set up the layout
left_col = [[psg.Text('Choose the Type: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','CSIR','CSCR', 'PSC', 'Poly', 'SP'], font='Helvetica',
    default_value='Select Option', key='type')],
    [psg.Text('Choose the TORQ Switch: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','282070101', '282070111', '282070117', '282070118', '282070125', '282070201','282070205','282070251',
    '282070261', '282070303', '282070304', '282070352', '282070403', '282070451', '282070600', '282070601', '282070602', '282070701', '282070702', '282070901', '282070902', '282070903', '282080101', 'None'],
     font='Helvetica', default_value='Select Option', key='switch')],
    [psg.Text('Is there a Terminal Board: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'Yes', 'No'], font ='Helvetica', default_value='Select Option',
    key='board')],
    [psg.Text('Choose the Voltage: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Single','Double'], font='Helvetica',
    default_value='Select Option', key='volt')],
    [psg.Text('Choose the Rotation: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Non-Reversible','Reversible'], font='Helvetica',
    default_value='Select Option', key='rot')],
    [psg.Text('Choose the Start: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Total','Half'], font='Helvetica', default_value='Select Option',
    key='start')],
    [psg.Text('Choose the Overload: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Disc','Lace-On','None'], font='Helvetica',
    default_value='Select Option', key='overl')],
    [psg.Text('Choose the Assembly Voltage: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'HI', 'LO', 'FX', 'None'], font='Helvetica',
    default_value='Select Option', key='assymv')],
    [psg.Text('Choose the Lead End: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','CW','CCW', 'REV', 'None'], font='Helvetica', default_value='Select Option',
    key='lead')],
    [psg.Text('Choose an Auxiliary Switch: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'Air', 'Line', 'Mech', 'On/Off', 'Pull Chain',
    'Relay', 'Rocker', 'SIMPAC', 'SSS', 'TRIAC', 'Volt', 'Multiple', 'None'], font='Helvetica', default_value='Select Option', key='auxsw')],
    [psg.Button('Search', font=('Helvetica')), psg.Button('Reset', font=('Helvetica')), psg.Exit(font=('Helvetica'))]]

right_col = [[psg.Listbox(values = diag_num, enable_events=True, font='Helvetica', select_mode = 'LISTBOX_SELECT_MODE_SINGLE', key='ConnDiag', size=(20,22))],
    [psg.Text('', size=(25,0))]]

layout = [[psg.Column(left_col, element_justification='c'), psg.Column(right_col, element_justification='c')]]
#Define the window
win = psg.Window('Connection Diagram Picker', layout, resizable=True)

The program then converts these inputs into 1s and 0s in order to handle whether the user left the drop down box as simple "Select Option" rather than giving an input. That way they can search for diagrams without being exact in their search. Here is the code for that:

if event == 'Search': #Searches based on combo values
        
        '''
        This event happens when the user clicks on the Search button in the GUI.
        When this happens, the program assigns the user inputs a Boolean value based on 1s and 0s.
        It then uses these values to perform basic logical tests to search through the Excel documents,
        to find the Connection diagram that matches that criteria input by the user.
        '''
        win['ConnDiag'].update([''])

        if v['switch'] != 'None' and v['switch'] != 'Select Option':
            switch = int(v['switch'])
        else:
            switch = v['switch']

        # Convert the inputs into Boolean logic assigned 1s and 0s
        if v['type'] == 'Select Option': typeBool = 0
        else: typeBool = 1
        
        if v['switch'] == 'Select Option': switchBool = 0
        else: switchBool = 1

        if v['board'] == 'Select Option': boardBool = 0
        else: boardBool = 1
        
        if v['volt'] == 'Select Option': voltBool = 0
        else: voltBool = 1

        if v['rot'] == 'Select Option': rotBool = 0
        else: rotBool = 1

        if v['start'] == 'Select Option': startBool = 0
        else: startBool = 1

        if v['overl'] == 'Select Option': overlBool = 0
        else: overlBool = 1
        
        if v['assymv'] == 'Select Option': assymvBool = 0
        else: assymvBool = 1

        if v['lead'] == 'Select Option': leadBool = 0
        else: leadBool = 1
        
        if v['auxsw'] == 'Select Option': auxswBool = 0
        else:auxswBool = 1

From there, the code interacts with the Excel table use Pandas. It will take the values in the drop down boxes and perform a search through the Excel table looking for the diagrams that have those specs input by the user. It will then pull up a list of those diagrams and display them in the list box.

# ---------------------------------------------------------------- All True ---------------------------------------------------------------- #

        if typeBool == 1 and switchBool == 1 and boardBool == 1 and voltBool == 1 and rotBool == 1 and startBool == 1 and overlBool == 1 and assymvBool == 1 and leadBool == 1 and auxswBool == 1:

            #Declare looping variables
            i = 0

            diag_num.clear()
            Torq.clear()

            while i < len(workbook):
                
                if workbook['Type:'][i] == v['type'] and workbook['TORQ:'][i] == switch and workbook['Voltage:'][i] == v['volt'] and workbook['Rotation:'][i] == v['rot'] and workbook['Start:'][i] == v['start'] and workbook['Overload:'][i] == v['overl'] and workbook['Assembly Voltage:'][i] == v['assymv'] and workbook['Lead:'][i] == v['lead'] and workbook['Aux Switch:'][i] == v['auxsw']:

                    #Read through the Excel file to find the matching diagrams and append the list.
                    diag_num.append(workbook['Connection Diagram:'][i])
                    Torq.append(workbook['TORQ:'][i])
                
                i  = 1

            diagNum, torq = DelDupl(diag_num,Torq)

            if len(diagNum) == 0:
                psg.popup('ERROR!', 'No connection diagram found!')

            win['ConnDiag'].update(values = diagNum)

            if v['ConnDiag']:

                UserChoice = v['ConnDiag'][0]
                UserChoice = BuildFile(UserChoice)
                readFile(UserChoice)

        # ---------------------------------------------------------------- One True ---------------------------------------------------------------- #

        if typeBool == 1 and switchBool == 0 and boardBool == 0 and voltBool == 0 and rotBool == 0 and startBool == 0 and overlBool == 0 and assymvBool == 0 and leadBool == 0 and auxswBool == 0:

            #Declare looping variables
            i = 0

            diag_num.clear()
            Torq.clear()

            while i < len(workbook):

                if workbook['Type:'][i] == v['type']:

                    #Read through the Excel file to find the matching diagrams and append the list.
                    diag_num.append(workbook['Connection Diagram:'][i])
                    Torq.append(workbook['TORQ:'][i])
                
                i  = 1

            diagNum, torq = DelDupl(diag_num,Torq)

            win['ConnDiag'].update(values = diagNum)

            if v['ConnDiag']:

                UserChoice = v['ConnDiag'][0]
                UserChoice = BuildFile(UserChoice)
                readFile(UserChoice)

This is just a snippet of the code. It keeps going onwards, alternating between 1s and 0s, basically creating a massive truth table that have things happen based on whether the list boxes had inputs by the user or not. The code then checks to see what those inputs are then looks through an Excel table to find the diagrams that have specs that match the input by the user. The functions in this should not make a big difference because they just remove duplicates and search for the pdfs of the diagrams.

So, here is the thing, my code works. It has no problems working so it doesn't need fixed. What I am running into though is that with the number of connection diagrams I have and their specs and me wanting to make sure that I can easily add or subtract more diagrams in the future without messing too much with the code, I have a little over 1000 different combinations these 1s and 0s can take. Each block here takes about 30 lines of code so I will have nearly 40,000 lines of code when I am done. I am about 11,000 lines deep and it is just taking way too long to make. The code runs fine and isn't slow, yet. But I want to find a way to condense the code so I don't go insane trying to copy an paste and edit so much code. I know there has to be a way, but I cannot seem to figure out what to do.

CodePudding user response:

Not sure about what the issue is ..., but something may help here.

Almost all the same options for lot of rows with text and combo, so following way for layout maybe better.

import PySimpleGUI as psg

item_dict = {
    "type"   :  ('Choose the Type: ',
                ['Select Option', 'CSIR', 'CSCR', 'PSC', 'Poly', 'SP']),
    "switch" :  ('Choose the TORQ Switch: ',
                ['Select Option', '282070101', '282070111', '282070117', '282070118',
                                  '282070125', '282070201', '282070205', '282070251',
                                  '282070261', '282070303', '282070304', '282070352',
                                  '282070403', '282070451', '282070600', '282070601',
                                  '282070602', '282070701', '282070702', '282070901',
                                  '282070902', '282070903', '282080101', 'None']),
    "board"  :  ('Is there a Terminal Board: ',
                ['Select Option', 'Yes', 'No']),
    "volt"   :  ('Choose the Voltage: ',
                ['Select Option', 'Single', 'Double']),
    "rot"    :  ('Choose the Rotation: ',
                ['Select Option', 'Non-Reversible', 'Reversible']),
    "start"  :  ('Choose the Start: ',
                ['Select Option', 'Total', 'Half']),
    "overl"  :  ('Choose the Overload: ',
                ['Select Option', 'Disc', 'Lace-On', 'None']),
    "assymv" :  ('Choose the Assembly Voltage: ',
                ['Select Option', 'HI', 'LO', 'FX', 'None']),
    "lead"   :  ('Choose the Lead End: ',
                ['Select Option', 'CW', 'CCW', 'REV', 'None']),
    "auxsw"  :  ('Choose an Auxiliary Switch: ',
                ['Select Option', 'Air', 'Line', 'Mech', 'On/Off', 'Pull Chain',
                                  'Relay', 'Rocker', 'SIMPAC', 'SSS', 'TRIAC',
                                  'Volt', 'Multiple', 'None']),
}

item_list = ["type", "switch", "board", "volt", "rot", "start", "overl", "assymv",
    "lead", "auxsw" ]

font = ('Helvetica', 11)
psg.theme('LightBlue')
psg.set_options(font=font)


left_col = []
for item in item_list:
    item_text, item_list = item_dict[item]
    left_col.append([
        psg.Text(item_text, size=(25, 2)),
        psg.Combo(item_list, default_value=item_list[0], key=item),
    ])
left_col.append([psg.Button(text) for text in ('Search', 'Reset', 'Exit')])

right_col = [
    [psg.Listbox(values=diag_num, enable_events=True, key='ConnDiag', size=(20,22))],
    [psg.Text('', size=(25, 0))],
]

layout = [
    [psg.Column(left_col,  element_justification='c'),
     psg.Column(right_col, element_justification='c')],
]

win = psg.Window('Connection Diagram Picker', layout, resizable=True)

For lot of cases to check if all true, try to use built-in function all, for your case

"""
# Replace code here by only one `all` statement
# Convert the inputs into Boolean logic assigned 1s and 0s
if v['type'] == 'Select Option': typeBool = 0
else: typeBool = 1

if v['switch'] == 'Select Option': switchBool = 0
else: switchBool = 1

...

if v['auxsw'] == 'Select Option': auxswBool = 0
else:auxswBool = 1

# ---------------------------------------------------------------- All True ---------------------------------------------------------------- #

if typeBool == 1 and switchBool == 1 and boardBool == 1 and voltBool == 1 and rotBool == 1 and startBool == 1 and overlBool == 1 and assymvBool == 1 and leadBool == 1 and auxswBool == 1:
"""
if all(map(lambda x:x != 'Select Option', item_list)):

The same if you have good settings for mapping between workbook and item in item_list, function all still work for you, maybe something like this

if all(map(lambda x:workbook['Type:'][i] == v[x], item_list)):

CodePudding user response:

I think you are going about this "the hard way". Anytime you need to enumerate (manually) a gajillion cases, there is likely a better way.

So the below assumes that you have a good data frame with all of the info in it, which I think you do for the purposes of matching the selections.

The key here (if you are using pandas...there are other ways if you had your item descriptions in dictionaries, etc.) is to use a pandas query to match the selections. You can manually create this query (as I did below) from the combo boxes and automatically omit the non-selected ones instead of writing cases out.

Note that I'm just making up a list of buttons here linked to the data, you could make your buttons manually, which is fine. And as I note in comment, if you have other items not related to search criteria in your window values, you will need to omit them from the values before constructing the query.

The below works and does as expected on my machine with the toy data included. Comment back w/ questions! :)

# gui search
import pandas as pd
import PySimpleGUI as psg

# some data

data = {    'Type':     ['CSIR', 'CSIR', 'Poly', 'Poly'],
            'Voltage':  ['Single', 'Single', 'Single', 'Double'],
            'Rotation': ['Reversible', 'Reversible', 'Non-Rev', 'Non-Rev'],
            'Diag':     [45, 63, 2001, 897]}

df = pd.DataFrame(data).set_index('Diag')
print(df)

# Some mock-up buttons....  use the df values...
psg.theme=('LightBlue')

buttons = {}
for c in df.columns:
    default='Select One'
    values = list(set(df[c]))
    b = psg.Combo(values=values, key=c, default_value=default)
    buttons[c] = b

# add the buttons from the list into your window...  This could be done several ways
# you could reference these buttons in your layout list individually, etc...
layout = []
for c in df.columns:
    layout.append([buttons[c],])
layout.append([psg.Button('Search'),])

win = psg.Window('example', layout)
e, v = win.read()
win.close()
print(v)

# make a query string for use with pandas dataframe query...
query = ""
# drop the ones that were not selected...
# note if you have other value objects in v besides the search items,
# you will need to remove them somehow
v = {k:v for k,v in v.items() if v != default}
for k, v in v.items():
    query  = f'{k} == "{v}" and '
query  = "True"  # cheap way of satisfying the last "AND"

print("\nmatches:")
result = df.query(query)
print(result)

print("\n\nget diagrams...:")
for diag in result.index.to_list():
    print('  '   str(diag))

Output

(selecting only "single" from the Voltage Combo):

matches:
      Type Voltage    Rotation
Diag                          
45    CSIR  Single  Reversible
63    CSIR  Single  Reversible
2001  Poly  Single     Non-Rev


get diagrams...:
  45
  63
  2001
  • Related