Python Forum
Python script to summarize excel tables, then output a composite table? i'm a total n
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python script to summarize excel tables, then output a composite table? i'm a total n
#1
So I have a production Limits management system, basically a huge table that has rows for each permutation of [Plan | subgroup | Tool | Item | Statistic | Limit] that will trigger automated events.

The problem I'm seeing is that we are tracking the limits in a table that just has [Tool | Item | limit], making it more reasonable for humans to interact with. In order to make an edit to the actual system table, a user must identify all permutations and then edit all the required rows. a lot of human glue and a lot of room for human error.

I'm wondering if python can help here? given some source tables, can it create a final edit table with all permutations? I'm able to run a query and get a list of all [Items | Plans | subgroup], basically a pivot summary.

Here's an example of what I'm looking at. first pic: a pivot summary of permutations of Item|Plan|SubGroup. noting that not all Items are in all Plans and not all SubGroups are in all Plans.

pivot summary of Item|Plan|SubGroup
[Image: 75OED.png]

Second table is an example of how the Operators manage the limits, on an excel sheet for easy reference.

Limits table
[Image: v5sw9.png]

The result I'm trying to figure out is how to script so that there's a final output table with all permutations of the source tables.

Any advice on where to start with this in Python? (or if there's a better way?)
Reply
#2
https://docs.python.org/3/library/iterto...ls.product

>>> import itertools
>>> tools = ['X1701', 'X1702', 'X1901']
>>> items = ['WaferCount', 'PlasmaTime']
>>> subgroups = ['Default', 'Step2', 'Test', 'Low', 'High']
>>> limits = [100, 200, 35, 65]
>>> statistics = ['max']
>>> list(itertools.product(tools, items, subgroups, limits, statistics))
[('X1701', 'WaferCount', 'Default', 100, 'max'), ('X1701', 'WaferCount', 'Default', 200, 'max'), ('X1701', 'WaferCount', 'Default', 35, 'max'), ('X1701', 'WaferCount', 'Default', 65, 'max'), ('X1701', 'WaferCount', 'Step2', 100, 'max'), ('X1701', 'WaferCount', 'Step2', 200, 'max'), ('X1701', 'WaferCount', 'Step2', 35, 'max'), ('X1701', 'WaferCount', 'Step2', 65, 'max'), ('X1701', 'WaferCount', 'Test', 100, 'max'), ('X1701', 'WaferCount', 'Test', 200, 'max'), ('X1701', 'WaferCount', 'Test', 35, 'max'), ('X1701', 'WaferCount', 'Test', 65, 'max'), ('X1701', 'WaferCount', 'Low', 100, 'max'), ('X1701', 'WaferCount', 'Low', 200, 'max'), ('X1701', 'WaferCount', 'Low', 35, 'max'), ('X1701', 'WaferCount', 'Low', 65, 'max'), ('X1701', 'WaferCount', 'High', 100, 'max'), ('X1701', 'WaferCount', 'High', 200, 'max'), ('X1701', 'WaferCount', 'High', 35, 'max'), ('X1701', 'WaferCount', 'High', 65, 'max'), ('X1701', 'PlasmaTime', 'Default', 100, 'max'), ('X1701', 'PlasmaTime', 'Default', 200, 'max'), ('X1701', 'PlasmaTime', 'Default', 35, 'max'), ('X1701', 'PlasmaTime', 'Default', 65, 'max'), ('X1701', 'PlasmaTime', 'Step2', 100, 'max'), ('X1701', 'PlasmaTime', 'Step2', 200, 'max'), ('X1701', 'PlasmaTime', 'Step2', 35, 'max'), ('X1701', 'PlasmaTime', 'Step2', 65, 'max'), ('X1701', 'PlasmaTime', 'Test', 100, 'max'), ('X1701', 'PlasmaTime', 'Test', 200, 'max'), ('X1701', 'PlasmaTime', 'Test', 35, 'max'), ('X1701', 'PlasmaTime', 'Test', 65, 'max'), ('X1701', 'PlasmaTime', 'Low', 100, 'max'), ('X1701', 'PlasmaTime', 'Low', 200, 'max'), ('X1701', 'PlasmaTime', 'Low', 35, 'max'), ('X1701', 'PlasmaTime', 'Low', 65, 'max'), ('X1701', 'PlasmaTime', 'High', 100, 'max'), ('X1701', 'PlasmaTime', 'High', 200, 'max'), ('X1701', 'PlasmaTime', 'High', 35, 'max'), ('X1701', 'PlasmaTime', 'High', 65, 'max'), ('X1702', 'WaferCount', 'Default', 100, 'max'), ('X1702', 'WaferCount', 'Default', 200, 'max'), ('X1702', 'WaferCount', 'Default', 35, 'max'), ('X1702', 'WaferCount', 'Default', 65, 'max'), ('X1702', 'WaferCount', 'Step2', 100, 'max'), ('X1702', 'WaferCount', 'Step2', 200, 'max'), ('X1702', 'WaferCount', 'Step2', 35, 'max'), ('X1702', 'WaferCount', 'Step2', 65, 'max'), ('X1702', 'WaferCount', 'Test', 100, 'max'), ('X1702', 'WaferCount', 'Test', 200, 'max'), ('X1702', 'WaferCount', 'Test', 35, 'max'), ('X1702', 'WaferCount', 'Test', 65, 'max'), ('X1702', 'WaferCount', 'Low', 100, 'max'), ('X1702', 'WaferCount', 'Low', 200, 'max'), ('X1702', 'WaferCount', 'Low', 35, 'max'), ('X1702', 'WaferCount', 'Low', 65, 'max'), ('X1702', 'WaferCount', 'High', 100, 'max'), ('X1702', 'WaferCount', 'High', 200, 'max'), ('X1702', 'WaferCount', 'High', 35, 'max'), ('X1702', 'WaferCount', 'High', 65, 'max'), ('X1702', 'PlasmaTime', 'Default', 100, 'max'), ('X1702', 'PlasmaTime', 'Default', 200, 'max'), ('X1702', 'PlasmaTime', 'Default', 35, 'max'), ('X1702', 'PlasmaTime', 'Default', 65, 'max'), ('X1702', 'PlasmaTime', 'Step2', 100, 'max'), ('X1702', 'PlasmaTime', 'Step2', 200, 'max'), ('X1702', 'PlasmaTime', 'Step2', 35, 'max'), ('X1702', 'PlasmaTime', 'Step2', 65, 'max'), ('X1702', 'PlasmaTime', 'Test', 100, 'max'), ('X1702', 'PlasmaTime', 'Test', 200, 'max'), ('X1702', 'PlasmaTime', 'Test', 35, 'max'), ('X1702', 'PlasmaTime', 'Test', 65, 'max'), ('X1702', 'PlasmaTime', 'Low', 100, 'max'), ('X1702', 'PlasmaTime', 'Low', 200, 'max'), ('X1702', 'PlasmaTime', 'Low', 35, 'max'), ('X1702', 'PlasmaTime', 'Low', 65, 'max'), ('X1702', 'PlasmaTime', 'High', 100, 'max'), ('X1702', 'PlasmaTime', 'High', 200, 'max'), ('X1702', 'PlasmaTime', 'High', 35, 'max'), ('X1702', 'PlasmaTime', 'High', 65, 'max'), ('X1901', 'WaferCount', 'Default', 100, 'max'), ('X1901', 'WaferCount', 'Default', 200, 'max'), ('X1901', 'WaferCount', 'Default', 35, 'max'), ('X1901', 'WaferCount', 'Default', 65, 'max'), ('X1901', 'WaferCount', 'Step2', 100, 'max'), ('X1901', 'WaferCount', 'Step2', 200, 'max'), ('X1901', 'WaferCount', 'Step2', 35, 'max'), ('X1901', 'WaferCount', 'Step2', 65, 'max'), ('X1901', 'WaferCount', 'Test', 100, 'max'), ('X1901', 'WaferCount', 'Test', 200, 'max'), ('X1901', 'WaferCount', 'Test', 35, 'max'), ('X1901', 'WaferCount', 'Test', 65, 'max'), ('X1901', 'WaferCount', 'Low', 100, 'max'), ('X1901', 'WaferCount', 'Low', 200, 'max'), ('X1901', 'WaferCount', 'Low', 35, 'max'), ('X1901', 'WaferCount', 'Low', 65, 'max'), ('X1901', 'WaferCount', 'High', 100, 'max'), ('X1901', 'WaferCount', 'High', 200, 'max'), ('X1901', 'WaferCount', 'High', 35, 'max'), ('X1901', 'WaferCount', 'High', 65, 'max'), ('X1901', 'PlasmaTime', 'Default', 100, 'max'), ('X1901', 'PlasmaTime', 'Default', 200, 'max'), ('X1901', 'PlasmaTime', 'Default', 35, 'max'), ('X1901', 'PlasmaTime', 'Default', 65, 'max'), ('X1901', 'PlasmaTime', 'Step2', 100, 'max'), ('X1901', 'PlasmaTime', 'Step2', 200, 'max'), ('X1901', 'PlasmaTime', 'Step2', 35, 'max'), ('X1901', 'PlasmaTime', 'Step2', 65, 'max'), ('X1901', 'PlasmaTime', 'Test', 100, 'max'), ('X1901', 'PlasmaTime', 'Test', 200, 'max'), ('X1901', 'PlasmaTime', 'Test', 35, 'max'), ('X1901', 'PlasmaTime', 'Test', 65, 'max'), ('X1901', 'PlasmaTime', 'Low', 100, 'max'), ('X1901', 'PlasmaTime', 'Low', 200, 'max'), ('X1901', 'PlasmaTime', 'Low', 35, 'max'), ('X1901', 'PlasmaTime', 'Low', 65, 'max'), ('X1901', 'PlasmaTime', 'High', 100, 'max'), ('X1901', 'PlasmaTime', 'High', 200, 'max'), ('X1901', 'PlasmaTime', 'High', 35, 'max'), ('X1901', 'PlasmaTime', 'High', 65, 'max')]
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Need help with saving output into an excel file Beyondfacts 4 269 Mar-22-2021, 11:51 AM
Last Post: jefsummers
  random fiber distribution for composite analysis pvdivakarraju 1 178 Jan-21-2021, 07:42 PM
Last Post: Larz60+
  Load data from One oracle Table to Multiple tables amy83 1 274 Dec-02-2020, 01:57 AM
Last Post: Larz60+
  I try to import data from Excel table to Word Template. NewbiePyPy 0 388 Oct-21-2020, 12:25 PM
Last Post: NewbiePyPy
  Import output from python script to another varietyjones 1 361 Oct-12-2020, 09:07 PM
Last Post: bowlofred
  Fetch Oracle DB rows & print it in HTML file with table's col headers in table format tssr_2001 1 665 Sep-04-2020, 01:39 PM
Last Post: ibreeden
  sports Stats > table output loop problems paulfearn100 3 539 Jul-22-2020, 03:21 AM
Last Post: c_rutherford
  Excel tables and move rows jdos 6 840 Jul-02-2020, 05:52 AM
Last Post: jdos
  How to kill a bash script running as root from a python script? jc_lafleur 4 940 Jun-26-2020, 10:50 PM
Last Post: jc_lafleur
  crontab on RHEL7 not calling python script wrapped in shell script benthomson 1 632 May-28-2020, 05:27 PM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020