Python script to summarize excel tables, then output a composite table? i'm a total n

Python script to summarize excel tables, then output a composite table? i'm a total n - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python script to summarize excel tables, then output a composite table? i'm a total n (/thread-32354.html)

Python script to summarize excel tables, then output a composite table? i'm a total n - surfer349 - Feb-04-2021

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?)

RE: Python script to summarize excel tables, then output a composite table? i'm a total n - nilamo - Feb-05-2021

https://docs.python.org/3/library/itertools.html#itertools.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')]