how to replace two row values by one?

ENTHU

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.

Sensor	Week	DoW	Value
A	1		0.25
A	2		0.33
A	3		0.76
A	4	Sun(D)	0.21
A		Sun(N)	0.75
A		Mon(D)	0.33
A		Mon(N)	0.34
B	1		0.6
B	2		0.82
B	3		0.55
B	4	Sun(D)	0.4
B		Sun(N)	0.22
B		Mon(D)	0.91
B		Mon(N)	0.98
C	1		0.25
C	2		0.35
C	3		0.76
C	4	Sun(D)	0.21
C		Sun(N)	0.75
C		Mon(D)	0.33
C		Mon(N)	0.34

 

The expected output is -

Sensor	Week1	Week2	Week3	Week4.Sun	Week4.Mon
A	0.25	0.33	0.76	0.48	0.34
B	0.6	0.82	0.55	0.31	0.95
C	0.25	0.35	0.76	0.48	0.34

txnelson

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 );