Processing fermentation with horizontal data table

[#1]

Hi all,

I am attempting to integrate jmp in order to process ethanol fermentation data into graphs and other metrics. I am having some issues converting the existing format of the data into a table that is useable by JMP. 

We receive the data as a google sheet from the plant. I am able to upload this data table to JMP. The issue comes with the format of the data. The data is listed by batch number for each row, and each column is a string of text with numbers such as "4hr Ethanol" or "8hr Lactic." See below for an example. 

Batch	0hr Ethanol	0 hr Lactic	0 hr Acetic	4hr Ethanol	4hr Lactic	4hr Acetic	….	60 hr Ethanol	60 hr Lactic	60 hr Acetic
1
2
3
4

I need to put each category on a per time basis. For example, I want to graph the ethanol concentration from 0 to 60 hours for Batches 2 and 3. I believe I need to stack the data table, text to columns, and use recode to change the column header from the string of text to the time in hours as a numerical entry. I am having some issues getting the table exactly how I want it to create these graphs. Any help would be appreciated. Thanks in advance.

[#2 · Accepted solution]

Here is an example that produces your Ethanol chart, with fictitious data

Here is a script that produces the plot

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
	Add Rows( 4 ),
	New Column( "Batch", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4] ) ),
	New Column( "0hr Ethanol",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [103.347990080313, 90.8056789034286, 110.847323879326, 94.4892596023655] )
	),
	New Column( "0 hr Lactic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Selected,
		Set Values( [98.2941757953967, 87.0280094785425, 96.8477348247328, 92.910237668013] )
	),
	New Column( "0 hr Acetic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [86.0736604847805, 84.7654940657705, 103.653020778599, 103.896496570196] )
	),
	New Column( "4hr Ethanol",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [106.780752584519, 83.6557999642181, 131.739275066723, 108.595538300447] )
	),
	New Column( "4hr Lactic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [117.501711811893, 110.280136421701, 99.5008817613032, 124.122069840277] )
	),
	New Column( "4hr Acetic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [65.8570956816124, 98.7590715244122, 116.085302666605, 77.465274192855] )
	),
	New Column( "60 hr Ethanol",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [93.2946183359449, 102.663246654816, 88.3140432209597, 77.0616091856786] )
	),
	New Column( "60 hr Lactic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [115.208356681673, 94.3976953097385, 117.870933434213, 88.9448640120466] )
	),
	New Column( "60 hr Acetic",
		Numeric,
		"Nominal",
		Format( "", 16 ),
		Set Values( [101.707143312459, 115.87798730461, 111.256786967136, 114.046044409696] )
	),
	Set Row States( [1, 0, 0, 0] )
);

// Stack the columns
dtStack = dt << Stack(
	columns(
		:"0hr Ethanol"n, :"0 hr Lactic"n, :"0 hr Acetic"n, :"4hr Ethanol"n, :"4hr Lactic"n, :"4hr Acetic"n, :"60 hr Ethanol"n, :"60 hr Lactic"n,
		:"60 hr Acetic"n
	),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);


// Create new time and Type of Gas
dtStack << New Column( "Time", Format( "h:m" ), set each value( Num( Word( 1, :label, "h" ) ) * 3600 ) );
For each row(:label = word(-1,:Label," "));

// Split out into separate columns
dtSplit dtStack << Split(
	Split By( :Label ),
	Split( :Data ),
	Group( :Batch, :Time ),
	Sort by Column Property
);

// Graph the data
Graph Builder(
	Size( 528, 454 ),
	Show Control Panel( 0 ),
	Variables( X( :Time ), Y( :Ethanol ), Overlay( :Batch ) ),
	Elements( Line( X, Y, Legend( 15 ) ), Points( X, Y, Legend( 16 ) ) ),
	Local Data Filter(
		Add Filter(
			columns( :Batch ),
			Where( :Batch == {1, 2} ),
			Display( :Batch, N Items( 4 ) )
		)
	)
);

[#3]

I actually used to work in this industry and have fond memories of all the different data formats we ran into... Most commonly it was the horizontal data format that you provided. Jim is exactly right that you need to do some combination of stacking and splitting the table. I would make one very small suggestion to the above script. If you use the format "hr:m" in the below line it will result in the duration units instead of time of day. 

Have fun!

// Create new time and Type of Gas
dtStack << New Column( "Time", Format( "hr:m" ), set each value( Num( Word( 1, :label, "hr" ) ) * 3600 ) );