Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level I

## Get column values using Loop

Hi, I am a beginner, currently learning jsl scripting. I would like to know how shall i write a script (loop) to check N columns of values (names) and only output those that with a valid name, and ignore those "NA"? The end result (Column "All members") in each row shall be a combined list of valid names (ignoring column value with "NA"). See example below:-

 Col 1 Col 2 Col 3 Col 4 ……………. Col n All members Jane NA Don Tom Amy Jane, Don, Tom, Amy NA Henry Terry NA NA Henry, Terry NA NA NA Jack Bob Jack, Bob
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Get column values using Loop

Here are 3 different methods to solve the problem

1. This one loops through the rows and columns, using the column names to come up with the answer
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 1",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the names of the columns in the data table
colNames = dt << get column names( string );

// Create the new column
dt << New Column( "All Members", character );

// Loop through each row
For( k = 1, k <= N Rows( dt ), k++,
// Loop through all columns finding non NA values
For( i = 1, i <= N Items( colNames ), i++,
If( Column( colNames[i] )[k] != "NA",
If( :All Members[k] == "",
:All Members[k] = Column( colNames[i] )[k],
:All Members[k] = :All Members[k] || ", " || Column( colNames[i] )[k]
)
)
)
);``````
2. This one loops through the rows and columns using the column numbers to solve the problem
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 2",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the number of current columns
numCol = N Cols( dt );

// Create the new column
dt << New Column( "All Members", character );

// Loop through each row
For( k = 1, k <= N Rows( dt ), k++,
// Loop through all columns finding non NA values
For( i = 1, i <= numCol, i++,
If( Column( i )[k] != "NA",
If( :All Members[k] == "",
:All Members[k] = Column( i )[k],
:All Members[k] = :All Members[k] || ", " || Column( i )[k]
)
)
)
);``````
3. This one changes the NA values in the data table, to blanks, which is the normal way to indicate missing values for character columns, and then uses a built in function called "Combine Columns()" to create the desired column
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 3",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the names of the columns in the data table
colNames = dt << get column names( string );

// Loop through the columns and delete the NA values
For( i = 1, i <= N Cols( dt ), i++,
dt << select where( as column( colNames[i] ) == "NA" );
// set the found cells to a blank
try( column( colNames[i] )[ dt << get selected rows ] = "" )
);

// Create the new column using a built in function
dt << Combine Columns(
delimiter( "," ),
Columns( eval(colNames) ),
Selected Columns are Indicator Columns(0 ),
Column Name( "All Members" )
);

// Move the column to the last position
dt << Move Selected Columns( :All Members, To last );``````

Jim
2 REPLIES 2
Highlighted
Super User

## Re: Get column values using Loop

Here are 3 different methods to solve the problem

1. This one loops through the rows and columns, using the column names to come up with the answer
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 1",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the names of the columns in the data table
colNames = dt << get column names( string );

// Create the new column
dt << New Column( "All Members", character );

// Loop through each row
For( k = 1, k <= N Rows( dt ), k++,
// Loop through all columns finding non NA values
For( i = 1, i <= N Items( colNames ), i++,
If( Column( colNames[i] )[k] != "NA",
If( :All Members[k] == "",
:All Members[k] = Column( colNames[i] )[k],
:All Members[k] = :All Members[k] || ", " || Column( colNames[i] )[k]
)
)
)
);``````
2. This one loops through the rows and columns using the column numbers to solve the problem
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 2",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the number of current columns
numCol = N Cols( dt );

// Create the new column
dt << New Column( "All Members", character );

// Loop through each row
For( k = 1, k <= N Rows( dt ), k++,
// Loop through all columns finding non NA values
For( i = 1, i <= numCol, i++,
If( Column( i )[k] != "NA",
If( :All Members[k] == "",
:All Members[k] = Column( i )[k],
:All Members[k] = :All Members[k] || ", " || Column( i )[k]
)
)
)
);``````
3. This one changes the NA values in the data table, to blanks, which is the normal way to indicate missing values for character columns, and then uses a built in function called "Combine Columns()" to create the desired column
``````Names Default To Here( 1 );

// Create an Example data table
dt = New Table( "Example 3",
New Column( "Col 1", Character, "Nominal", Set Values( {"Jane", "NA", "NA"} ) ),
New Column( "Col 2", Character, "Nominal", Set Values( {"NA", "Henry", "NA"} ) ),
New Column( "Col 3",
Character,
"Nominal",
Set Values( {"Don", "Terry", "NA"} )
),
New Column( "Col 4",
Character,
"Nominal",
Set Values( {"Tom", "NA", "Jack"} ),
Set Display Width( 48 )
),
New Column( "Col 5", Character, "Nominal", Set Values( {"Amy", "NA", "Bob"} ) )
);

// Get the names of the columns in the data table
colNames = dt << get column names( string );

// Loop through the columns and delete the NA values
For( i = 1, i <= N Cols( dt ), i++,
dt << select where( as column( colNames[i] ) == "NA" );
// set the found cells to a blank
try( column( colNames[i] )[ dt << get selected rows ] = "" )
);

// Create the new column using a built in function
dt << Combine Columns(
delimiter( "," ),
Columns( eval(colNames) ),
Selected Columns are Indicator Columns(0 ),
Column Name( "All Members" )
);

// Move the column to the last position
dt << Move Selected Columns( :All Members, To last );``````

Jim
Highlighted
Level I

## Re: Get column values using Loop

Hi txnelson, I really appreciate for your willingness to share multiple solutions! The scripts work like a charm and really thanks !
Article Labels