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 III

## parse string into different columns

Hi,

I need your help to parse a string of data into table.

Attached are example of Raw_data table and the RSLT table I want to achieve.

For each EQP I may have a string with couple of (Position:Value) separated by ";" like

P:V;P:V;P:V;

Geof

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: parse string into different columns

This will do it.  I'm processing lists within lists using the WORDS function.

dt = New Table( "Raw_data",

Add Rows( 6 ),     New Column( "EQP", Character( 1 ), Nominal,

Set Values( {"A", "B", "A", "C", "A", "B"} )

),

New Column( "Position : Value", Character, Nominal,

Set Values(

{"362:53;394:134;", "227:37;247:152;122:44;", "96:88;", "266:48;",

"362:64;394:136;", "266:47;"}

)

)

);

// Create an empty results table

rslt_tbl = New Table( "Results", New Column( "EQP", Character( 1 ), Nominal ),

New Column( "Position", Numeric, Continuous, Format( "Best", 12 ) ),

New Column( "Value", Numeric, Continuous, Format( "Best", 12 ) )

);

m = 0;

for (i = 1, i <= nrows(dt), i++,

one_eqp = dt:eqp[i];

pos_val_list = words(column(dt, "Position : Value")[i], ";");

for (k = 1, k <= nitems(pos_val_list), k++,

one_pos_val_list = words(pos_val_list[k], ":");

one_pos = num(one_pos_val_list[1]);

one_val = num(one_pos_val_list[2]);

m++;

rslt_tbl:eqp[m]      = one_eqp;

rslt_tbl:Position[m] = one_pos;

rslt_tbl:Value[m]    = one_val;

);

);

2 REPLIES 2
Highlighted
Super User

## Re: parse string into different columns

This will do it.  I'm processing lists within lists using the WORDS function.

dt = New Table( "Raw_data",

Add Rows( 6 ),     New Column( "EQP", Character( 1 ), Nominal,

Set Values( {"A", "B", "A", "C", "A", "B"} )

),

New Column( "Position : Value", Character, Nominal,

Set Values(

{"362:53;394:134;", "227:37;247:152;122:44;", "96:88;", "266:48;",

"362:64;394:136;", "266:47;"}

)

)

);

// Create an empty results table

rslt_tbl = New Table( "Results", New Column( "EQP", Character( 1 ), Nominal ),

New Column( "Position", Numeric, Continuous, Format( "Best", 12 ) ),

New Column( "Value", Numeric, Continuous, Format( "Best", 12 ) )

);

m = 0;

for (i = 1, i <= nrows(dt), i++,

one_eqp = dt:eqp[i];

pos_val_list = words(column(dt, "Position : Value")[i], ";");

for (k = 1, k <= nitems(pos_val_list), k++,

one_pos_val_list = words(pos_val_list[k], ":");

one_pos = num(one_pos_val_list[1]);

one_val = num(one_pos_val_list[2]);

m++;

rslt_tbl:eqp[m]      = one_eqp;

rslt_tbl:Position[m] = one_pos;

rslt_tbl:Value[m]    = one_val;

);

);

Highlighted
Level III

## Re: parse string into different columns

It works fine !

Thanks

geof

Article Labels

There are no labels assigned to this post.