Single Cell Table Breakout

dpeyer

I am trying to analyze the data for one of the subcomponents of the device my company manufactures. Unfortunately, the actual useful data is saved in a table that is condensed into a single cell in the database (see below). Is there a way to break out this data into separate columns and rows while keeping all the other data (e.g. assign the same serial number to all the new rows of data)? The end goal is to have something like the excel table below (that I had to make by hand :P) with all the serial #, station ID, etc. data for each row.

 

Before:

StationID	StartDateTime	Serial	FunctionalTestTable
1223	1/1/2020	ASDF1234	Pressure,Flow Setting,Flow Variance,Flow Value,Flow Result,UUT dP,Step Pos 11.000,0.00,0.050,0.001,PASS,11.047,-156 10.000,3.00,0.250,3.097,PASS,9.984,-119 10.000,5.00,0.250,4.933,PASS,9.990,-103 10.000,10.00,0.500,9.847,PASS,9.973,-64 10.000,100.00,5.000,101.048,PASS,9.968,525 7.000,3.00,0.250,2.957,PASS,6.983,-114 7.000,5.00,0.250,4.885,PASS,6.988,-95 7.000,10.00,0.500,9.822,PASS,6.990,-48 7.000,80.00,4.000,80.916,PASS,6.981,518 4.000,3.00,0.250,3.078,PASS,3.981,-101 4.000,5.00,0.250,5.110,PASS,3.988,-76 4.000,10.00,0.500,9.980,PASS,3.987,-15 4.000,50.00,2.500,52.123,PASS,3.993,447

 

After:

StationID	StartDateTime	Serial	Pressure	Flow Setting	Flow Variance	Flow Value	Flow Result	UUT dP	Step Pos
1223	1/1/2020	ASDF1234	11.000	0.00	0.050	0.001	PASS	11.047	-156
1223	1/1/2020	ASDF1234	10.000	3.00	0.250	3.097	PASS	9.984	-119
1223	1/1/2020	ASDF1234	10.000	5.00	0.500	4.933	PASS	9.990	-103
1223	1/1/2020	ASDF1234	10.000	10.00	5.000	9.847	PASS	9.973	-64
1223	1/1/2020	ASDF1234	10.000	100.00	0.250	101.048	PASS	9.968	525
1223	1/1/2020	ASDF1234	7.000	3.00	0.250	2.957	PASS	6.983	-114
1223	1/1/2020	ASDF1234	7.000	5.00	0.250	4.885	PASS	6.988	-95
1223	1/1/2020	ASDF1234	7.000	10.00	0.500	9.822	PASS	6.990	-48
1223	1/1/2020	ASDF1234	7.000	80.00	4.000	80.916	PASS	6.981	518
1223	1/1/2020	ASDF1234	4.000	3.00	0.250	3.078	PASS	3.981	-101
1223	1/1/2020	ASDF1234	4.000	5.00	0.250	5.110	PASS	3.988	-76
1223	1/1/2020	ASDF1234	4.000	10.00	0.500	9.980	PASS	3.987	-15
1223	1/1/2020	ASDF1234	4.000	50.00	2.500	52.123	PASS	3.993	447

 

Thanks!

David

 

 

 

 

 

 

Jeff_Perkinson

First off, whoever created a table like this should be...well...I can think of some appropriate punishments.

 

Fortunately, I think JMP will make this relatively easy to deal with.

 

First, use the Cols -> Utilities -> Text to columns... as @Mandy_Chambers suggested, but use NEWLINE as the delimiter.

This should give you one new column for each line within your FunctionalTextTables column.

Now, use Tables->Stack to stack all the FunctionalTestTable # (i.e., FunctionalTestTable 1, FunctionalTestTable 2, etc.) columns.

This will give you one row for each of those columns.

Now, you can use Text to Columns... again, this time with a comma (,) as the delimiter.

Now you'll get the columns of individual values.

Now it's a matter of copying and pasting the column names. Here's a tip to help you thereColumn Names Got You Down? Try This. And then, use Cols->Standardize Attributes to change the data type of columns to Numeric as appropriate.

 

You said you had a database of 14k of these. Are they 14k different tables in the database or 14k rows of one table? Hopefully the latter as you should be able to get that all into one JMP data table using File->Database->Open Table...

 

If it's really 14k different tables then you'll need to use Tables->Concatenate to get them all into one table in JMP, but let's cross that bridge after you confirm which it is in the database. 

 

Regardless, Select Matching Cells is another tool that will help you clean up. I'll explain that in a follow up post after we make just a bit more progress.