304444_en-US

liliconcom

Table Transform Query

I am having difficulty reformatting a table data in JMP. I'm hoping to transform a table from a 'standings' format to 'weekly score format', e.g.

 

From:

 

Week     1st_Name     1st_Score     2nd_Name     2nd_Score     3rd_Name     3rd_Score

1               David               5                  Tim                  2                  Rachel            1

2               Tim                  7                Alexis                 4                 Rachel             2

3               Tina                 6                Rachel               5                 David                2

 

To:

 

Week     David     Tim     Rachel     Alexis     Tina

1                5           2           1            0            0

2                0           7           2            4            0

3                2           0           5            0            6

 

I can order the columns differently in the original data pull if that makes it easier to reformat.

If anyone is able to help with this I would be very grateful.

Data TableScriptingRe: Table Transform Query

This is easy to do.  

Interactively you:

1. Stack the names and scores specifying in the Stack dialog to use Multiple Series Stack
2. Then Split the stacked table
   1. Split By column Data
   2. Specify column Data2 as the Split Column
3. Then you will need to go through each column and select and change the missing values to 0

Here is a script that illustrates the solution based upon your example data

Names Default To Here( 1 );
dt = New Table( "Example",
 Add Rows( 3 ),
 New Column( "Week", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),
 New Column( "1st_Name", Character, "Nominal", Set Values( {"David", "Tim", "Tina"} ) ),
 New Column( "1st_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 7, 6] ) ),
 New Column( "2nd_Name", Character, "Nominal", Set Values( {"Tim", "Alexis", "Rachel"} ) ),
 New Column( "2nd_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 4, 5] ) ),
 New Column( "3rd_Name", Character, "Nominal", Set Values( {"Rachel", "Rachel", "David"} ) ),
 New Column( "3rd_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3] ) )
);

dtStack = dt << Stack(
 columns(
  :"1st_Name"n,
  :"1st_Score"n,
  :"2nd_Name"n,
  :"2nd_Score"n,
  :"3rd_Name"n,
  :"3rd_Score"n
 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Number of Series( 2 ),
 Output Table( "Stacked Data" )
);

dtFinal = dtStack << Split(
 Split By( :Data ),
 Split( :Data 2 ),
 Remaining Columns( Drop All ),
 Sort by Column Property
);

For( i=1, i<=N Cols(dtFinal), i++,
 missing = dtFinal << get rows where( isMissing(as column( i )));
 try(column(i)[missing]=0);
);