330113_en-US

liliconcom

Single Cell Table Breakout

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

 

Thanks!

David

 

 

 

AnalyticsData TableRe: Single Cell Table Breakout

you can take the value like this, 11.000,0.00,0.050,0.001,PASS,11.047,-156, that column and use Columns > Utilities > Text to Cols to create separate columns for the different things in that column. 

Use the Delimiters  ,-

and that will give you columns like this:

 

11,000  0.00  0.050  0.001  PASS .. etc..

if you want to break it down more or differently you can use a different delimiter or space. 

 

Hopefully this is what you were trying to do.

 

Re: Single Cell Table BreakoutI think our post crossed. So after you use Text to columns, now go use Tables Transpose and you can turn the table around and get the data into columns ..

Re: Single Cell Table Breakout

Mandy,

 

Thanks for the advice! I learned about the Text to Columns utility right before I posted this message, but that created 84 new columns per row and lost the functionality of the tabulated data that I am trying to use. I build an example of what I would like the output to be and added it to my original post. 

 

Thanks!

Re: Single Cell Table Breakout

Re: Single Cell Table BreakoutHmmm...I must not be describing what I am trying to do properly. I have updated my original question with a before/after example to try an illustrate better. Additionally, I have a database of 14,000 FunctionalTestTables that I need to do this for, so doing Text to Columns and then transposing every individual table is not a viable option.

Thank you for your help! I really do appreciate it:DRe: Single Cell Table Breakout

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

 

Re: Single Cell Table Breakout

Hey Jeff,

I have had words with the Test Engineer responsible, don't you worry :P. It is unfortunately 14k separate tables each with their own cell and headings repeated in each table. I have attached a image of 3 (of the 14k...). I will try your method an let you know how it goes!

 

 

Re: Single Cell Table Breakout

Hi @dpeyer ,

 

Here is an add-in that you can call to import the data.

Alternatively, you can run the script to import the text file into JMP.

 

I based the import on the example text file attached.

 

Both the script and the add-in can be updated to concatenate like files into a single file.

 

cheers,

Stan

Re: Single Cell Table Breakout

Hey Jeff that worked perfectly! I do have a follow-up question. Is there a way to save all those steps as a script so I don't have to redo them every time I need to pull this data? I tried saving it as a session script, but that didn't save any of the data breakout.

Re: Single Cell Table Breakout

I'm glad to hear that it's gotten the data into shape for you to analyze.

 

Yes, you can create a script to do this for you. It'll take a little bit of work but JMP creates some of it for you.

 

Here's an outline of how to do it.

 

JMP 15 won't capture the Text to Columns... command but it's pretty easy syntax to write:

 

 

// Text to columns
Data Table( "Community330288.jmp" ) <<
Text to Columns( columns( :FunctionalTestTable ), Delimiters( NEWLINE ) );

 

 

The next step is the Stack command. JMP puts the JSL for this in a script called Source in the resulting table.

Right click on that and choose Edit and you'll get the script to copy and paste:

 

stacked_data = Data Table( "Community330288.jmp" ) << Stack(
 columns(
  :FunctionalTestTable 1,
  :FunctionalTestTable 2,
  :FunctionalTestTable 3,
  :FunctionalTestTable 4,
  :FunctionalTestTable 5,
  :FunctionalTestTable 6,
  :FunctionalTestTable 7,
  :FunctionalTestTable 8,
  :FunctionalTestTable 9,
  :FunctionalTestTable 10,
  :FunctionalTestTable 11,
  :FunctionalTestTable 12,
  :FunctionalTestTable 13,
  :FunctionalTestTable 14
 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Name( "Non-stacked columns" )(Keep( :StationID, :StartDateTime, :Serial ))
)

The next step is to repeat the Text to Columns with comma as the delimiter:

 

 

// Text to columns
stacked_data << Text to Columns( columns( :Data ), Delimiters( "," ) );

Another thing JMP won't record is the renaming of columns from the first row of the data. Renaming can be scripted but you'll have to write it yourself.

 

If you know the number of columns will always be the same (in this case 7) you can hard code that. If the number of columns is variable you'll have to do a little more work. Let me know if that's the case and that's another bridge we can help you cross.

 

for(i=1, i<=7, i++,
 column("Data " || char(i))<<SET name=""></SET>

Finally, you'll need to select the rows that contain column names and delete them. I've hard coded the column name and the value below. If they are variable...well, it'll just take a little more work.

stacked_data << Select Where( :Pressure == "Pressure" ) << Delete Rows;

Finally, change some columns to numeric. You'll need to repeat this for each column.

:Pressure << set data type(Numeric);

I hope that helps.

Keep the questions coming.