Ordering columns based on the frequency not names...

Isabel26

I know how to do it in SAS but I cannot figure this out in JMP!!! As attached dummy data, I have unique ID for each row and different variables that either 1 or 0. I want to summarize for each ID that which variable flagged as 1, AND the list has to based on the order of frequencies of each Variable (who has the most 1's down to the least 1's). It is easy to manually reorder columns then concatenate all to my results column, but I have thousands rows and hundreds columns to reorder. I have to find a way rather than manual... dying here. please help!  

Q OF THE DAY.jmp

jthi

This post should most likely be in Discussions not in Community Discussions area.

 

Most likely there are better ways to do this, but here is scripting heavy solution which seemed to work with example data:

Names Default To Here(1);

//Example datatable

dt = New Table("Q OF THE DAY",
	Add Rows(5),
	New Column("ID", Character(1), "Nominal", Set Values({"A", "B", "C", "D", "E"}), Set Display Width(38)),
	New Column("V1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 1, 0, 0]), Set Display Width(38)),
	New Column("V2", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 0, 0]), Set Display Width(38)),
	New Column("V3", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 1, 0, 0]), Set Display Width(38)),
	New Column("V4", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 0, 1]), Set Display Width(42)),
	New Column("V6", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 1, 0, 1]))
);

/*
colCount = 10;
dt = new table("example", 
	add rows(1000),
	New Column("ID", Numeric, Ordinal, << Set Each Value(Row()));
);

For(i = 1, i <= colCount, i++,
	dt << New Column("V" || char(i), Numeric, Ordinal,
		<< Set Each Value(Random Binomial(1, 0.9))
	);
);
*/

colNames = dt << Get Column Names("String");

//Drop ID column 
colNames = Substr(colNames, 2);

//sorting list https://community.jmp.com/t5/JSL-Cookbook/Sorting-Lists/ta-p/240795
//create ranking based on sums and column idx
sumsOfCols = V Sum(dt[0, colNames]); //order is same as in coLNames
orderCols = Reverse(Rank(sumsOfCols));

newCol = dt << New Column("ORDERED", Character, Nominal);
For Each Row(dt,
	//Indices of columns which have 1
	idxWithOne = Loc(dt[Row(), colNames]);
	//if all values are missing continue to next row
	If(N Items(idxWithOne) == 0,
		continue();
	);
	//fill list with empty values
	tempList = Repeat({""}, N Items(colNames));
	//replace empty with colnames which had 1
	tempList[idxWithOne] = colNames[idxWithOne];
	//reorder list based on sum ranking
	reOrderedList = tempList[orderCols];
	//create string of list and remove whitespaces
	orderedString = Trim Whitespace(Collapse Whitespace(Concat Items(tempList[orderCols], " ")));
	//add value to column
	newCol[Row()] = orderedString;
);

Also it has no specific handling for ties.

 

You might be also to  do this interactively:

Stack data:

Add formulas:

	New Column("Orders",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(Col Sum(:Data, :Label))
	),
	New Column("RankForId",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(Col Rank(Col Max(:Orders) - :Orders, :ID)),
		Set Display Width(70)
	),
	New Column("ColumnName",
		Character,
		"Nominal",
		Formula(If(:Data == 1, :Label))
	)

Sort data by ID and RankForId (select both and sort ascending):

Transpose data:

 

Remove Label column and Combine Row columns from Columns menu:

Remove unnecessary columns and join back to original datatable if needed.

 

Interactive solution might have different ordering between ties that the scripting version