Delete cell values based on a column condition and merge rows with same timestamp using JSL

himajamuppalla

Hi,

 

I have following "TestData":

1. I want the cell value to be null when the status is down. for example ("A status " cell value is "down" the value in Column "A" should be null instead of "1334". should do the same for B , C and D Columns.

2.Delete all the columns that has "status" in the column name.

 

Output should be something like this:

3. Now all the same timestamp rows  should be merged. expected output would be:(i know i can use summary [group by and sum] to achieve this . But is there a better way to do it in JSL?)

 

 

I have to do this for multiple csv files. Can someone help me out writing JSL for this.

 

Thank you so much in advnace.

TestData.jmp

cwillden

This should do it for you, but the script assumes that the status columns correspond to the column just preceding them.  You could do this by looking at the name of the status column and finding a column with the same name minus " Status", but I figured that is unnecessarily complicated if the assumption is true.

 

dt = Current Data Table();
col_names = dt << Get Column Names(string);

//Assuming the corresponding value column for each status column is always the preceding one
//Loop backwards through the columns
for(i = N Col(dt), i>= 1, i--,
	//If column name has word "status" in it
	if(Contains(Lowercase(col_names[i]), "status") > 0, 
		//Look for instances of "{down}"
		down_rows = dt << Get Rows Where( dt:(As Name(col_names[i])) == "{down}" );
		//Assuming preceding column is the value column, replace {down} rows with missing
		Column(dt, i-1)[down_rows] = .;
		dt << Delete Columns(i);
	);
);


//Create table summary to consolidate time stamp rows with same value
//Build a string that we can parse for the summary operation
remaining_cols = dt << Get Column Names("string");
summary_str = "dt_clean = dt << Summary( Group( :TimeStamp ), ";

//Loop through all columns except TimeStamp to add "Sum( <current col> )" to summary string
for(i = 2, i <= N Col(dt), i++,
	summary_str = summary_str||"Sum( :Name(\!""||remaining_cols[i]||"\!")), ";
);

//Attach last part of script
summary_str = summary_str||"Freq( \!"None\!"), 
Weight(\!"None\!"), 
statistics column name format( \!"column\!" ),
Link to original data table( 0 ),
output table name( \!"CleanedData\!" ));";

//Parse and evaluate the string to create the summary table dt_new
eval(parse(summary_str));

//Delete N Rows column
dt_clean << Delete Columns(:N Rows);