Sql pareto for column values per row help request

Ignition
81

good
we are both confused some, I feel better.

I want to gain mastery.

for p in pareto:

I need help to understand this.

Is this the same as:

for row in pareto:

or is it

for col in pareto:

and what is this called?
Or are col and row keywords for pydatasets, and if you say for {anything} in {anything}
it just grabs the list items?

if it is grabbing the list items, I think I need to know how to grab a list item inside a list of lists

Because I think I used the technique I learned in Java, where I iterate through the array by i and j

82

There are no rows, there are no columns. There is only what you call them.

Let's dive:

things = [1, 2, 3]
for thing in things:
    print(thing)

This goes through each thing in things.
But what if each thing also contains things ?

things = [[1, 2, 3], ['a', 'b', 'c'], ['first', 'second', 'third']]
for thing in things:
    print(thing)

this will print

[1, 2, 3]
['a', 'b', 'c']
['first', 'second', 'third']

but you could also loop through each of those sub-things:

for thing in things:
    for subthing in thing:
        print(subthing)

this will print

1
2
3
'a'
'b'
'c'
'first'
'second'
'third'

But wait ! What if each sub-thing in each thing in things also things ? we're entering the 3rd dimension !

things = [
    [['1a'], ['1b'], ['1c']],
    [['2a'], ['2b'], ['2c']],
    [['3a'], ['3b'], ['3c']],
]
for thing in things:
    print(thing)
    for subthing in thing:
        print("    {}".format(subthing))
        for three_d_thing in subthing:
            print("        ".format(three_d_thing))

I'll let you try it yourself

1 Like
83

Thank you very much

84

I’ll give you my very best python trick: use dir(), help() and type().

Open an interactive console (just type python, python3, etc in your terminal), import things, then start calling dir() on everything you want to know more about. This will tell you everything’s attributes, methods, etc.
If it’s hard to read, type(dir(x)) will tell you that it’s a list, so you can do for item in dir(thnig): print(item)
Then you can dir() items from this list. Spoiler, dir(dir(thing)[i]) will not give you what you thought it would. Why ? type(dir(thing)[i]) will tell you it’s a string. So you’re getting the attributes of string, not of the item. dir(thing.subthing) is what you’ll want.
Python’s inspection and introspection abilities are CRAZY. There are even packages that are dedicated to this. inspect for example. Or ast will even give you the tools to rebuild the abstract syntax tree used to actually compile python code (yes, it has its uses. I built doc generators with this).

So in the case where things behave weirdly, or you’re not getting the result you were expecting… start getting things’ type.
Oh, you thought you could append() to this variable but it raises an error ? type(thing) tells you it’s a float. You can’t append to float.
Now why the hell can’t you do for key, value in whatever.items() ? type(whatever) tells you it’s a list, not a dict. Doesn’t have an items() method.
Why doesn’t "hello world".to_upper() work ? dir("") will tell you it has an upper method, try that instead. You might even find some methods you didn’t know existed, and if you’re curious about what they do… Well, help() is there for you. What’s that rindex you saw when inspecting strings methods ? help(str.rindex)
Note that you can call those either with the type: dir(str), help(int)
or directly on an instance: dir("hello"). help(42)

Have fun exploring !

ps: You can do this on ignition objects in the script console. Mighty useful to figure out what you can do with them.

3 Likes
85

+1 for everything @pascal.fragnoud said here. dir() and type() are very helpful for debugging.

I highly recommend using script console - it makes debugging a new script as you’re making it much easier.

The last point about doing it with ignition objects is very useful. If you have a window open in designer, you can do

window = system.gui.getWindow("Some window")
rc = window.getRootContainer()
someComponent = rc.getComponent('some component')

and now from here you can do dir or type on the components themselves which will reveal what attributes and methods are callable on it.

1 Like
86

My requirements changed again.

I have a table like:

Machine |  Hot_Rejects | Cold_Rejects | Deformed_Rejects
B1      |  5           |     3        |   0
B2      |  0           |     2        |   1
B3      |  2           |     4        |   1

I need it to look like:

Line   | Reason_Top_3            | Qty_top_3_Reasons
B1     | Hot_Rejects             |  5         
B1     | Cold_Rejects            |  3           
B1     | Deformed_Rejects        |  0    
B2     | Cold_Rejects            |  2          
B2     | Deformed_Rejects        |  1  
B2     | Hot_Rejects             |  0      
B3     | Cold_Rejects            |  4    
B3     | Hot_Rejects             |  2                
B3     | Deformed_Rejects        |  1

If I have three reject reasons though, I can't figure out how to pivot multiple columns to the rows.
I looked on :

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

Pivot and Unpivot in SQL - GeeksforGeeks
I only see how to pivot just one column which has to be an aggregate, and I continually get a an error revolving around a comma or index however I try to put in more columns.

I really want to be able to do this in SQL, but if I have to do it in a script, then I will do that.
I am working on the script now, but if someone could help me with the SQL, I would appreciate it.

87

It is partially working.

Select Line, Reason
       from (
       Line,
       sum(isnull( Hot_Rejects,0)) as Hot_Rejects,
       sum(isnull( Cold_Rejects,0)) as Cold_Rejects,
       sum(isnull( Deformed_Rejects,0)) as Deformed_Rejects
       from myTable
       group by Line ) as sourceTable
Unpivot ( reason for reasons in (Hot_Rejects, Cold_Rejects, Deformed_Rejects) as unpivotedStuff

However, the reasons end up being the numbers, not the column headers.
I am not sure yet how to get the headers to the rows still.

Use SQL Server's UNPIVOT operator to help normalize output
This was the source that helped.

11:42AM EST 2022 09 014
Still trying to get the reason column name to a column.
This failed:

Unpivot ( Reason,Qty for (Reasons,Qtys) in (('Hot_Rejects',Hot_Rejects),
       ('Cold_Rejects', Cold_Rejects),
       ('Deformed_Rejects', Deformed_Rejects))
) as unpivotedStuff

How does SQL let you unpivot two things at once?

1155am

I need a way to rank/enumerate the rows by the Line.
Then I need a way to do inserts inside my own table somehow.

1 Like
88

In your case, the column Machine is your PIVOT point. That column is similar between the input and output data.

It is important to note that, the data types must be the same for the values that are being combined together, I don’t think that is an issue for your data sample.

This query, should get you what you’re looking for.

SELECT Line, Reason,Qty
FROM
(
	SELECT	machine as Line,
			Hot_Rejects,
			Cold_Rejects,
			Deformed_Rejects
	FROM yourtable
) pivData
UNPIVOT
(
Qty
for Reason in (Hot_Rejects,Cold_Rejects,Deformed_Rejects)
) unpiv;

Results:
image

3 Likes
89

Thanks, I didn’t realize that I could use that variable.

Now I just need to rank the the reasons/Qtys, trim to top 3 for each, and sort.

I have about 20 reasons and about 20 lines.

Thanks

Got the ranking.

Select Line, Reason, Qty from(

Select  Line
, Reason
, Qty , rank() over(partition by Line order by Qty desc)  qtyRank
from 
	(
		SELECT	machine as Line,
				Hot_Rejects,
				Cold_Rejects,
				Deformed_Rejects
		FROM yourtable
	) pivData
	UNPIVOT
	(
	Qty
	for Reason in (Hot_Rejects,Cold_Rejects,Deformed_Rejects)
	) unpiv) as S

Where qtyRank<4 and Qty>1
order by Line, Qty desc

Fetch is 0.035s on a side note.

2 Likes