We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
ENTHU
Level III

how to replace two row values by one?

I am working with a data set where in I need to find average of two rows and replace the two rows by average.

the following is the data set in which I want to find average value for each day for a given week and sensor.Once i figure this out I can use transpose/stack functions to obtain desired output.Any help is appreciated.

SensorWeekDoWValue
A1 0.25
A2 0.33
A3 0.76
A4Sun(D)0.21
A Sun(N)0.75
A Mon(D)0.33
A Mon(N)0.34
B1 0.6
B2 0.82
B3 0.55
B4Sun(D)0.4
B Sun(N)0.22
B Mon(D)0.91
B Mon(N)0.98
C1 0.25
C2 0.35
C3 0.76
C4Sun(D)0.21
C Sun(N)0.75
C Mon(D)0.33
C Mon(N)0.34

 

The expected output is -

SensorWeek1Week2Week3Week4.SunWeek4.Mon
A0.250.330.760.480.34
B0.60.820.550.310.95
C0.250.350.760.480.34
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: how to replace two row values by one?

See if this bit of JSL will give you what you want, or at least a starting point.  I had to make a couple of data assumptions, but it may work for you

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 21 ),
	New Script(
		"Source",
		Data Table( "Untitled 29" ) << Transpose(
			columns( :Column 1 ),
			By( :Column 2 ),
			Output Table( "Transpose of Untitled 29" )
		)
	),
	New Column( "Sensor",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B",
			"C", "C", "C", "C", "C", "C", "C"}
		)
	),
	New Column( "Week",
		Numeric,
		"Ordinal",
		Format( "Best", 12 ),
		Set Values(
			[1, 2, 3, 4, ., ., ., 1, 2, 3, 4, ., ., ., 1, 2, 3, 4, ., ., .]
		)
	),
	New Column( "DoW",
		Character,
		"Nominal",
		Set Values(
			{"", "", "", "Sun(D)", "Sun(N)", "Mon(D)", "Mon(N)", "", "", "",
			"Sun(D)", "Sun(N)", "Mon(D)", "Mon(N)", "", "", "", "Sun(D)", "Sun(N)",
			"Mon(D)", "Mon(N)"}
		)
	),
	New Column( "Value",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.25, 0.33, 0.76, 0.21, 0.75, 0.33, 0.34, 0.6, 0.82, 0.55, 0.4, 0.22,
			0.91, 0.98, 0.25, 0.35, 0.76, 0.21, 0.75, 0.33, 0.34]
		)
	)
);

wait(5); // wait so you can see the original data table

	dt << New Column( "The Date",
		Character,
		"Nominal",
		Formula(
			dowInclude = "";
			If( Is Missing( :Week ) == 0,
				theInclude = Char( :Week )
			);
			If( :DoW != "",
				dowInclude = Word( 1, :DoW, "(" )
			);
			Trim( "Week" || theInclude || " " || dowInclude );
		),
		Set Selected,
		Set Display Width( 83 )
	);

wait(5); // wait so you can see the new formula generated column	

// Create the means
dtSum = dt << Summary(
	invisible,
	Group( :Sensor, :The Date ),
	Mean( :Value ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);

// Split the table into the Final Display Format
dtFinal = dtSum << Split(
	Split By( :The Date ),
	Split( :Value ),
	Group( :Sensor ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Clean up
close( dtSum, nosave );
Jim

View solution in original post

3 REPLIES 3
Highlighted
mzwald
Staff

Re: how to replace two row values by one?

You would use the summary table platform to do this with two Subgroup variables.

First you would need to strip out the extra characters from the DoW values and then perform the Summary so for example:

 

 

New Column( "Day", Character, "Nominal", Formula( Substr( :DoW, 1, 3 )));

Summary(
Group( :Sensor ),
Mean( :Value ),
Subgroup( :Week, :Day ),
Freq( "None" ),
Weight( "None" )
);

 

Highlighted
ENTHU
Level III

Re: how to replace two row values by one?

Thanks for your response.I have already tried this approach.But the problem is the column names are in the format Value,week,day and renaming them is tricky because the column names change with change in week and day.

 

Is there any other option I can try/read up about?

Highlighted
txnelson
Super User

Re: how to replace two row values by one?

See if this bit of JSL will give you what you want, or at least a starting point.  I had to make a couple of data assumptions, but it may work for you

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 21 ),
	New Script(
		"Source",
		Data Table( "Untitled 29" ) << Transpose(
			columns( :Column 1 ),
			By( :Column 2 ),
			Output Table( "Transpose of Untitled 29" )
		)
	),
	New Column( "Sensor",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B",
			"C", "C", "C", "C", "C", "C", "C"}
		)
	),
	New Column( "Week",
		Numeric,
		"Ordinal",
		Format( "Best", 12 ),
		Set Values(
			[1, 2, 3, 4, ., ., ., 1, 2, 3, 4, ., ., ., 1, 2, 3, 4, ., ., .]
		)
	),
	New Column( "DoW",
		Character,
		"Nominal",
		Set Values(
			{"", "", "", "Sun(D)", "Sun(N)", "Mon(D)", "Mon(N)", "", "", "",
			"Sun(D)", "Sun(N)", "Mon(D)", "Mon(N)", "", "", "", "Sun(D)", "Sun(N)",
			"Mon(D)", "Mon(N)"}
		)
	),
	New Column( "Value",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.25, 0.33, 0.76, 0.21, 0.75, 0.33, 0.34, 0.6, 0.82, 0.55, 0.4, 0.22,
			0.91, 0.98, 0.25, 0.35, 0.76, 0.21, 0.75, 0.33, 0.34]
		)
	)
);

wait(5); // wait so you can see the original data table

	dt << New Column( "The Date",
		Character,
		"Nominal",
		Formula(
			dowInclude = "";
			If( Is Missing( :Week ) == 0,
				theInclude = Char( :Week )
			);
			If( :DoW != "",
				dowInclude = Word( 1, :DoW, "(" )
			);
			Trim( "Week" || theInclude || " " || dowInclude );
		),
		Set Selected,
		Set Display Width( 83 )
	);

wait(5); // wait so you can see the new formula generated column	

// Create the means
dtSum = dt << Summary(
	invisible,
	Group( :Sensor, :The Date ),
	Mean( :Value ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to original data table( 0 )
);

// Split the table into the Final Display Format
dtFinal = dtSum << Split(
	Split By( :The Date ),
	Split( :Value ),
	Group( :Sensor ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

// Clean up
close( dtSum, nosave );
Jim

View solution in original post