BookmarkSubscribeRSS Feed

Extracting the maximums

amy

Occasional Contributor

Joined:

Apr 27, 2018

Hi all, I worked around a script that would loop around column names and find the ones I want to use. It then finds the row number and the value of that cell. However, I find that these values are repeated for every column. Can anybody point me out to where I might be going wrong?

Here is the script:

dt = current data table();

col = dt << get column names( string );
nc = N Items( col );
Row = 1;
For( i = 1, i <= nc, i++,
 for(r=1, r<=NRow(dt), r++,
 Col1 = Column(dt,i);
 If( Contains( col[i], "Prediction" ),
 If(Column(dt, col[i])[r] > 5 | Column(dt, col[i])[r] > 4.5,
 show(col[i]);
 val = abs(Col Max( Col1 ));
 show(val);
 MaxRow = dt << get rows where(as column(Col1)==abs(Col Max( Col1 )));
 show(MaxRow);

 )
 	
 	)
 )
 ); 

The log looks like this:

col[i] = "Prediction B";
val = 6.6;
MaxRow = [6];
col[i] = "Prediction B";
val = 6.6;
MaxRow = [6];
col[i] = "Prediction B";
val = 6.6;
MaxRow = [6];
col[i] = "Prediction C";
val = 5;
MaxRow = [1];

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

@amy,
      I think the script is doing what you have scritped out. The reason you are seeing the repeated values is because in column "Prediction B" , there are 3 values i.e. rows that meet the condition that you have specified , so the same value gets printed repeatedly - since you have asked it to print the maximum value and the position. 

     Alternatively, you can try doing it this way. 

dt = Current Data Table();

// amy's approach 
//col = dt << get column names( string );
//nc = N Items( col );
//Row = 1;
//For( i = 1, i <= nc, i++,
//	For( r = 1, r <= N Row( dt ), r++,
//		Col1 = Column( dt, i );
//		If( Contains( col[i], "Prediction" ),
//			If( Column( dt, col[i] )[r] > 5 | Column( dt, col[i] )[r] > 4.5,
//				Show( col[i] );
//				val = Abs( Col Max( Col1 ) );
//				Show( val );
//				MaxRow = dt << get rows where( As Column( Col1 ) == Abs( Col Max( Col1 ) ) );
//				Show( MaxRow );
//
//			  )
// 	
//		);
//	)
//);

// alternatively 
ColNames = dt << get column names( string );
for(i = 1, i <= N Cols(dt), i++,
		If( Contains( ColNames[i], "Prediction" ),
			Col = Column(dt,i); // get reference to column 
			MaxVal = Col Max(Col); 
			ColVals = As List(Col << Get Values); 
			If(MaxVal > 4.5,
				Show(Col);
				Show(MaxVal);
				Show(Loc(ColVals,MaxVal));  
			  );
		  );
   );
Best
Uday
txnelson

Super User

Joined:

Jun 22, 2012

Solution

I believe that your example list you provided should actually have been specified as

a={ [12], [13], [14],[15,16] };

Your example had specified the syntax of a Matrix [], not a list {}.  As a matrix, your example is not valid.  So I will answer making the assumption that what you have is a list.

The list you show, contains multiple matricies.  All that has to be done to get the data out is to correctly specify the subscripting for the list and matrix.  So for example, if you wanted to pull out the number 13 from your example, you would specify

x = a[2][1]

and "x" would then contain the value of 13.  Likewise, if you wanted to retrieve the 2nd element of the 4th matrix in your list, [15,16] you would specify:

x = a[4][2]

To take all of the 1st elements in each of the matricies, and move them into a simple list, all you need to do is to loop across the elements in your list called "a" and to extract the first matrix element

a = {[12], [13], [14], [15, 16]};

aList = {};
For( i = 1, i <= N Items( a ), i++,
	Insert Into( aList, a[i][1] )
);
Show( aList );

and the result would be:

     aList = {12, 13, 14, 15};

Jim
6 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

@amy,
      I think the script is doing what you have scritped out. The reason you are seeing the repeated values is because in column "Prediction B" , there are 3 values i.e. rows that meet the condition that you have specified , so the same value gets printed repeatedly - since you have asked it to print the maximum value and the position. 

     Alternatively, you can try doing it this way. 

dt = Current Data Table();

// amy's approach 
//col = dt << get column names( string );
//nc = N Items( col );
//Row = 1;
//For( i = 1, i <= nc, i++,
//	For( r = 1, r <= N Row( dt ), r++,
//		Col1 = Column( dt, i );
//		If( Contains( col[i], "Prediction" ),
//			If( Column( dt, col[i] )[r] > 5 | Column( dt, col[i] )[r] > 4.5,
//				Show( col[i] );
//				val = Abs( Col Max( Col1 ) );
//				Show( val );
//				MaxRow = dt << get rows where( As Column( Col1 ) == Abs( Col Max( Col1 ) ) );
//				Show( MaxRow );
//
//			  )
// 	
//		);
//	)
//);

// alternatively 
ColNames = dt << get column names( string );
for(i = 1, i <= N Cols(dt), i++,
		If( Contains( ColNames[i], "Prediction" ),
			Col = Column(dt,i); // get reference to column 
			MaxVal = Col Max(Col); 
			ColVals = As List(Col << Get Values); 
			If(MaxVal > 4.5,
				Show(Col);
				Show(MaxVal);
				Show(Loc(ColVals,MaxVal));  
			  );
		  );
   );
Best
Uday
amy

Occasional Contributor

Joined:

Apr 27, 2018

Oops, I got it.. Thank you so much @uday_guntupalli

amy

Occasional Contributor

Joined:

Apr 27, 2018

How can I save all these values in a table? @uday_guntupalli

dt1 = New Table( "Information",
	New Column("ColName", character, nominal, values(ColNames[i])),
	New Column("MaxVal", numeric, continuous, values(MaxVal)),
	New Column( "Column", character, nominal, values (Col))
);
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Hello @amy
   What data would you like to save to the data table ? 

   In the example script that you have posted, ColNames[i] refers to a specific value of ColNames which is a list of all the column names. Can you provide sample of what is contained in these columns. 

   What you are attempting and the path you are on is correct. You just want to make sure the data you are passing to the columns is correct. Here is a simple example: 

 

Names = {"Rick","Tom","Sam"}; 

Age = [10,20,30]; 

Weight = [110,130,150]; 

dt = New Table("Test", Add Rows(N Items(Names)),
				New Column("NamesOfStudents",Character,Nominal,<<Set Values(Names)),
				New Column("AgesOfStudents",Numeric,Continuous,<<Set Values(Age)),
				New Column("WeightsOfStudents",Numeric,Continuous,<<Set Values(Weight))
			  );
Best
Uday
amy

Occasional Contributor

Joined:

Apr 27, 2018

I don't know why, but I have a list that looks like this:

a=[ [12], [13], [14],[15,16] ];

How can I just extract numbers and add them into a table in numeric format? Is this like, a list within a list? @uday_guntupalli  Also, if I have more than one values like [15,16] in the above list, I would only want 15 to be added to a data table. Thank you.

txnelson

Super User

Joined:

Jun 22, 2012

Solution

I believe that your example list you provided should actually have been specified as

a={ [12], [13], [14],[15,16] };

Your example had specified the syntax of a Matrix [], not a list {}.  As a matrix, your example is not valid.  So I will answer making the assumption that what you have is a list.

The list you show, contains multiple matricies.  All that has to be done to get the data out is to correctly specify the subscripting for the list and matrix.  So for example, if you wanted to pull out the number 13 from your example, you would specify

x = a[2][1]

and "x" would then contain the value of 13.  Likewise, if you wanted to retrieve the 2nd element of the 4th matrix in your list, [15,16] you would specify:

x = a[4][2]

To take all of the 1st elements in each of the matricies, and move them into a simple list, all you need to do is to loop across the elements in your list called "a" and to extract the first matrix element

a = {[12], [13], [14], [15, 16]};

aList = {};
For( i = 1, i <= N Items( a ), i++,
	Insert Into( aList, a[i][1] )
);
Show( aList );

and the result would be:

     aList = {12, 13, 14, 15};

Jim