Onboarding Data from Excel - Data wrangling support requested

analyticsmurali

Hi,

 

i am having some challenges in converting excel data sheet where the data is spread like this. i have attached the Spread sheet as well.

 

any thoughts on this? how can i quickly convert similar date from excel sheet to JMP dataset?

 

thanks

 

 

 

txnelson

Using the new JMP version 16, I simply walked through the interactive steps to modify the data, and then cut and pasted the JSL from the log into my script.  I then applied some of my own preferences to the code, but that would not have been necessary.

Note: I also deleted the graphics and the final result columns from the Excel data sheet, Data wrangler, before I opened it in JMP

names default to here(1);
dt = Open(
	"<Path to>/Data wrangling .xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
// Delete selected rows
dt << Select Where(
	:Column 1 == "" & Is Missing( :Column 2 ) & Is Missing( :Column 3 ) & :Column 4
	 == "" & Is Missing( :Column 5 ) & Is Missing( :Column 6 ) &
	Is Missing( :Column 7 )
) << Delete Rows;

// New column: Column 8
dt << New Column( "Group",
	character,
	formula(If( Is Missing( :Column 1 ) & Is Missing( :Column 2 ) & Is Missing( :Column 3 ),
		:Column 4
	))
);

// Delete column formula: Group
dt:Group << Delete Formula;
For(i=1,i<=nrows(dt),i++,
	if(:group[i]=="",:group[i]=:group[i-1])
);

// Delete selected rows
dt << Select Where(
	:Column 1 == "" & Is Missing( :Column 2 ) & Is Missing( :Column 3 ) &
	Is Missing( :Column 5 ) & Is Missing( :Column 6 ) & Is Missing( :Column 7 ) 
) << Delete Rows;

// Change column info: Column 4
dt:Column 4 << Data Type( Numeric ) <<
Set Modeling Type( "Continuous" ) << Set Field Width( 12 );

// Transpose data table
// → Data Table( "Transpose of Sheet1" )
dtTrans = dt << Transpose(
	columns( :Column 2, :Column 3, :Column 4, :Column 5, :Column 6, :Column 7 ),
	By( :Column 1, :Group ),
	Output Table( "Transpose of Sheet1" )
);

// Change column name: Column 1 → T(Type)
dtTrans:Column 1 << Set Name( "T(Type)" );

// Delete column: Label
dtTrans << Delete Columns( :Label );

// Change column name: Row 1 → Response
dtTrans:Row 1 << Set Name( "Response" );

// New column: Column 4
dtTrans << New Column( "SI. No.",
	Numeric,
	"Continuous",
	Formula( Row() ),
	Format( "Best", 12 )
);

// Delete column formula: SI. No.
Data Table( "Transpose of Sheet1" ):SI. No. << Delete Formula;

// Move selected columns
Data Table( "Transpose of Sheet1" ) <<
Move Selected Columns( {:"T(Type)"n, :Group, :Response}, after( :SI. No. ) );

Data wrangling .xlsx