cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
Do Not Copy and Paste in Excel (Use JMP) (2022-EU-PO-1054)
Poster Winner

 

Åke Öhrlund, Galderma, Galderma

 

Data from a tensile tester contained 92 runs, each with four columns and 3,800 rows. The sample name was between the header and data, in one of the four columns. At first, the data was imported and stacked, which omitted the sample name. Next, the sample name was imported and stacked. Finally, the sample name was joined to the data table, allowing visualization and analysis. Instead of preparing the data in Excel, it was imported to JMP and formatted, saving hours of work and preventing possible errors.

 

 

Hi, my name is Åke Öhrlund .

I work at Galderma in Uppsala in Sweden.

I've been using JMP for many, many years,

almost my whole working life so far, but apparently I'm still learning.

That's what I want to share today.

One of the things I've come to realize more late is I've spent too much time

working on data, preparing it for getting into JMP in Excel; c utting and pasting.

I want to show you an example how you can do that much faster in JMP.

I got this data set from a colleague of mine.

It was a Tensile testing data.

It was 92 runs, each one 3900 rows of data.

It was layout like this with four columns for each run,

so 444, 92 times.

On row number three there was a sample name crammed in

and then the third 900 rows of data.

I couldn't pick it directly into JMP,

so she said, "You want me to cut and paste

it to be in same columns like you used to in JMP?"

I said, "N o. I'm going to try to do it directly JMP."

This is what I did.

I started by importing the data leaving out row number three.

I just have to tell JMP that is two header rows and it should skip

row number three and start on row number four.

Do that and I have all the data with the two rows of header on top.

But now this is a white table so I want to stack this.

S elect all the columns, put them into stack.

Now I have to tell JMP there's four of these columns that go together,

and JMP actually seems to get four by four

the way they should be.

I just click okay,

and then I have a table containing all the data,

a lot of data rows stacked on top of each other.

I only have four columns actually containing the data and four columns

called label that described what is in those data columns.

I could be working from here,

but rather also have the sample name there.

Do the import again; st art all over.

This time I want row number three

still including both header rows and include row number three,

so data states starts here.

Click next,

and then I have to tell you that you could skip everything after row three.

There you go.

You have one row of data containing the sample names.

Still this is wide so I want it in the long version

like I have with the data table,

so do stack again.

Select all the columns in to stack.

This time I don't have to tell JMP anything, just stack what you have there.

There you have it.

Another data table with all the sample names here.

I named this one Sample.

This one I can match with the original data table.

That's what I'll do.

I go to the data table, it's named Untitled Three.

Then I go to update,

and in update I say, "Update with data from Untitled Six."

What should I take from there?

Selected the columns, sample,

replace nothing in the old data table

and now I match the label here with label here.

Click okay and I end up with a data table

with all the data and all the sample names.

Again, I could work from here but

since this was quite fast I want to tidy up a bit.

I want these labels to go up here,

this label to go up there and so on.

J ust copy these

and go down here and paste them into the data two, data three, data four.

They end up here.

After doing that, I don't need the label columns anymore.

I can just remove those.

Now I have all the data in their columns headings

with the right column headings and the sample name here.

Now, of course, this is where the actual fun begins.

It's very easy now to pick out certain samples and do what not.

That's the fun part of JMP, of course.

What I conveyor here is try not wasting too much time

in Excel cutting and pasting because chances are

you might as well do it in JMP in much, much less time.

I've been using JMP for many, many years and still trying to realize

all the stuff you can do so I'm going to try this a lot more.

That's all from me today.

Thank you very much for listening.

Comments
trish_roth

Nice examples of how to use the different features in Excel import wizard, especially use of multiple series stack. Would be good to have an example data file to practice use of this function. Are you aware of one in the JMP sample data?