Consolidating Rows and Deleting Blank Data in JMP13

[#1]

Hello all, 

My question is twofold. My data appear something like this: 

1-A	x	y	.	.	.	.
1-A	.	.	z	q	.	.
1-A	.	.	.	.	w	w
2-B	y	w	.	.	.	.
2-B	.	.	t	y	.	.
2-B	.	.	.	.	q	p

I would like to A) delete the empty data points and B) consildate the rows according to the identifier in the first column that each row has in common (i.e. 1-A and 2-B). 

I would like my end result to look like this: 

1-A	x	y	z	q	w	w
2-B	y	w	t	y	q	p

Does anyone have any suggestions about approaching this issue for a large table with hundreds of values?

Thanks in advance,

Charlie 

[#2]

The most painless way to do this would be to:

1. Stack all those columns except for the ID column so that they are all consolidated into a single data column (resulting Label column maintains traceability).

2. Delete all rows with missing values in the new data column. Easy way: select a missing value > right-click on it > select matching cells > right-click > delete rows.

3. Do a table split to return to original shape of the data table. Split column is the new data column. Split By is the new label column, and you can group by the ID column.

[#3]

Thank you for the prompt reply. I should add that I've glossed over much of the detail in my actual table -- for example, when I attempt Step #1, JMP tells me "Cannot stack. Stacked columns are of different data type". 

Is there a way to stack columns of a various data type? 

[#4]

If you don't have too many columns, you could make them all character first, use the steps I recommended, and then convert the numeric columns back.  If your table is much wider than your example, this may not be very feasible.

[#5]

Unfortunately this table has >1000 columns. Thank you for the suggestion, but that does not seem feasible. 

Now, suppose I wanted to ignore the blank data and attend to them at a later date. How would I consolidate the rows? Hopefully that step is more feasible. 

[#6]

If the data are numeric, excluding the first column, then you can just use

     Tables==>Summary

and specify to generate a mean or a sum for the columns in question

[#7]

Unfortunately the data are mixed: categorical, binary, numeric, free text. I am looking to simplify the database and remove unnecessary blank entries. Thank you for the suggestion, but I don't believe that it will work for this scenario. 

[#8]

Time for the big guns.  Here's a script that should work.  Basically the way it works is by iterating through all the rows and columns.  If the next row has the same ID, it passes down the non-missing values to the next row.  By the last row for a given ID, all non-missing values should be in that row.  In the last step, you delete all the extra rows.

dt = Current Data Table();
dt << New Column( "Delete Later", character, formula( If( :ID[Row()] == :ID[Row() + 1], "Yes", "No" ) ) );
for(i=1,i<=N Row(dt),i++, //iterate through each row
	//If next row has same ID, pass non-missing values down to next row
	If(:Delete Later[i] == "Yes",
		for(j=2,j<= (N Col(dt) - 1),j++, //iterate through each column (except ID and Delete Later)
			//pass non-missing data down to next row
			if(!isMissing(Column(dt,j)[i]), Column(dt,j)[i+1] = Column(dt,j)[i])
		)
	)
);

//Collapse all the extra rows
dt << Select Where(:Delete Later == "Yes") << Delete Rows();

This worked perfectly for your example.  It should work for your table, though if you have millions of rows, it could take a good long while to finish.  This script also assumes the column is named "ID" and all other columns should have non-missing values for only 1 row for each ID.

Starting table I used to test the script:

Final Table:

Script to recreate my starting table:

New Table( "ConsolidateTable",
	Add Rows( 6 ),
	New Column( "ID",
		Character( 3 ),
		"Nominal",
		Set Values( {"1-A", "1-A", "1-A", "2-B", "2-B", "2-B"} ),
		Set Display Width( 87 )
	),
	New Column( "Column 2",
		Character( 16 ),
		"Nominal",
		Set Values( {"x", "", "", "y", "", ""} ),
		Set Display Width( 87 )
	),
	New Column( "Column 3",
		Character( 16 ),
		"Nominal",
		Set Values( {"y", "", "", "w", "", ""} ),
		Set Display Width( 87 )
	),
	New Column( "Column 4",
		Character( 16 ),
		"Nominal",
		Set Values( {"", "z", "", "", "t", ""} ),
		Set Display Width( 87 )
	),
	New Column( "Column 5",
		Character( 16 ),
		"Nominal",
		Set Values( {"", "q", "", "", "y", ""} ),
		Set Display Width( 87 )
	),
	New Column( "Column 6",
		Character( 16 ),
		"Nominal",
		Set Values( {"", "", "w", "", "", "q"} ),
		Set Display Width( 87 )
	),
	New Column( "Column 7",
		Character( 16 ),
		"Nominal",
		Set Values( {"", "", "w", "", "", "p"} ),
		Set Display Width( 87 )
	)
)