235535_en-US

liliconcom

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.

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

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

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?

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