Saving You 15 Minutes: Navigating Your Data Table and Recording Your Actions (2021-US-30MP-869)

[#1]

Level: Beginner

Peter Hersh, JMP Senior Systems Engineer, SAS
Mary Loveless, Manager, Pre-Sales Support, SAS

Whether you want to sleep more, be more productive at work, leave for happy hour early or get an insurance quote, who doesn’t want an additional 15 minutes? Pete and Mary walk through some of their favorite time-saving tips:

• Interactively build a repeatable data workflow with Action Recorder.
• Organize and clean up columns all at once.
• Identify changes between data tables.

The semi-live session is an interactive session with a panel fielding questions from attendees. 

The materials to reproduce the steps Mary and Pete walkthrough in their presentation are attached abover.

Auto-generated transcript...

Speaker	Transcript
Peter Hersh	Hey Mary. How are you doing?
Mary Loveless, JMP	I'm doing okay. How about you?
Peter Hersh	Yeah, making it through this hot and smoky summer. I saw you were out fishing.
Mary Loveless, JMP	I was, I was in Montana, that was hot and smoky and then I came back to Boston and it was hot, and I brought the smoke back with me.
Peter Hersh	Well, I saw a picture of that fish you caught, so I think totally worth it, right?
Mary Loveless, JMP	Yeah,
	most definitely. Hey, I got something to share with you. I created this animated gif of my well plate information.
	Okay yeah.
	And I wanted to share with you, because now I'm trying to think about that...you know I'm always thinking of a shortcut. What can I do? How can I simplify things? And there's got to be a way for me to capture this and be able to
	see it
	and capture the code and everything with it. And I don't have to write everything down and save everything to a script. And you know, write notes in a journal of different steps and all this to share with my peer. So there's gotta be...you gotta...you gotta help me out, Pete.
Peter Hersh	Yeah.
	Yeah.
Mary Loveless, JMP	This is not my favorite activity. But I didn't know that, did you know that with the local data filter, you can animate?
Peter Hersh	Yeah, isn't that cool? That...that looks really awesome there, Mary. What...what are we looking at?
Mary Loveless, JMP	So we're looking at data over time, and I'm looking at a specific instrument, and I'm looking at the wells. This is a 96 well plate and I'm looking at the potency in each well over time, so I'm looking at exponential
	growth or potency in the well. But I think animation is a great way to kind of communicate and show people what's changing over time.
	Yeah, and then eventually you could overlay some of these and see what's changing.
Peter Hersh	So tell me a little bit about your challenge? Where...what kind of data are you starting with? I see your output here, but what...what process do you have to go through?
Mary Loveless, JMP	So I have a csv file so, you know, the data comes from the instrument, and I get a csv file. I bring that in, I open it up in JMP, and then I go through some data table manipulation, some column
	manipulation. And then I create the the graph in Graph Builder and create this animated gif.
	Okay.
	It sounds easy when I say it.
	But when you sit down and try to write it
	out and document it, it becomes very tedious. So I had heard something about an enhanced log or
	an action reporter.
	So help me out because you know I don't keep up with things.
Peter Hersh	Absolutely, Mary, why don't I I'm going to steal the screen here and just kind of first start out with a recap of what we're... what we want to do. So
	you start out with this csv file, you bring it into JMP, you got to do some data manipulation, and in the end, you want to create a shareable object here, and you're not a big fan of writing a script. So we're going to do this all without having to write
	a bit of code, because as much as we love bugging Brady and Mike and those coders to help us, it'd be great if we would didn't have to do that. So let's...let's...let's look at how we can do that in JMP. So the first thing we need to do is turn on that that log, right.
	This is something yeah, the log, exactly. So this is something that you might not have used in JMP in previous versions, because it was about scripting and seeing if your script worked but
	this is what it looks like. And we can turn it on by going to view and log.
	You'll also notice that when you...
Mary Loveless, JMP	Who knew there was a log?
Peter Hersh	What's that?
Mary Loveless, JMP	Is the log brand new?
Peter Hersh	No, the log's been there, it's just got a lot better in JMP 16.
Mary Loveless, JMP	So what did it do before, just record when JMP would crash or what?
Peter Hersh	Kind of. It would...it was when you ran a script and the script broke, it would tell you where it broke, and what happened, what kind of error you got but...
Mary Loveless, JMP	Oh okay, so it was more of a log that a scripter would leverage or used to see and track and debug.
Peter Hersh	Yes, exactly and
	now it's become more of a general purpose, like if you're used to, say, recording macros in Excel or something like that, yeah, this kind of does the same thing, in my opinion, much easier because you don't have to like turn on the record button, it's just doing it for you automatically.
	And, and so that's...
	again, to turn this on you go to view and log. It also by default, you'll notice, it's in the home window just hidden, so I can just double click on that too and open it up.
	Gotcha. Okay so let's start this process here. You gave me this...some of your data here
	and like you said, the raw data comes in the csv file.
	Right, let's start by just importing that csv file. So I'm gonna just take that, drag it to a JMP window. There you go. There's our first step in the process. We have our data.
Mary Loveless, JMP	Now did I need
	to have the log open? Did you just drag it...drag it onto the log window?
Peter Hersh	I could have dragged it onto any JMP window and it's just going to record what was done.
	Gotcha. And if, for folks that have been using JMP for a while, all that's captured here is the same thing that's in that source script. So that part was already captured, but this is just kind of keeping a running log of everything we've done.
	Okay, so what's the next step now that we've imported? What do we need to do?
Mary Loveless, JMP	So it's a wide table. It's got time across, so I'd like to make it more tall and just stack time, all the time columns.
Peter Hersh	stacked data. Okay?
Mary Loveless, JMP	Yeah.
Peter Hersh	There we go. Does that look a little better?
Mary Loveless, JMP	Much better.
Peter Hersh	So what we do next?
Mary Loveless, JMP	Select the label column.
Peter Hersh	Okay.
Mary Loveless, JMP	And
	go to new columns menu, utilities,
	text to column.
	And you notice that you have to put a delimiter, so let's a space.
	hit the space bar because there's just a space.
Peter Hersh	Okay.
	Looks good.
Mary Loveless, JMP	And take label and label 1. Select those two and delete them.
Peter Hersh	Okay, so just right click here and delete.
Mary Loveless, JMP	And then I want label 2, I want to change the name and the column properties.
	Okay. Change that label to the time.
	Okay, and change your data type to numeric.
Peter Hersh	Okay.
Mary Loveless, JMP	And change the modeling type to ordinal, because the time is an order.
Peter Hersh	Got it.
	Awesome. Just hit apply and okay. There we go.
Mary Loveless, JMP	And now, this data is ready to go into Graph Builder and click add animation.
Peter Hersh	Okay. Perfect so.
Mary Loveless, JMP	Open up Graph Builder
	and put location and the map shape.
Peter Hersh	Got it.
Mary Loveless, JMP	And put data on the color.
Peter Hersh	Alrighty.
Mary Loveless, JMP	So now that's just the, you know, data, as the summarized data for all of it on top there. So now, what I want to do is look at each time point. So let's go under the red triangle and select local data filter.
Peter Hersh	Alrighty.
Mary Loveless, JMP	And plus select time.
	Okay, and so I like to just do a few tweaks here. So under the main local data filter red triangle, I like to turn off the counts, since we know it's 96 wells so.
	Down at the bottom. And I also like to, under the red triangle next to time, under display options, which sometimes I forget about, I like to have the check...the display, because I like to have the boxes checked as I go through and animate.
Peter Hersh	Okay.
Mary Loveless, JMP	Now, I think I think I'm ready.
Peter Hersh	Okay, I don't want to offend John Sall, so I'm gonna hit done.
Mary Loveless, JMP	Oh yeah, have to say done.
Peter Hersh	All right, okay, and then we'll just apply one of these filters. And so now it's not summarized, it's a single time point, okay.
Mary Loveless, JMP	And now go on to the main triangle and say animate animation.
Peter Hersh	So cool, I don't think people use this enough.
Mary Loveless, JMP	Oh no,
	no.
Peter Hersh	I love to use animation. It makes it look like I'm doing work and very busy, and you know, if I create one of these, and just let it play, you know.
Mary Loveless, JMP	It's my screensaver so...
Peter Hersh	That's right.
	Impress your boss, right?
Mary Loveless, JMP	Right.
	Alright, so let's take a cycle through an animate.
Peter Hersh	Okay.
	And I just turned on that red triangle there to record, so it's playing and recording at the same time. And then, once it's sort of cycled through, I'll turn that off and now I can pause this and then I can...I can save this as, you know, our
	graphic or whatever we want to call it.
Mary Loveless, JMP	Right right right right.
Peter Hersh	And hit save. And there she is. All right.
Mary Loveless, JMP	Perfect, perfect. So now show me this enhanced log and show me what it...what...how it's going to help me.
Peter Hersh	Okay, so you can see here in this enhanced log, right, that everything that we did to the data table was captured there. It imported, stacked, text to columns, deleted those extra columns, and then changed some column info.
Mary Loveless, JMP	Where's the Graph Builder?
Peter Hersh	Great question. So why doesn't the Graph Builder get put in there right away? Because I could still be making changes to it. It doesn't get added until I close this. Now watch.
Mary Loveless, JMP	Oh.
Peter Hersh	There we go. So now everything I need to create this is right here. So just to kind of show you how this works, I'm going to close these two so we
	know I'm not
	cheating.
	And then I'm going to clear out those last two closed things. So now we have the import to the Graph Builder.
Mary Loveless, JMP	Right, right.
Peter Hersh	All right, and so, now that the easiest way to do this is going under the red triangle.
	Yeah, save script through a script window.
Mary Loveless, JMP	Uh huh oh.
Peter Hersh	Now you can see all those steps from...
Mary Loveless, JMP	Wait a minute. So I see the stack,
	I see the text column. Oh, and it and wow, it actually puts a comment string above the action.
Peter Hersh	Yes, yes that's really handy.
Mary Loveless, JMP	Nice, nice.
Peter Hersh	It's like having a good coder in your back pocket so not just
	me
	and forgetting to comment things. This actually tells you what it's doing, and this is a great gateway to being a scripter yourself. You can easily tweak this,
	and you can see how this step was done, right. If
	you forget what stacking...how stacking or text to columns is done,
	it's all right there. So
	now that we've saved this let's just run it and make sure it does what we want.
	There we go, and now you can see, if we want to recreate that animated gif or just have this JMP report, all we have to do is hit play there.
Mary Loveless, JMP	Awesome. That is awesome and that's just new in JMP 16?
Peter Hersh	Just...yeah in one release they created all this. Isn't that awesome?
Mary Loveless, JMP	Wow, I think that's really neat. So now that I have this automated process, for, you know, for myself and through this enhanced log, how do I share that with people and how do I document it for record, if I want to do any record keeping or any management like that?
Peter Hersh	I am so glad you asked that question, Mary. Those are great questions. So
	two things. I can just save this script and send it to anyone, and they can run it as well. The one sort of
	thing that you might have to do is make sure that they're navigating to a csv file that's maybe on a shared drive or someplace they can access.
Mary Loveless, JMP	Teah, got it.
Peter Hersh	But the other thing I can do is...
	you'll notice under this red triangle, I can actually...well, let me, let me clear out these extra steps because we're...we don't want to capture those. So I'm going to clear those and then I'm going to go here and under the save script option, the final thing is make a data table.
Mary Loveless, JMP	Ah, oh.
Peter Hersh	Yeah. So you can see here each step that was done, and then the message about that step, and then it tells you the results, and it tells you that the JSL and it's all captured here.
Mary Loveless, JMP	Oh, that's just really nice, because part of the documentation or your record keeping or just for quality, you have a record of the script that the action that that was recorded
	against that particular data. Now one additional question, Pete, is you know, I noticed that you had to go in and remove some things that we didn't want in there, because we did some steps. Is there a way to turn it off and not have a recording? Just capture what we have, or is it always on?
Peter Hersh	Yes, so I can..I can tweak these options here
	individually. I can also set my preferences under file and preferences. And all the way at the bottom, there's this log, which allows me to turn off or turn on things by default. So I can, like you said, I can have it stop recording or only record certain parts,
	like
	data table operations
	and stuff.
Mary Loveless, JMP	Oh, this was...this...you have made my day.
Peter Hersh	That was easy.
Mary Loveless, JMP	That was...that was easy, and here I was, figuring out this interesting steps up put together and and now I can actually record it and then I have ways between the data table or the script itself to deploy it to my
	team and be able to leverage it in about less than five minutes.
Peter Hersh	Yeah, absolutely. So did you have any other questions about this or anything else?
Mary Loveless, JMP	Yeah, my other question that comes to mind in thinking about the data table and bringing it in and stuff, you know, we're always...we have lots of different data tables that we're bringing in and
	and we're always operating on them in this shared space. Is there a way that we can maybe do a quality check or check to make sure the data table that
	with the final outcome or the output that I want, I can compare it and make sure it's the most recent or I'm not using one that maybe is older or less than? Something that shows changes and data tables.
Peter Hersh	Yeah, absolutely. So let's let's close this and I'm going to clear this. So let's run this script again here and we'll just generate that. And we'll...I'm going to close out of this graph and close out of this and what we want to see is, is this data table the same as the data table that
	...that you
	...that you sent me? Did we make any other changes that that we maybe forgot that we made? So I'm going to grab that and look here. And you had sent me this data table here.
	And here it is over here. And we want to see, is this the same as this or was there another step, maybe, that we added
	and changed to these two that maybe we forgot to document? So let's let's take a look at this data and compare it to that. And we can do that under the tables...compare data table. Now this has been here for a couple releases.
Mary Loveless, JMP	I know, but it's never been....it just says, yes, it's different.
Peter Hersh	Yeah, right, which wasn't super valuable.
	Right so let's take a look at what it does in 16. Alright, so we're going to hit compare.
	And you can see here, it's comparing the stacked data, the one that we made with that script to this plate test, which you sent me that had been tweaked. So by default, JMP is going to see the columns with the same names and compare those.
Mary Loveless, JMP	Right.
Peter Hersh	We can see here that this last one, data, which is actually potency, we didn't change the name, so it didn't link automatically. But we can tell JMP, hey, these are the same. We want to link them.
	All right, and then what what we have...a bunch of options here, but the first thing I'm going to do is just say, let's just compare these row by row. We know that they're all in the same order, so I'm going to hit compare.
	And you can see here that it's found 900 differences. So it says each cell is a little different, but if we look at this, it's just a difference in, like, significant figures, right. So well this one looks like it has, you know, 10, this one has 11, or something like that. So
Mary Loveless, JMP	Right.
Peter Hersh	it's not...yeah not really different, but JMP has flagged those as different because they're slightly different. And what we can do up here is allow for some relative error,
	which, we'll just say, okay, well, if they're that close, we don't want to call them different, and now we can see that that data is no different with that tiny amount of relative error.
Mary Loveless, JMP	Now
	we got that difference, but what happens...I mean, we got some different scripts in the table, we might have done put added spec limits, you know, there might have been some different
	properties. So under...if you go to column info,
Peter Hersh	Yeah yeah.
	Oh.
Mary Loveless, JMP	and know the properties, is that everything that's
	under column properties there, do you catch everything?
Peter Hersh	That's a great question. So if you notice here under the red triangle, right,
	by by default, the only thing on is the data,
	but I can compare table properties and column properties.
Mary Loveless, JMP	I gotcha.
Peter Hersh	a spec limit and a map role.
	And so those were not in the data table that we created with that the action recorder, but they're here. And so we can see, okay, well the data is similar or or the same even. We have some properties that weren't captured so that that's...
	And we can also like you said, compare table properties, so we can see is there an additional source and or something like this and you can see...
Mary Loveless, JMP	I see.
Peter Hersh	That's captured.
Mary Loveless, JMP	Is there a way to save this to a data table, to a script? How can I save this information, if I have to use it for quality or validation?
Peter Hersh	to clipboard, to a data table, to script window,
	or just like this journal here, right. And the nice thing here is that journal...I'm going to just close all of these...and what that journal script, or or if I saved it to any place, does is it just opens up that...or compares those two data tables
	if they were open. I happened to close the wrong one so.
Mary Loveless, JMP	Okay, all right. Well, that's pretty neat. So this too...well, right now, there's the enhanced log action recorder, which I love it, that it's recording actions, and and the other is compared data tables.
Peter Hersh	Yes.
Mary Loveless, JMP	And that's pretty neat. I probably have to do a little extra reading on it just to understand how to navigate the report window,but
	just to be able to go through and see what properties are different, especially when somebody shares a table with me and they've added spec limits and they ordered columns and done some interesting...
	added some interesting properties to the data table, it allows me to see the differences.
	As long as the data integrity is the same, you could have those attributes or those properties be added.
Peter Hersh	Absolutely.
Mary Loveless, JMP	Well, this has been really insightful and that you have given me back at least 15 minutes in my day.
Peter Hersh	Perfect.
Mary Loveless, JMP	I can actually.
	add a little extra time to lunch.
	I was going to say quit early, but I don't want my boss to hear that.
Peter Hersh	Yeah.
	Hopefully Ian's not watching.
Mary Loveless, JMP	Thanks, Pete, it's always good to chit chat with you and see what's new and what's going on and
	and hopefully,
	at the end, people will get as excited about the enhanced log action recorder compare tables, as I have.
Peter Hersh	Yeah, absolutely. Well, thank thanks for the time, Mary, and hopefully,
	this was useful.
Mary Loveless, JMP	Oh, it was. Always. Thanks, Pete.
Peter Hersh	Bye bye.