nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Combine N columns ( for M parameters ) into a unique column per parameter based on regex using JSL

mpanggabean

Hello,

I have N columns with numeric data, some of the columns are blank for a given row ( so the parameters of interest are scattered column wise )

The N columns represent M parameters and I would like to have each of the M parameter combined into a single column.

Ideally I'd like to get guidance on how to script this in JSL and have some form or regex incorporated so I can do this for a large number of columns based on keywords

Attaching a very basic sample data table.

Sample_Data_table_N_Cols_M_parameters_scattered.jmp

Accepted answers

ian_jmp

With these things, it very much depends on what you can safely assume about the data (and what you can't!). If I understand correctly, the code below works for the sample table given (and 'similar' tables). It could be generalised, or, with a bit more work, made to build formula columns (by exploiting @Jeff_Perkinson's use of 'Sum()' above).

NamesDefaultToHere(1);

dt = CurrentDataTable();

// First data column
fdc = 2;

// Number of columns in a group
nc = 2;

// Number of groups
ng = (NCol(dt) - 1) / nc;

// Loop over each group and add a 'sum' column for each
for(g=1, g <=ng, g++,
	firstCol = fdc + (g-1)*nc;
	lastCol = firstCol + (nc-1);
	gData = dt << getAsMatrix(firstCol::lastCol);
	// Replace ',' by '0' so 'VSum()' works as required
	gData[loc(IsMissing(gData))] = 0;
	// Get the sum of columns using 'VSum()' (need to transpose, and back again)
	sumVals = Transpose(VSum(Transpose(gData)));
	// Make the new column
	cName = Column(dt, firstCol) << getName;
	dt << NewColumn(cName||"_Sum", Numeric, Continuous, Values(sumVals));
);

All comments

uday_guntupalli

@mpanggabean,

The following script will help you separate out the list of columns into 2 lists . The 2 lists will tell you

1. What are the Column Names that contain Parameters
2. What are the unique Parameters available in the table .

From here or with this base script you can manipulate the data how ever you choose.

Clear Log () ; Clear Globals(); 

dt = Current Data Table() ; // Getting a reference to the table . Alternatively you can open the file using Open() 

ColNames = dt << Get Column Names("String"); // Acquire names of columns in table as string 

ParameterColList = list(); // Intialize an empty list
ParamsList = list() ;  
KeyWord = "param";      // Define Keyword all your parameters have 
CommonDelim = "_"; 

for(i = 1 , i <= N Items(ColNames), i++, 
		If(!IsMissing(Regex(Char(ColNames[i]),KeyWord)), 
			Insert Into(ParameterColList,ColNames[i]); 
			If(Contains(ColNames[i],CommonDelim),
				Pos = Contains(ColNames[i],CommonDelim);    // Identify where the common delimiter is 
				Insert Into(ParamsList,Substr(ColNames[i],1,Pos-1));
			  ); 
		  );
   );
Show(ParameterColList); // Display List
Show(ParamsList);

Jeff_Perkinson

If you can be sure that there will be only one column with a parameter value you can use the Sum() function. This is because Sum() ignores missing values. So the sum of the parameter values will be the value of the nonmissing parameter.

mpanggabean

Hi Jeff,

Yes there should be only one column with a parameter value, however, there are too many parameters & sub parameters. For example param1 can have: param1_domain1_vdd1, param1_also_domain1_vdd1, param1_domain1_vdd2, param1_also_domain1_vdd2, param1_domain1_vdd3, param1_also_domain1_vdd3, & so on. Would only want to consolidate a parameter w/in same domain and same vdd and may have a larger overall number of parameters. Therefore, I'd like to avoid selecting & summing too many times. Any further suggestions?

Thanks though for the simple starting point!

ian_jmp

NamesDefaultToHere(1);

dt = CurrentDataTable();

// First data column
fdc = 2;

// Number of columns in a group
nc = 2;

// Number of groups
ng = (NCol(dt) - 1) / nc;

// Loop over each group and add a 'sum' column for each
for(g=1, g <=ng, g++,
	firstCol = fdc + (g-1)*nc;
	lastCol = firstCol + (nc-1);
	gData = dt << getAsMatrix(firstCol::lastCol);
	// Replace ',' by '0' so 'VSum()' works as required
	gData[loc(IsMissing(gData))] = 0;
	// Get the sum of columns using 'VSum()' (need to transpose, and back again)
	sumVals = Transpose(VSum(Transpose(gData)));
	// Make the new column
	cName = Column(dt, firstCol) << getName;
	dt << NewColumn(cName||"_Sum", Numeric, Continuous, Values(sumVals));
);

Kevin_Anderson

Hi, mpanggabean!

Please tell us if this is correct: You wish to stack columns that contain, for instance, "param1" and "domain1" and "vdd", and also presumably "paramx" and "domainy" and "zzz" as separate columns.

How many columns and rows do you anticipate starting with and ending with?