Saving You 15 Minutes: Navigating Your Data Table and Recording Your Actions (2021-US-30MP-869)
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. |