Choose Language Hide Translation Bar
Highlighted
Chaytorial
Level I

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Table Transform Query

My error....but a simple fix.....just add the column Week as the grouping column in the Split Dialog.  Below is the full script with the addition of the Group column to the Split code.

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 ),
	Group( :Week ),
	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);
);
Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: 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);
);
Jim
Highlighted
Chaytorial
Level I

Re: Table Transform Query

Hi Jim,

 

Thanks so much for getting back to me so quickly.

 

I just tried your solution, but I think I must not be following the instructions correctly because the final table I get it is:

 

David     Tim     Rachel     Alexis     Tina

    5           2           1            4            6

    2           7           2            *             *

    *           *            5            *             *

 

The 'week' column has disappeared and the rows show how many times each person has scored points, rather than when they scored points. For example, 'Tina' scored points in week 3, but her score is in the first row.

 

 

Highlighted
txnelson
Super User

Re: Table Transform Query

My error....but a simple fix.....just add the column Week as the grouping column in the Split Dialog.  Below is the full script with the addition of the Group column to the Split code.

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 ),
	Group( :Week ),
	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);
);
Jim

View solution in original post

Highlighted
Chaytorial
Level I

Re: Table Transform Query

That worked perfectly, thanks so much