The final episode of Ask the Data Doctor is less about solving a concrete problem and more about the concept of how to solve problems. Brady Brady, AKA The Data Doctor, says the key to answering the question, “How do I get from Point A to Point B,” is often knowing something about Point B. What do you want to do with the data? What “data shape” are you dealing with and what data types will JMP need to perform your analysis? What are some inputs, outputs, platforms and operations that could get you there?  In this session, Brady walks us through the steps he takes to diagnose and cure data problems so that we can learn to think like Data Doctors ourselves.   ... View more

  Sometimes a column contains text that would be more useful if it were stored as columns of data. In this segment of Ask the Data Doctor, JMP Systems Engineer Brady Brady expands on an earlier episode by continuing this discussion around converting text to columns. The Data Doctor shows us more ways to use JMP's Text to Columns tool to generate new columns from text. Using a variety to data (including newspaper, trucks and order data), Brady will also give you tips for using – and display for your viewing pleasure – the real power and flexibility behind JMP’s data filter, how to create and select indicator columns, combine columns and more.   ... View more

  Brady Brady, AKA the Data Doctor, is back to talk about all things Recode. A great tool for cleaning up messy data, Brady gives us a detailed look at the Recode platform in JMP 15. In addition to high-level discussions of some common Recode tasks, Brady hits on some lesser known and advanced features of the tool. ... View more

  Data Doctor Brady Brady shows you some of the cool, built-in, point-and-click functions you’ll find in the Columns Menu (Cols) of JMP. You’ll learn how to convert text to columns and how to break up text or numerical data found in a single column and transform it into multiple columns for better clarity or insight. As an example, Brady shows us how to convert a column that includes first and last names into multiple columns that separate out first, middle and last names. (Phone numbers, emails, etc., can be done in the same way.) Best of all, Brady shows you how to repeat the process formulaically, using the Words, Word and Eval Insert functions in JMP.  ... View more

  Creative and clever uses for column formulas is the focus of this episode of Ask the Data Doctor. Using interesting data like crime, automotive and nutritional information for candy bars, Brady shows us how powerful “select matching cells” can be to quickly extract information from data tables by highlighting only the appropriate rows. Learn how to do some amazing things including computations, and group and filtering. Brady also shows us how to easily select local data filters using the lasso feature, how to use the selected(rowstate()) formula to assign values dynamically and much, much more.   Full Transcript (Automatically Generated) Welcome to today's show. And I just learned that I've got some extra time, which was great because I was I was going to be pressed for time. And I actually decided to slow things down and just cover one topic instead of two. But now I think we'll be able to cover them both. So today's show is going to show you how to do some things in jump, that build on built in features. But that adds significantly more power. And will actually start because I've got this extra time today.   We'll actually start on the selection front. And what we are going to do is first of all introduce the naming selection, name selection and column feature, which may some of you may not know is there we've got some great ways to select data and this is one of them. And actually, it's not selecting data, but it's helping you record what you selected. So let's take a look at that. Just to introduce that idea. So let's say I've got a graph and I find something interesting in the graph. And so I highlight some points. And then I look in the data table. And I would like to remember that selection so that I don't have to, you know, guess exactly how to select those points in the future. That's something that's built right into jump in the rows section of our menu. If we come down here to row selection, about halfway down, all the way to the bottom, named selection and column, what this will let us do is you get to pick a column name. And we get to say what we would like the selected columns to be labeled.   So I'll just put a one there, and unselected columns, I'll put a zero there, we'll just treat this like a flag. But I could put text if I wanted. When we're done, we hit OK. And you'll notice that all the selected rows have the label they're supposed to and the unselected rows get their label. And this is nice because if I ever came back to this table and this graph and Wanted to select those same points again, I could just select the one and then right click and select matching cells and I get my selection back.   So that's pretty nice. What I want though, is for this to be dynamic that adds significant power is if I could actually select make a selection either in the data table itself or in some report that's linked to the data table and have these points respond. And that's not happening here. So how can I get that to happen? I'm going to delete the column I just made, make a new one. And we can get this with a formula column. It's a simple formula, but you might want to write it down because it's not one that you'd probably remember offhand. It's going to be the selected function. And then as its argument, row state, and that is also a function. So we need parentheses there selected row state Okay, that's that's the function we want. Let's hit OK, and bring that in. Now watch what happens. Anytime I select a row, I get a one in that column. And the rows that are not selected, get zeros. Keep pretty straightforward what that's doing. And this, of course, works because everything's linked. It works, whether I'm in the data table, or in a report that is linked. So this is really powerful. We're going to see a couple of reasons why here.   First, the continuous case, that column, I can make it continuous, or discrete. And if I make it continuous, I can do math. I can average or I can sum and if I average, that's actually giving me the proportion of rows that I've selected. If you think about averaging ones and zeros when you take the average, it's just gonna tell you the proportions of ones you had. I could sum this up Instead, and that will give me the number of rows that got selected. And so the punchline here is that we can update a reporter graph just by making selections somewhere else in jump. So very powerful here. And to, to take a look at that in action, we'll notice that I did put this selected row state column into a data table here. Okay, I've got a continuous and a nominal version will work with the continuous version. And this dataset has complaints about people's trucks. So I've got a manufacturer name, among other things. And I've also got system flags. So 35 different columns here. And I'll just double click on the brakes column, take us over there. And so when I see a one that's telling me that this person, this row of data had a problem with the brakes. And you can see that if I select the matching cells, I look down I've got over 9000 rows of data where people had problems with their brakes.   Now rather than goes Through and scroll and do that select matching cells thing. I actually wrote a dashboard that will illustrate this point. And what the dashboard does, is down below, I've got a data table where I put event handlers in. And so that I can just click, you know, like air conditioner, there's 160 air conditioner problems apparently in this data set. And if you've looked at the report that just came up, okay, if you add those numbers in that lower graph, that's going to add to 160. Those are the 160 air conditioner, issues broken down by manufacturer name.   Similarly, up top I've got the proportion that those events represent in that manufacturers data. So what I'm saying for example, is that the 45 instances of the air conditioner problem among the Fords represent point 3% of the Ford data in the data set point 3% of Ford's rows and The data set, if you will, so we can check it out for breaks. And then we're going to get this updating. So why is this happening? It's because I am selecting by clicking this one, I'm going back to that original table and selecting those 9444 cells. And it is updating this table dynamically. So anything I do in that original table just updates this one. And what's great is that it's not dependent solely on this dashboard a row remember this is linked to that original table. So I could do something like run text Explorer. And when my text explorer results come back, I might see something I want to investigate like windshield wipers. And so I can just select the the rows that are associated with the windshield wipers. Not through a flag in the dataset, but in the text that is actually in that data set that text explorer has processed and you'll notice I've selected some rows here in the data set about 1600, almost 1700. And if I actually go back to my dashboard here, okay, these now are those 1600 rows that we selected. Okay, so this that has updated this. So bottom line is I can drive reports by looking at either sums of that flag, or averages of that flag. And it's all driven by selections I make, whether it's in a dashboard, whether it's in a report, a graph, what have you. It's a very powerful thing to do.   Now, what about if we have the nominal case? Let's take a look at a couple of those. What the nominal case is going to allow us to do is to use this selection flag as a grouping variable. So I get I can do an A versus B comparison. I can say, Well, let me let me investigate those selected cells versus everybody else. Are they the same? Are they different, they look like they're within the distribution. Let's take a look at that. And we can also do an a very powerful extension of the local data filter to be selection based instead of column based.   We'll see that as well. So here's a data set about candy bars. And when I was looking through this data set, I wanted to find a candy bar that was pretty high and non saturated fat and pretty for the calories it had. So we're, you know, the kid interested in or, you know, in this portion of the graph, and what I did was I put that selection column in that nominal column. And you'll notice that I've got a box plot here and a violin plot. But I've got my selected variable there, and it's just reading zero. And similarly, I've got a tabulate over here, and I've inverted the selection of zero. So it's showing me things that are not Selected equals zero, which is precisely selected equals one. In this case, nothing's showing up. But as soon as I select, something shows up, and so I get a list in the tabulate.   That is showing me what exactly what I selected. And that helps me not have to hover over all of these little candy bars. And in fact, that gets difficult because some of the candy bars have exactly the same amount of fat and calories. And I haven't managed to land on any of them. But sometimes when you select like, say two points, you get three or four bars. And so this is good because the tabulate will take care of that. It'll show me everything. And so this is really cool, because I could come over here and say, Well, these are some of the bars I might want. Let's compare them to the others. And I can immediately see not just in this graph, but for any for any metric that I want. I can look at the cholesterol and compare them. I can look at the sugars, the protein, I can go through this and it's not just For graphs like this, this could be for models that you fit I could have, if you think about a model, I could, I could put a local data filter on a model and say I want to filter the selected columns for this model and then build that same model, just side by side in a window and say, I want the unselected points to filter on this model. And then you'll get to see side by side model estimates. For the things you've selected. The things you haven't, think about this in the semiconductor industry, where you might be looking at any wafer map, might find something interesting in here. And you're saying okay, those points I selected, how do they compare to all the other points on that wafer? With respect to any parameter you might want to view as well as listing out, you know, whatever metric you wanted to associate with those particular locations on the way for you may have text that you wanted to render here and that could be, you know, part of your local data filter and your data. tabulate. As another example of that, let's consider, go back to clean this up a little bit.   Let's go to the San Francisco crime data, which has a lot of data, I mean too much data to ever, you know, if I if I look at this local data filter. And again, here's the tip, when you're setting this up, put your selected flag, but but select zero and then invert that. Because you won't get a one to choose. If there's nothing selected yet. You won't get a zero and a one you'll just see a zero. So you just select that and put in verse and then you're good. That's the same as picking ones. But if I turn this off, look what happens. Look at the report I get. Trying to turn this off. Let me just take off inverse. Look at what happens here. Way too much to read. This isn't I mean, this is the whole data set, and it's just it's not what I'm interested in what I want They just want to be able to highlight a little piece of this and have this update.   So not a problem. Let's just get the lasso here. Just say I just want this little piece just right there. Okay, boom, there, that that shows me exactly what I want. And this would be very difficult to do with the typical local data filter. Because if you look at the columns I have here, none of them are spatial. There's no Latin long. I mean, well, there's a Latin long, I guess, if I knew the Latin long I could, I could try to do that. But what if it's a funny shape? That would, that would be difficult. So we get around all that just by setting up with the selection flag, I can go anywhere and make any kind of shape, and it's not going to be a problem. So this in generally works anywhere that you might want to do a local data filter. If you don't have the columns that can easily help you slice and dice your data. You can always do this trick, and then slice and dice the data. Just by selecting or brushing certain points in reports. It's really, really powerful way to do that to extend that local data filter. Right. Next up, we're going to attack a problem that we hear about a lot from customers. And I think that there's a feature that's relatively recent, that a lot of people may not know. Is is called select Duplicate rows. And that lives in the rows selection menu, and it's right above name selection column. This is relatively recent. This is in the last couple of releases or so. And what's this do? Let's take a look. Let's just do that. Let's select the duplicate rows does not thing, apparently. Well, why is that that's because when I don't make a column selection, and here's the trick, if I don't make a column selection, then jump is going to look at all the columns. And so the rows have to match all the way across every column value has to be the same for a row to be deemed a duplicate. However, if I do select the column, or multiple columns, now those only those columns will be considered. And if you look at rows seven, and eight, in this respect, eight could be viewed as a duplicate of row seven. Okay, and so in that way, if I come along and try this again, ah, row eight is selected. And sometimes I have even more duplicates. If I go down here a little bit further, I'll see that rows 89 and 90 are duplicates with respect to these first two columns of row ad. Now why is this a thing and why can this help Well, we get a lot of questions about, hey, I've got some measurements that were taken at certain points in time. And I've got certain tool and part combinations for which these measurements are taken. But sometimes I get more than one, you know, I'll have to revisit this, maybe something went wrong, I have to do it again. And I'll get two, maybe three, maybe four or more rows in my data set, where I've got the same tool in part, and it's, there's measurement going on. And so I get, I don't want all of those rows, I just want the unique rows that are the most recent. And so how can I make that happen? Well, one of the ways if you want the most recent is I can sort here, just sort the date and time in descending order.   That means all the most recent dates are at the top of my data table. So the the tools and parts don't have to be contiguous, you know, they don't have to all be grouped together. For this to work, it'll just go through the table and find those duplicates for you. So I can just now do that selection. And it's going to find out those 200 selected rows that are duplicates of some other row that came first. And remember, a row gets to come first, if its date time is more recent. So I could just delete those 200 rows, if that's all I'm after, is the most recent data.   Similarly, I could I could decide I want the oldest measurement, the first measurement I want to look at, well, then I sort snd and so that now the oldest data is at the top of the data table. And I do the same trick where I might decide that hey, if I have multiple measurements, it's because I wasn't happy with you know, the standard deviation. I think the tool was messed up and I'm going to fix the tool, and I'm looking for the one with the lowest standard deviation Okay, easy enough, sort that standard deviation in ascending order. And now select the duplicate rows and delete them. So why I point this out is not a lot of people, I think know about this because it's relatively new, especially if you've been using jump a long time. And sometimes that's all you need. You're just, you know, really looking for that first occurrence, if you will, in the data table, and you want to, you want to save that and delete everything else. When things get more complicated, you're going to have to start using rankings, and we'll talk about that right now. So let's first of all, bring up a simple data set for illustrations sake. Okay, and what I'm going to do here is I'm going to do a couple things here. I want to show you you know what, I'm gonna do this. We'll make this easier for you. I'm going to put numbers here that allow me to get this data set back in the shape it is right now with respect to row orders. Okay, and I'm just gonna say I'm just gonna, this is my original order. Now I'm going to sort on group so that we can take a look right here. I'm going to put a columns of ones and those of you that watched last week's episode are going to understand what I'm about to do.   Okay, I'm unclear as to why that's not a it's not a sequence. Actually, I just want to continue sequences to the end of table. Yeah, or repeat it, whatever. Okay, there we go. All right. Now, I'm going to actually group by these letters here. And so I'm going to make a new formula column and group by that. Okay, and then I'm going to do a couple cumulative sum. Let's take a look at this. It's in the rows section of the new formulas, cumulative sum. Now what's happening? Let's just look at the A's. Okay, when I just look at the A's, I get 123456. Why? Well, because it keeps taking these ones and adding them up and accumulating them. But it starts over when I get to the B's. It starts over again, when I get to the CS.   What's nice about this is that it doesn't matter how these things are sorted. Okay, it doesn't rely on that when you write lag functions. A lot of people do this with lag functions, then the sorting matters after or you have to write a bunch of logic in there. what's what's happens when I go to the original sort order. It's still okay. If I look at my A's there's still 123456 Okay, there's still going to be numbered in the order in which they occur. In the dataset starting at the top of the table.   So what why is this a thing? Why is this powerful? Well, let's clean this up a little bit. And the first way I'm going to clean this up is to realize, if I just have a column of ones, there's actually a shorthand for dealing with that in the cumulative sum formula. So the column cumulative sum function takes a column that you'd be summing and then and then group variables if they occur. Well, I can just replace this column three, it's all ones. So I can just replace it actually with the number one. And I get the same behavior.   So in fact, they don't even need this column of ones, so we'll get rid of it. Now let's do this. I'm going to group this to make it or excuse me sort on this on these groups to make this a little more clear what's going to happen. But now I'm going to add some columns actually. And let's take a look right here at this x one variable And this x one within group, okay, I'm going to sort by x one and group. Okay, I put a rank function here. If you look at the function, it's the column rank. And it's tied though to x one, this, this is only good for that x one column. But what it does, by group it ranks the values of x one. So the lowest value is rank one. The next lowest values rank two all the way up to whatever the highest value is. I could do this in reverse order, but watch what happens. You know, make sure this is still my by variable. Yep. So let's go ahead and do this in reverse order. And we'll rank this this is the distribution section in reverse. If the trick is I don't know how many each of these groups have, they don't all have six items.   And so what happens when I look at the reverse rank? Look at what happens to me here. I need extra logic, I need to know how many items are in that group for that given column and then do subtract the rank and add one. There's a lot of drama going on. Okay. Okay, before we even go there, let's just get rid of this column and go back and take a look at these numbers. And these numbers are the same. They're the same as long as x one is sorted. These are going to match. The beauty of this column cumulative sum is, all I have to do is sort a column the way I want to sort it. And these are dynamic ranks. I don't have to write 3456 new column formulas. It's all right here as long as I remember what I sorted. If I say you know what i want to rank The X twos, okay, we'll just sort those ascending. And now these ranks actually represent x two and we could take a look at that by by sorting by group and x two.   You'll notice that these values are the ranks. Okay? If wherever I have a one for example, that's going to be the lowest value that any of these variables have for X to pick the lowest value. That's what this universe okay now the ones always show me the highest value within a given group of x two. So why this is so powerful is it now allows us to greatly extend what we did before in case we need more power for something like that, you know, select Duplicate cells. Let's let's look at what we can do with this now.   First of all, it lets us easily perform a bunch of queries. If we have to do that if we have to break out a bunch of subset tables that involve things like oh, what are the top 10 for each group I want to see their top 10 are their top three are the top three territory you know each territory want to see their top three sales, on and on and on. That's really easy with this. Okay, so let's, let's take a look. Here in the car physical data data set. I've got these cars grouped by country and type. And so I might decide, okay, within each of those categories within each combination of country and type, I want to see the three most powerful cars Actually, let's let's look at the three least powerful cars. Let's say I've got to buy a car for my teenager. I don't have a teenager yet, but when I when I buy them a car will be very large and have low horsepower.   So let's let's do this. All I have to do I've got this this column built in right now. Okay, let's take a look at the formula. It's our column, cumulative sum of ones. It's grouped by country and type. Okay, that's exactly what we want. And let's just sort that horsepower, ascending. And so now what do I know, I know that a one right here means it's the first time this country in type has shown up in the table. And that is going to mean because I sorted on horsepower, that it's the lowest horsepower for that country and type.   So I just look at the the two lowest horsepower cars, maybe I just select the one and the two. Then I select matching cells. Now all any cell with one or two get selected, and I can just bust out that subset table. And now I've got my list. You know, I could sort of clean it up and everything but the bottom line is this list now contains for each combination of country and type in this table, the two least powerful vehicles. Okay, that's what that's what this is doing. horsepower, I didn't have to write a ranking for I never will have to write a ranking formula. This is just doing it for me. And it's nice because even if I try to set this up for somebody, you know, I can't decide where they're gonna go from there. And if it's someone who's not super familiar with formula columns, they might not know to set up a rank formula. And they may want to do something like maybe they want to know power to weight ratio, you know, maybe that's what I should have looked at, it's, it's not just enough to be low powered, it has to be heavy to make sure it's really slow. And so I can select horsepower and weight. And let's do a column transform here. On ratio, we'll go reverse order. So now we have power to weight ratio. Let's sort that ascending. Okay, and these will be these cards are going to be good and slow. They got high weight and low horsepower for the for the weight. And we'll just pick let's say the top three in each category. So I just have to grab some ones, twos and threes, as long as I got all of those, select the matching cells. And now I'm ready to take a subset.   Now, you will notice the subsets coming back, you know, this should have 45 cards in it. But in fact, one of these, if you look at one of the country type combinations that we've got, it only has one car, and I think it was the other large.   Yeah, this one, this only has one car in the in the category. So that's why we only got 43 rows instead of 45. But it's so easy to do this, and I don't have to keep rewriting ranking formulas. So it's very easy to do those types of queries and just, you know, table after table after table if you've got work where you have to do that kind of thing. It's just so fast. Last thing is, maybe you've got to answer questions about you know, some do a model say on some data by country and type and you want to figure out Okay, which rows Do I need to get rid of just because there's not enough occurrences within country and type, let's say I only want to keep value, keep countries and types where they got at least 10 cars. Well, typically what you'd have to do is go through, do a summary, right by country and type. And then see that table, come back, sort the rows that come back and pick the ones that are high enough. And you'll notice I've selected 73 rows back in the main table. And so those are the 73 rows, I want these other guys don't have enough observation.   But I don't have to do all that and create, you know, run another platform, create a summary, all I got to do here. If I've got this ranking in places just go find a 10 somewhere, okay, find a 10. And I select the matching cells. Well, you don't you don't have a 10 unless you have at least 10 data points in that country and type combination. So these are precisely the combinations of country and type that have at least 10 data points. So in fact, these six combinations of country and type that are selected now are the ones we want. So I can just right click right now and select the matching cells and those are the same 73 rows that I had selected before, but I didn't have to make a summary table and sorted or do any of that work. It's all you know, these these little few seconds here a few seconds there adds up to a lot of time saved over over an analysis. So that is what I have for you today. I hope you find this useful and can go practice this. I hope that this saves you a lot of time. ... View more

Best practices on getting your data into shape for effective analysis.     Full Transcript (Automatically Generated) Hi, everybody. Welcome to today's segment. And what we are going to do today is talk about a few techniques that can help us clean up data more efficiently. Data cleanup is something that is painful in many instances, but we can, we can certainly help things. If we know a few tips and tricks, and these are all built into jump, what we're doing today that was part of my goal for today's show, is, you know, those of you that know me know that I like to script things when I have a problem that needs solved again and again. But for today's show, we're not gonna use any scripts, we're not going to write any formulas, we're just going to see how much we can do with things that are built into jump. So let's get started. And, you know, you can see here what the topics we'll cover today.   And I think we're going to hopefully do some things that you may know they were even in jump but he didn't maybe think to use them the way we're going to use them today. So let's jump right in here. First of all, let's take a look at this data table. It's been post pasted in from somewhere. And we've got a lot of missing cells, or the cells aren't missing, but the data is. And so you can see, for example, that these counties need to span down several cells. And the states are the same way. It's a long table. So it's hard to see that but we've got to spin those down. And the same with these metrics. So the first thing I want to talk about is the different ways that jump can fill in data so that you don't have to cut and paste. So let's just run through those real quickly in case you don't know about any of these.   Okay, first of all, if I select a single cell and right click, I have the option to fill that to the end of the table orders or to some row of my choosing. And if I pick a row, that's too high for the given data table, then jump extends the data table to accommodate that request. If we select more than one cell at a time, then jump does a couple things. If it can figure out a pattern to Data if that's numeric data, then it might offer us well, it will offer us to the ability to continue that sequence to the end of the table or to another row. So here, let me do that. And you can see the jump is figured out what that sequence was figured out. It's an arithmetic sequence, I'm adding one each time. And here I've got a geometric sequence.   And if I do the same thing, you can see the jump figures out, we're multiplying by three from one row to the next. Now, if I've got categorical data, like you see at the end, you know, there's no mathematics behind that. So my choices are to fill to the end of the table or fill to a given row. And what will happen is it'll just repeat that sequence over and over. And sometimes we want to do that with numbers. And that's fine too. If I had just selected these cells and decided I would repeat the sequence. I'm going to do it to the end of the table. It goes 123123 and so forth. Finally jump. And this is something I think maybe not everyone knows about. If I highlight cells where I've got gaps in between data, then my option here is to replace the missing values with whatever is above them. So here, I'll select and you'll notice that those A's, B's and C's have gotten filled in.   And so that's very useful. And something we'll use in that table we saw before. And this works with numbers as well. And notice in that first column, column four, I did not go to the end of the table. So this only happens wherever you select. So if you want to do this all the way down to the bottom of the table, select the whole column, and then do this. Okay, so just a quick review of the ways that jump can fill in data so that you don't have to copy and paste. Let's let's go to our data set. So it's pretty clear what we have to do here and this is going to be pretty easy. We're going to select the state and I want to point this out. If you right click on that The column header, then you get all these column options. That's not what you're after, you need to click in the data that you've selected.   And that's where that fill command will show up. So we're going to replace the missings with the previous values for the states and counties. And then these metrics, we're just going to repeat over and over again, because we were lucky enough in this data set, that every county has four rows of data, births, deaths, the net international migration and the net domestic migration. So we can just come here, right click and fill to the end of the table. And we're done. Okay, we can leave the data like this, we could split it out using this column. That's a lot of times why we want to do this and so we can split the data out into a wider format, but we're ready to go. So that's a really easy example.   That would not have been fun with copy and paste, but it's easy with the built in tools we have. Let's go to our next example. And This is going to involve a little bit of cleanup where and this is I'm sure this has happened to you before you've you've pasted in data. Or maybe you've, you've tried to do an import, but there's been so many header rows that things didn't work out the way you wanted. And so what ends up happening is you have a header row in the first row or some other early row of jump.   And really, you want those to be column names. The easiest way to make that happen is to select that whole row, and you're just going to copy it. Okay, now you won't see it from a right click. So you're going to use your edit menu or you're just going to do a Ctrl C, or Command C on a Mac. Then you're gonna come over here to the left and just select all these columns. And you can do that with a Ctrl A if you need to, and then we'll Ctrl V will paste and immediately all those names go in the columns just like you wanted. Okay, now our columns are named appropriately. So that's that's the first thing is just a quick cut and paste of these column names. It saves a lot of time.   Now the next Thing is, I'm going to show something that a lot of you have seen before. And that is when I right click on a selected cell, I can select the cells that match that cell. So here I've got a negative four in this column, and I can select all the rows in the column that have a negative four. And that's with a right click. What you may not know is that if I do this for a pair of columns, a pair of values in the same row, they don't have to be adjacent. I don't see that menu option here. So you might not know that it exists, it still exists, you got to come to the left here in the selection bar for that row.   And then you can select matching cells. And if you look down below, in the lower left of the table, you can see only three rows in the data set have this criteria, but the point is, you can do it and we'll take a look at this later. It also works for multiple selections. So if I if I select a 123 or four and I select the matching cells Then I go through the whole table and select anywhere that I see a one, a two or three or a four in that in that column.   So how does that help us here? Well, if you look at this data, we've got this header information repeated every so often. And but the thing is, it's the same, it's the same values. It's the same corruption if you will. So I'm just going to take this, this whole row selected and right click and select the matching cells. And you can see that that is selected 10 rows, I'm just delete them. And boom, we're ready to go. Now we have to clean up a little bit, you can see that by the left justification.   These columns are actually being stared stored as characters and so are these. But you're going to see a cool technique later today with Peter and Harry. called them, but I won't go through the whole thing. But I'll show you where to see it called standardized attributes. If I right click and click on standardized attributes, This lets me change attributes for all six of those columns at once. So real time saver, so I could make those numeric very easily. Alright, so next, we're going to use this select matching cells, we're gonna use these last three bullets in a more complicated example.   So let's, let's pull that up and take a look. Now this data has been pasted in from a website, I'll show you how I got this. This really happened to me is not fun. So I went to our discovery website and I we've got let me go to the abstracts here. So we've got papers and different information about them and the authors and in everything else, and I just copied this whole page and pasted it into jump.   And so what I got was something that looked like this. The issue is, of course, that everything's in a single column. And I don't want it to look like that how I want it to look, I'll just run this script here. This is what I want. I want every paper on its own line, and things broken out by session, topic, title, authors level and abstract. So this would be easy with the tricks we've learned today, except for one thing.   And the one thing is that some papers have two authors, some papers have one author, some papers have more authors, four, or five or six authors. And that's what's going to keep us from using the trick that we used here where I could just say, Look, I know the five things that I have for every paper, and I'm just going to copy those over and over and over again. It's not going to work like that for us, because we don't always have the same number of things. So what can we do about that?   Well, there's a lot of things you can do about that. You saw my script. You know, that does it instantly, but you have to write a script. And if you use formula columns, you might have to write formulas. So how can we do that with with none of those things? Let's take a look. First of all, let's select all the blank cells with select matching cells and delete those rows. We're also going to delete the rows where we have, you know, non useful information. So that's some of these first few rows and then at the bottom of the table, we've got the same thing going on. So let's get rid of those.   We'll just highlight all those rows and get rid of them. So that's kind of first step. Next step, I'm going to delete column two here, we don't need that column. And we're going to pass this through a quick recode, where I'm just going to collapse the whitespace. Let's get rid of redundant white spaces. And we'll just do this in place. We don't need a we don't need a new column for this. Once that's done, go ahead and hit OK. A recode rather. Now let's take a look at papers. How do we know that we've got a paper Well, if you if you take a look at the first line in this data table, you'll see the word session. Okay session ID. And this this is how you set off papers.   There's, there's a paper, there's a paper, there's another paper. And how can I flag this? Well, the first thing I want to do is I'm going to do a Ctrl. Find Ctrl F, and I'm going to change session ID to session underscore ID. And I do that for for two reasons. One, it lets me double check. You saw it's made 44 replacements, I can see that Yeah, I had 44 papers, so that that makes me happy. But secondly, I've created a word here. Now, that is unusual. It's not something I'm likely to find. If I if I search the text of an abstract, you might find the word session, but you're not going to find session underscore ID. So it helps make that unique. Now what we're going to do, is we're going to do another recode but this time, we will allow the new formula to be made. And we're going to say, or the new column rather, I want the first word I just want the first word at every line.   And what's that do for us. Now I can see wherever session ID is, and we're going to use a cool copy and paste with a flagging technique to set up all the papers and group them together so that we don't mix up the contents for each paper. And here's how we'll do it. We'll create a new column with a double click, and we'll use the fill technique we had earlier. To fill this column with zeros. Then what we'll do is we'll change that first entry to a one. That's our flag that's flagging the beginning of a paper. I'm going to Ctrl C, I'm going to copy that into my buffer. And then when I go to Select matching cells in this session ID column or the, you know, the second column we created. Watch what happens when I move around. Those rows are still selected. And because of that, I can just paste into this last column I had. So now I've got a flag. It's a new mayor.   I could hear What I want to accomplish next, what I want to accomplish next is use these flags with a new formula column A, it's in the row section here, and we're going to do a cumulative sum and watch what happens here. Let's look at that first paper, my cumulative ones, Psalm starts at one, and then I keep adding zero, so it doesn't change until it gets to that second paper. And it keeps changing only when I find a new paper. So what's nice about this is I'm going to remove that formula. And this is now this is a grouping column. This is now something I can use. I'll just call it paper to group my papers and make sure that the information for each paper stays together. And I'm going to need this as a grouping column when I split out later because papers have different numbers of rows. Now the next thing I can do also with this, this idea of a cumulative sum, this might be counterintuitive. I'm going to change all these entries to one by filling to the end of the table, and then I'm going to group on paper. So I'm going to right click go to the new formula column, and group by that. Now, what sorts happens when we do a cumulative sum on these ones?   Yeah, I get counting, right? Because I'm accumulating ones 1234567. And then it starts over, in each paper that the counting starts again. Why is that nice? Let's take a look at one and two, and select the matching cells. Notice, that's always the session ID of that paper. It's always the title of the paper.   Now, I wish that this happened everywhere, but it starts to break down for author because here's two authors. And so I've got the numbers three and four, but I only have one author in the next paper, so I just get the number three. So the bottom line of this is the last three sections with three rows of each papers data. The last three rows are always going Topic level and industry but they don't always necessarily equal 567. They're going to equal maybe four or five, six, or 910 11. So how do we deal with that? Let's do another formula column. And we'll do this now with the distributional section and do a rank and reverse. And so now look what this is done. This is allowing us to to count backwards. We're counting those same numbers one through seven for that first paper, but now they're in reverse. why this works is is is now if I select the 123 in this column, I do always get those last three parts, I always get the topic, the level the industry, and this is exactly what's needed. So I'm going to break these down by taking out the formulas so that they don't change as I change data here and watch what we can do. Okay, I don't even I don't need this column anymore.   In fact, I don't need this column anymore. Okay, what do I need to do, I need to make sure that these fives sixes and sevens actually turn into high values. Now, everywhere the last three lines in the data, I want it to be high values, I don't want the authors to be able to collide as this goes 34567 authors. So I'm just going to be lazy. And type 50 6070. I know I'm not going to have 40 something authors anywhere. Now again, we'll use a a tricky form of copy paste, very convenient here. I'm going to copy these three things into the buffer, come to this column with the 321 and right click select the matching cells. Now I've got the last three rows of every table. When I come back over here, I'm going to paste these with a Ctrl V and now look what's happened.   I've got one two, which we know are the session and the title. I've got 50 6070 which I know is a topic level in industry. And starting at three I count as high as I need you to get all the author's my go to for my go to 12. And who knows, the point is, I can now split this table. So let's do it. We'll split it out. And we'll split by this column that we made. And we'll group by paper. And so now I don't even need this grouping column anymore. And this is exactly what we wanted. Okay, if I, if I take a look at column three, that's where the authors start. And let's look as we go along. Some papers have more authors than others. Okay, it looks like the highest number was eight. So that's six authors. We can combine all those.   That's what we wanted. We didn't want to lose any authors. We want them all in a single column. So we're going to highlight all of these columns with a shift to try to highlight them all with the shift and dread. There we go. And we're just going to use our columns utilities. Right here, columns utility and combine those two. And now you'll see that in between column two and column three, I have an author's column, columns, three through eight is still selected in the table. So I'm just going to hit the delete key, and they're gone. So now I have exactly what I want, I just need to do a little more cleanup. And it's pretty easy to do. I'll go through a call this first column session and title, this topic, and I've got level here, abstract. Okay, so that's all looking good.   And finally, I can just come through. And as a one last thing, I can do a recoding here. And so I'll just I'll recode on all of these things. And I'll do these in place. So for this first one, I'll collapse the whitespace again. And now that we've done that I can come along and Do the first word if I want to get rid of that first word that's in the advanced section. You can say I want everything but the first word that's cleaned up. You the same thing here. But first word. Do it here. And finally, we'll just do another collapsing of whitespace. Just to be vigilant. Alright, that's it. Okay, we got exactly what we wanted. With no scripting, no formulas, not any that we had to make anyway. So there's a lot you can do here to clean up nasty data. And this is this will not be the last time we talked about cumulative sums on the on the doctor. So that's all I've got for today. I hope that helps you I hope this can help you with your data cleanup and back to Julian   ... View more

Learn about the key new capabilities in this release of JMP and JMP Pro.  Try them using the JMP Jounal.  View the video. ... View more

  When more than 3 columns are added to an axis in Graph Builder, the orientation of the plots (in this case, boxplots) is changed, and the scales are separated like you'd find in a parallel plot. This requires a novice user to do two things (s)he might not know to do: 1) Place the columns of interest on the axis opposite the one on which (s)he ulitmately wants the boxplots to appear and 2) change the axis setting to "Parallel Merged" by right-clicking the axis and selecting Combine Scales > Parallel Merged.   This addin automates all of this, so boxplots with combined scales can be immediately created.    Directions: 1) Select the columns in the data table corresponding to the variables you wish to compare with boxplots. 2) Run the addin.   Output: A graph of boxplots with a common scale will be created.   Also attached is the script on which the addin is based, so interested users can see an example of expression handling, which is one way to address analyses where the columns of interest are not known until run-time.   Cheers, Brady   ... View more

Level: Intermediate Job Function: Analyst / Scientist / Engineer Brady Brady, JMP Principal Systems Engineer, SAS Bernd Heinen, JMP Senior Systems Engineer, SAS Sometimes questions that seem simple on the surface can be incredibly difficult to answer analytically – even for PhD statisticians. For these problems, analytical solutions may not exist, may be difficult to derive, or may depend on assumptions the analyst is unwilling to make. That’s the bad news. The good news: In many cases, answers can be found through simulation, and in JMP there are many ways you can perform simulations, both interface-driven and JSL-based. In this session, we’ll take a closer look at how easy it is to perform simulations using a few of JSL’s built-in functions, and use these ideas to make quick work of some fun questions that would be really tough to answer otherwise. ... View more

Voted Best Invited  Paper by Summit attendees.   NOTE 1: Much of the content in this talk was based on two of @Jeff_Perkinson's blogs (thank you, Jeff!), which can be found at the links immediately below. Be sure to have a look.  -Brady   https://community.jmp.com/t5/Jeff-Perkinson-s-Blog/10-Things-You-Don-t-Know-About-JMP/ba-p/21314 https://community.jmp.com/t5/Jeff-Perkinson-s-Blog/10-More-Things-You-Don-t-Know-About-JMP/ba-p/28959   NOTE 2: We are currently having difficulty getting the video of the screen recording uploaded to the community. We apologize, and thank you for your patience as we get this sorted.       Level: Beginner Brady Brady, JMP Technical Enablement Engineer, SAS As you already know, JMP is an amazingly rich tool for data analysis and exploration. That also means that you know that there's more than one way to do almost anything in JMP. The 10 (or more!) tips and tricks shared here will make you more efficient and introduce you to techniques, keyboard shortcuts, formulas and more. Come in and level-up from novice to expert, or expert to guru.   The JMP Journal includes example of these 10+ tips:   Shortcut Keys Help > Books > Quick Reference JMP has keyboard shortcuts for many common tasks. For example, many of the cursors in JMP can be selected with single-key shortcuts. A:            Arrow ?:            Question-mark S:            Selection R:            Scroller H:            Hand B:            Brush L:             Lasso Z:            Magnifying Glass C:            Crosshairs T:            Annotate Selecting “Help > Books > Quick Reference” from the main menu will open the Quick Reference Guide, which lists these, and many more, keyboard shortcuts.   Make combined table       When you right-click on a report table, you can place the output into a data table by selecting “Make into Data Table”. When multiple tables of the same type are present within a report—for example, when an analysis has been generated using “by” groups—you can select “Make Combined Data Table”, which conveniently gathers the table you’ve clicked and all others like it into a single data table.   Combine Windows     Select “Window > Combine Windows” from the main menu to quickly create dashboards by merging several open windows in JMP. Drag columns into report windows   Click and drag a column from the data table Columns pane (center left in the data table) onto an open report’s graph. Drop it in one of the red zones to replace the column currently being used in the graph with the column you’ve dragged over.   Internet Open   Select “File > Internet Open” from the main menu, paste a webpage link into the dialog, and let JMP search for importable tables on the webpage.   After JMP lists the tables it has found, select the ones you’d like to import and JMP will place the data into tables automatically. A table script is saved so you can repeat this using a JSL script, and if the table holds images, a table script is provided to load these into the table if desired.   Copy / Paste for Column Names Any number of column names can easily be copy/pasted from the columns pane in the left center of the data table. Select the desired columns, then use copy/paste keyboard shortcuts (CTRL-C/CTRL-V on Windows, CMD-C/CMD-V on Mac) to copy or paste the names. This makes it easy to do cleanup on many column names at once.   Standardize Attributes Standardize attributes allows you to set column properties for many columns simultaneously.    Select the columns whose properties you want to change, then right-click at the top of one of the selected columns and select “Standardize Attributes”.   Word() + Words()      These two functions separate a string into words based on the delimiter you choose. Word(k, string, delimiterString) returns the k-th word in the string, based on the delimiters contained in delimiterString. For example, Word( 2, “Mg#55%A%17”, “#” ) returns the string “55%A%17”, while Word( 2, “Mg#55%A%17”, “#%”) returns the string “55”. Words(string, delimiters) returns a list of all the words in the string, so Words(“Mg#55%A%17”, “#%”) returns the list { “Mg”, “55”, “A”, “17” }. Both functions use the empty string, “”, to break the string into individual characters. Therefore, Word (5, “abcdefg”) returns the string “e”. If delimiterString is omitted, the space string, “ “, is used as a delimiter.   Copy/Paste using Select Matching Cells / Invert Selection When rows are selected in a data table, copy + paste operations apply to only those cells that are highlighted. This makes it easy to place text into the buffer using CRTL-C, select rows in a table using some criteria (using the “Rows > Row Selection” submenu or selecting points in a graph, for example), and paste the text into only those cells highlighted in the table. This technique is particularly helpful when cleaning up data, or categorizing rows based on selections made in graphs.   Multiple Response: change permutations to combinations     When working with multiple response data, sometimes the order of the responses is important, and sometimes only the set of responses is important—not the ordering. Any string operation will consider “cat, dog” to be different from “dog, cat”—the Summary platform works this way, for example. You can make sure that all elements within a given set appear in alphabetical order (so only “cat, dog” would be possible), using the following approach: From the main menu, select “Cols > Utilities > Make Indicator Columns” to create an indicator column for each element found in the multiple response data. As you will notice, the indicator columns appear in alphabetical order. Next, select all of the indicator columns, then select “Cols > Utilities > Combine Columns” from the main menu, making sure to check the “Selected Columns are Indicator Columns” checkbox. The column produced is like the column you originally selected, except that all elements are in alphabetical order, which ensures that each set of responses has a unique representation.   Value ordering     JMP orders categorical axes alphabetically, but you can order them any way you want by using the Value Ordering column property. Right-click at the top of a column and select “Column Properties > Value Ordering”. Using the dialog, reorder the values any way you would like. After applying the changes, the values will appear in the order you specified in all graphs and reports.   Axis column property This column property lets you specify the axis settings for a column, so you don’t have to re-edit this variable’s axes whenever the variable is used in a graph. To access, right-click on the top of the column and select “Column Properties > Axis”.   Using Copy/Paste within dialogs   Many of JMP’s dialogs support copy/paste operations. In the example used in the talk, we copied a 51-row by 2-column block of data from a table and pasted it into a Value Labels dialog, greatly simplifying the data entry process and reducing the opportunity for errors.   Summary Histograms In addition to computing summary statistics, the Summary platform can write histograms to the summary table. The histogram option is located in the Summary platform’s launch dialog, at the bottom of the Statistics drop-down menu.   Auto-join from Summary You can perform a summary, and drag results back to the source table. This also works for newly created columns in the summary table.   ex: P.8 - P.2 for weight         Any column in a linked summary table—even ones that you created after the summary table was produced—can be joined to the source table by drag + drop, without the need for a Join. To do this, click and hold on the column header you’d like to drag, then drag it over to the source table and drop it on a column header. The column will be inserted into the table in the position where you dropped it.   Easily support “By” groups in scripts. Use a summary table to cycle through selections in the main table—avoiding the need for cumbersome where clauses in JSL To support “By” groups for analyses that don’t support these natively, Summary tables can be used instead of compound “Select Where” clauses. Once the “By” groups are known, create a Summary table, hidden if desired, and move through it by selecting one row at a time. As this is done, all corresponding rows in the source table will be selected, so a subset can be taken and the analysis can be performed on only the data in the subset table. When you’ve finished, close the subset table, and repeat the process for each remaining row in the summary table.   Number within group for quick queries and subsets The simple formula “Col Cumulative Sum ( 1, <<by group columns>> )” counts the instances of each row within the specified groups, in the order in which they appear in the table. Each time the table is sorted, the numbers update to reflect this; in this way, the new column serves as a dynamic ranking tool, allowing us to answer questions that otherwise would require time-consuming query formation or additional ranking columns for each question posed. In the example in the talk, we used “Country” and “Type” as grouping columns. This allowed us to easily answer questions like “Which are the 3 cars of lowest weight, within each combination of Country and Type?”. To answer, we merely sorted “Weight” in descending order, selected a 1, 2 and 3 in the cumulative sum column, then invoked “Select Matching Cells” and produced a Subset table from the selected rows. Using a similar approach on time-stamped data, it would be easy to produce subsets containing the most recent observation for each tool, or the first 5 survey responses received within each demographic, for example.   Pareto Frontier Rows > Select Dominant   (Wikipedia): Pareto efficiency or Pareto optimality is a state of allocation of resources from which it is impossible to reallocate so as to make any one individual or preference criterion better off without making at least one individual or preference criterion worse off, [and] the Pareto frontier is the set of all Pareto efficient allocations. In JMP, we can select the Pareto frontier by choosing “Rows > Row Selection > Select Dominant” from the main menu. In the example from our talk, this resulted in the selection of 6 of 75 candy bars. Each of the 6 bars was optimal in the sense that it was impossible to select any other bar both having fewer calories, and containing at least as much unsaturated fat as the optimal bar.   Name selected in column Choosing “Rows > Row Selection > Name Selection in Column” from the main menu creates a 2-vaued column, where one value is assigned to all currently selected rows, and another value is assigned to the unselected rows. This makes it easy to re-select exactly the same set of points at a later date, using this new column and the “Select Matching Cells” feature.   Row State columns Columns to which you assign the “Row State” data type are able to store the table’s row states for retrieval later. Row selection, exclusion, hiding, markers and colors are examples of properties that the row state column can store, and you may use as many row state columns as you wish, allowing you to easily switch among various row state configurations according to your needs. Row state columns are easily identified by their red “star” icon.   The “Selected” formula column Selected(Row State())    What if we could make “Name Selection in Column” dynamic? We can! Using the simple formula “Selected ( Row State ( ) )", we make a formula column that contains 1 for all selected rows, and 0 for all unselected rows. Using this new column, we can easily perform comparisons between the selected and unselected rows. By assigning this column the Nominal modeling type, we can select rows in one graph, and in another graph, compare these to the unselected rows using histograms, boxplots, analysis platforms, or anything else we can imagine. If we assign the Continuous modeling type to this column, then by computing the mean of the selection column, we can see what proportion of rows have been selected… doing this with a “By” group can easily reveal interesting differences between groups.   Using Graph Builder’s Save to Table and image labels to provide drilldowns From Graph Builder’s red triangle menu, select “Make into Data Table” to save the graph images to a table. You can then join these images back to the original data table and use them as markers, providing the viewer more information on demand.   Optimizing with custom desirability functions JMP’s profiler helps you find the factor settings that optimize outputs according to criteria you set through desirability functions. By default, these functions are specified by dragging the default desirability curves into appropriate shapes. You can, however, optimize with respect to a custom desirability function by following these steps: Add the custom desirability function as a formula column, dependent on the prediction formulas for those responses you wish to optimize. Select “Graph > Profiler” from the main menu, cast the prediction columns and the custom desirability column into the “Y” role, and click “OK”. From the Prediction Profiler’s red triangle menu, select “Optimization and Desirability > Desirability Functions” to turn on the desirability functions. Select “Optimization and Desirability > Set Desirabilities”, and set the importance value of all prediction columns to 0, while setting the importance of the custom desirability function to 1. Finally, select “Optimization and Desirability > Maximize Desirability” to optimize the inputs subject to the custom desirability function.   Drag images into frameboxes               JMP graphs support a variety of built-in backgrounds, but you can also drag image files into the graphs. This feature can be used (along with the necessary JSL) to get data that would be otherwise unavailable into JMP.   Two scripted examples were used in the talk: the first used an image of a function to place (x,y) points into a data table, after which a model was fit and prediction formulas were saved. These allowed us to generate data from the function as desired. In our second example, we sketched the density of a distribution, allowing us to sample from the distribution without first having to derive a mathematical representation of it. ... View more