topic Re: How to loop through rows from groups created by 2 columns in Discussions

How to loop through rows from groups created by 2 columns

nthai — Thu, 08 Jun 2023 16:39:56 GMT

Hi all,

i have a pretty simple question but unable to find the exact answer from the forum. Here's my sample data table:

Basically it has 4 columns, and i need to loop through each of combination items generating from "group 1" and "group 2", but if and only when "item" # is different then open the all file path in "path" column. For example on first 2 rows, we have the combination is "aA", and because items also different so the script need to open both file path 1 and 2.

Re: How to loop through rows from groups created by 2 columns

Ressel — Mon, 30 Jan 2023 20:41:09 GMT

Hi, the below at least starts what you want to do. You will only have to change the "Print( dtTemp:path[j], dtTemp:path[j + 1] )" part with the function to open a file.

Unfortunately, however, the way I wrote it, this script prints "file path 6" twice to the log, so it will also open "file path6" twice if modified accordingly. I am sure there is a way around it (like checking whether a file path has already been opened maybe), but it is getting late here. Anyway, if I figure out how to do the rest as well, I'll let you know. Feedback is appreciated!

dt = New Table( "today's challenge",
	Add Rows( 7 ),
	New Column( "group1",
		Character,
		"Nominal",
		Set Values( {"a", "a", "a", "a", "c", "c", "c"} )
	),
	New Column( "group2",
		Character,
		"Nominal",
		Set Values( {"A", "A", "B", "B", "A", "A", "A"} ),
		Set Display Width( 70 )
	),
	New Column( "item",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 3, 2, 4, 5, 7, 9] )
	),
	New Column( "path",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"file path 1", "file path 2", "file path 3", "file path 4",
			"file path 5", "file path 6", "file path 7"}
		)
	)
);

dt << New Column( "PairedGroup", formula( :group1 || :group2 ) );

PairedGroupList = Associative Array( dt:PairedGroup ) << Get Keys;

dt << Clear Column Selection;

For( i = 1, i <= N Items( PairedGroupList ), i++,
	dt << Select Where( :PairedGroup == PairedGroupList[i] );
	dtTemp = dt << Subset( Invisible, Selected Rows );
	For( j = 1, j <= N Rows( dtTemp ), j++,
		If( dtTemp:item[j] != dtTemp:item[j + 1],
			Print( dtTemp:path[j], dtTemp:path[j + 1] )
		)
	);
	Close( dtTemp, no save );
);

Re: How to loop through rows from groups created by 2 columns

StarfruitBob — Mon, 30 Jan 2023 21:52:27 GMT

Please investigate NChooseK Matrix() and see if it's right for you. In the scripting index it's described as: Creates a matrix of nChooseK(n,k) rows and K columns forming all the combinations of k integers from 1 to n.

I wonder if it can be changed to work with character data types?

Names Default To Here( 1 );
Print( NChooseK Matrix( 5, 3 ) );

__________________________
Prints:
[	1 2 3, 
	1 2 4, 
	1 2 5, 
	1 3 4, 
	1 3 5, 
	1 4 5, 
	2 3 4, 
	2 3 5, 
	2 4 5, 
	3 4 5]

Re: How to loop through rows from groups created by 2 columns

jthi — Tue, 31 Jan 2023 06:12:26 GMT

I'm not exactly sure I understand the problem correctly but below are three different options using, Col Sum/Col Cumulative Sum and Col Max(ColSum())

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(8),
	Compress File When Saved(1),
	New Column("g1", Character, "Nominal", Set Values({"a", "a", "a", "a", "a", "c", "c", "c"})),
	New Column("g2", Character, "Nominal", Set Values({"A", "A", "B", "B", "B", "A", "A", "A"})),
	New Column("i", Numeric, "Continuous", Format("Best", 12), Set Values([1, 3, 2, 4, 4, 5, 7, 9])),
	New Column("p",
		Character,
		"Nominal",
		Set Values({"filepath1", "filepath2", "filepath3", "filepath4", "filepath4.1", "filepath5", "filepath6", "filepath7"})
	)
);

// depending how we handle duplicated g1+g2+i we would use different formulas
// if we won't open any
new_col = dt << New Column("ValidFile", Numeric, Continuous, << Set Each Value(
	Col Sum(1, :g1, :g2, :i); 
));

/*
// if we open the first one
new_col = dt << New Column("ValidFile", Numeric, Continuous, << Set Each Value(
	Col Cumulative Sum(1, :g1, :g2, :i); 
));
*/

/*
// if we don't open g1+g2 at all
new_col = dt << New Column("ValidFile", Numeric, Continuous, << Set Each Value(
	Col Max(Col Sum(1, :g1, :g2, :i), :g1, :g2)
));
*/

rows_to_open = dt << Get Rows Where(:ValidFile == 1);
filepaths_to_open = dt[rows_to_open, "p"];
show(filepaths_to_open);
// dt << Delete Column(new_col);

// To open files loop over filepaths_to_open
stop();
file_list = {};
For Each({file_path}, filepaths_to_open,
	Insert Into(file_list, Open(file_path));
);

Idea is that we pick the rows where ValidFile is 1 and then open those files. This image demonstrates the calculations for the three different formulas.

Edit:

Fixed old reference rows_to_open1 to rows_to_open

Re: How to loop through rows from groups created by 2 columns

jthi — Mon, 30 Jan 2023 22:25:16 GMT

@Ressel

I think it would be easiest to re-think the looping structure, but you could make the current one also work they way you want without double printing: keep a list of what has been printed or figure out some sort of additional logic to prevent that from happening . I think the idea below should work:


For( i = 1, i <= N Items( PairedGroupList ), i++,
	dt << Select Where( :PairedGroup == PairedGroupList[i] );
	dtTemp = dt << Subset( Invisible, Selected Rows );
	For( j = 1, j < N Rows( dtTemp ), j++,
		If( dtTemp:item[j] != dtTemp:item[j + 1],
			Print(dtTemp:path[j]);
			If(j + 1 == N Rows( dtTemp ),
				Print(dtTemp:path[j + 1]);
			)
		)
	);
	Close( dtTemp, no save );
);

For example here you wouldn't loop over the last value, but check if the next value would be last, if it would be then print that also (if you did printthe current rows one).

Re: How to loop through rows from groups created by 2 columns

nthai — Tue, 31 Jan 2023 04:44:14 GMT

Hi Ressel,

I've tried your solution, indeed it printed "file path6" twice as you mentioned. Another problem i saw is when i purposely changed row 5 item value from 5 to 7 in order to have 2 identical rows, it's printed the last row ("file path 6"). Is there any way to avoid that? I'm seeing alternative for loop from jthi also have same issue.

Re: How to loop through rows from groups created by 2 columns

nthai — Tue, 31 Jan 2023 04:55:20 GMT

Hi jthi,

Thank you for such a great answer with 3 different options. One of side note is i have to remove "1" from rows_to_open1 variable in order to make the code running. If you can correct this typo for future reference, that would be great!

Other than that, i have a question related to your answer. What is the purpose of value "1" in each of Col Sum, Col Cumulative Sum and Col Max(Col Sum? And i don't get why we can sum character value from columns g1 and g2?

Re: How to loop through rows from groups created by 2 columns

jthi — Tue, 31 Jan 2023 06:16:28 GMT

Edited my message to rename old variable. We aren't adding up character columns, we are using them from grouping (<byVar>). And we use 1 so we have something that will increase by 1 for each row in the group. It might be a good idea to create different types of formulas by using those functions to make it easier to understand how they work, scripting index also provides some examples.

Col Sum(name,<By var, ...>)

Re: How to loop through rows from groups created by 2 columns

nthai — Tue, 31 Jan 2023 06:47:33 GMT

Thank you jthi! One last question, how am i supposed to further modify formula to ignore if those columns g1, g2 and i only have 1 combination of valid file?

Re: How to loop through rows from groups created by 2 columns

nthai — Tue, 31 Jan 2023 07:44:47 GMT

I manage to find one by create another column, "Unique":

new_col = dt << New Column("Unique", Numeric, Continuous, << Set Each Value(
	Col Sum(1, :g1, :g2); 
));



rows_to_open = dt << Get Rows Where(:ValidFile == 1 & :Unique != 1);

Re: How to loop through rows from groups created by 2 columns

Ressel — Tue, 31 Jan 2023 07:45:21 GMT

@jthi, neat, as always. Good learning, thank you.

Re: How to loop through rows from groups created by 2 columns

jthi — Tue, 31 Jan 2023 07:48:19 GMT

You will have to modify the ValidFile calculations and possibly the row selection depending on how you build the ValidFile check.

new_col = dt << New Column("ValidFile", Numeric, Continuous, << Set Each Value(
	If(Col Sum(1, :g1, :g2, :i) == 1 & Col Number(:i, :g1, :g2) > 1,
		1
	,
		0
	);
));

Re: How to loop through rows from groups created by 2 columns

Ressel — Tue, 31 Jan 2023 11:51:39 GMT

@jthi, I just want to ask a few questions with regards to the last part of the code, i.e., I don't understand it.

file_list = {};
For Each({file_path}, filepaths_to_open,
	Insert Into(file_list, Open(file_path));
);

file_list = {};

This is an empty list. The loop will fill this up via the Insert Into(file_list, Open(file_path)). Why can't one just use the list already contained in filepaths_to_open? Also, I don't understand how the Open(file_path) will insert something into the list. Isn't this just a function to open a file? How can at it all contribute to a list?

{file_path}

This, I also don't understand. Is this just a placeholder for the actual filepaths?

Maybe you have a reference explaining these things? Thank you very much already in advance.

Re: How to loop through rows from groups created by 2 columns

jthi — Tue, 31 Jan 2023 15:38:39 GMT

I think those are easiest to understand by writing a demo script ( I modified For Each a bit to add idx, it might make it a bit easier to understand how it works)

Names Default To Here(1);

filepaths_to_open = {"$SAMPLE_DATA/Big Class.jmp", "$SAMPLE_DATA/Probe.jmp", "$SAMPLE_DATA/Iris.jmp"};
show(filepaths_to_open);
file_list = {};
show(file_list);
For Each({file_path, idx}, filepaths_to_open,
	show(idx, file_path);
	Insert Into(file_list, Open(file_path));
	show(file_list);
);

I use file_list to collect the references to the data tables which are opened (I don't need the filepaths as I already have them in the filepaths_to_open, but I don't have references)
Open(file_path) will return a reference to the opened file and Insert Into will then add that reference to the file_list.
The {filepath} comes from how For Each function should be used. Basically it will loop over filepaths_to_open and will return each element (one by one) in file_path variable.

filepaths_to_open = {"$SAMPLE_DATA/Big Class.jmp", "$SAMPLE_DATA/Probe.jmp", "$SAMPLE_DATA/Iris.jmp"};
file_list = {};
idx = 1;
file_path = "$SAMPLE_DATA/Big Class.jmp";
file_list = {DataTable("Big Class")};
idx = 2;
file_path = "$SAMPLE_DATA/Probe.jmp";
file_list = {DataTable("Big Class"), DataTable("Probe")};
idx = 3;
file_path = "$SAMPLE_DATA/Iris.jmp";
file_list = {DataTable("Big Class"), DataTable("Probe"), DataTable("Iris")};

Hopefully answer some of the questions.

Re: How to loop through rows from groups created by 2 columns

Ressel — Sun, 05 Feb 2023 19:26:49 GMT

Yes, that was actually very useful. The file_list variable was confusing, since it is not strictly needed to open to the files (but; I take it; that it is useful later on when closing all files again (?), for example, or some other manipulation). I have to admit that it also took me some time to wrap my head around the "file_path" variable, which is assigned to a different file path at every iteration, and that it doesn't matter what it is called. (In my head, I just interpret it now as a vehicle that picks one file path after the other from the filepaths_to_open list and lets the user do "something" with it.)

Thank you! Every week a tiny growth of knowledge is better that none at all.

P.s.: The below I was only able to write based on my understanding of your explanations. Again, very useful.

Names Default To Here( 1 );

filepaths_to_open = {"$SAMPLE_DATA/Big Class.jmp", "$SAMPLE_DATA/Probe.jmp", "$SAMPLE_DATA/Iris.jmp"};

For Each( {file_path}, filepaths_to_open, Open( file_path ) );