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
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.

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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",
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
3 REPLIES 3
Highlighted
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
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.

Highlighted
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",
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