nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Consolidating Rows and Deleting Blank Data in JMP13

cfsaylor

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 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

Accepted answers

All comments

cwillden

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.

cfsaylor

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?

txnelson

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

cfsaylor

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.

cwillden

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.

cfsaylor

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.

cwillden

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: Collapsed Table.PNG

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 )
	)
)