How to specify which columns and rows to transpose for a given table?

[#1]

Hi,

I have the following script where I am opening two data tables which I need to process before using it for analysis. One of the data sets requires transposing from column 3 (:Inoculum ...) to the end of the table column 16 (Positive control leak-test) as shown below. However, I would like to make run automatic as the names are irrelevant but the table structure is always the same. Could anyone provide some feedback please on how to make it run? Thanks

dt1 = Open(
"C:\Users\ioliveira\Desktop\data\DEF-BMP Curves.csv",
columns(
New Column( "Sample", Character, "Nominal" ),
New Column( "Hour", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Average", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Minimum", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Maximum", Numeric, "Continuous", Format( "Best", 12 ) )
),
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
dt2 = Open(
"C:\Users\ioliveira\Desktop\data\DM for BMP 31.07.18.xlsx",
Worksheets( "Protocol" ),
Use for all sheets( 0 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 3 ),
Data Starts on Row( 4 ),
Data Starts on Column( 1 ),
Data Ends on Row( 6 ),
Data Ends on Column( 16 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
dt2 << Transpose(
columns(
:Inoculum,
:Positive Control,
:Name( "FB Powder 2/5 1.05.18" ),
:Economizer WEL 6.7 bar 15.05.18,
:Chicken muck black REL 08.05.18,
:Chicken muck white REL 08.05.18,
:Name( "Rye Ag-Bag EEL" ),
:M301,
:M302,
:M303,
:Name( "WEL ECON 24/7" ),
:Name( "WEL STRAW 24/7" ),
:Name( "WEL STRAW 20/6" ),
:Name( "Positive Control Leak-Test" )
),
Label( :Sample name ),
Output Table( "dt3" )
);

[#2 · Accepted solution]

I believe this is the solution you need

colList = dt2 << get column names;
colList = remove( colList, 1,3);

dt3 = dt2 << Transpose(
	columns(
		eval(colList)
	),
	Label( :Sample name ),
	Output Table( "dt3" )
);

[#3]

Is it the column names that are irrelevant or the table names?

You can get a list of the names of columns in your table with Get Column Names() function.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
n = dt << Get Column Names();
Show( n );

I think you will find lists useful for what you are trying to script.

We can use Remove() on the list, n, to get rid of 2 columns starting from column 1. To give us the list, m, of columns that we wish to use.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
n = dt << Get Column Names();
m = Remove( n, 1, 2 );
Show( m );

Now we can use this list of columns. As a simple example we will plot distributions of the columns.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
n = dt << Get Column Names();
m = Remove( n, 1, 2 );
dt << Distribution( Y( Eval( m ) ));

Notice that we need to use the Eval() function to evaluate the argument so that Distribution() recognises m as a list of column names.

You should be able to use the same approach to define the columns that you need to transpose, if that is what you needed to do.

I hope that helps.

Phil

[#4]

Thanks for the feeback.

I have tried using the different examples but with the exception of the third scrit that does the distribution of the data table, there is no difference when running the script one and the second one.Is that correct? I thought the script would be removing columns 1 and 2 but this does not seem to be displayed.

[#5]

Hi,
The second script is removing column names from the list, n, to give a shorter list, m. It is not removing the columns from the table. If you view the log when running the scripts you will see the lists n and m. You can also turn on the embedded log in any script window - that can be useful when scripting.
Hope that helps.
Phil

[#6]

Thanks. It definitely helps. I understand now what you mean. Did not know of the existence of the Log window.