Disentangling and Organizing Wide Data - Mastering JMP
gail_massari
| Updated on
Video was recorded in February 2025 using JMP 18.
Do you have wide data where each observation occupies a single row, and where you have individual or groups of columns useful for making predictions? Do you need some direct way assure that your data is set up to assess relationships accurately and completely? Would you like to establish repeatable routines to assure the quality of new records when they are added?
Wide format is great for regression modeling and data mining, but can be overwhelming to clean up to make sure it is useful, easily visualized, and accurately analyzed.
In this session, we will follow these steps to explore JMP tools for organizing and providing an overview of large datasets, particularly those with many columns:
- Organizing columns and column properties using Column Manger (Column Manager, Group Columns and Standardize Attributes) to efficiently add, edit, and delete columns.
- Rename columns using Recode Column Names.
- Identify, explore, visually inspect and manage missing values and outliers that might distort estimates and bias results
- Visualize relationships using Column Switcher and Screening tools.
- Provide interactive management reports on the wide data.
Suggested Prerequisites
- Some experience managing and analyzing wide data.
After you use the attached journal to try the techniques in the video, consider using two companion Hands-On Activities created by statistician and JMP Educator @Di_Michelson for further practice: Explore Outliers Hands-on Practice and Solution and Explore Missing Values Hands-on Practice and Solution.
Questions answered by @Laura_Higgins and @DonnyKopp at the live webinar:
Q: How do I widen columns so I can see the whole name on one line?
A: Go to the line next to the column. It will show an arrow facing both ways and then drag it to the right. To do them all at once, select them and grab will format them all to same width go to Cols>Column Manager>Shift or Ctrl click (multiple columns)>click Edit Column Properties>Width>Set desired width>Click Apply.
Q: If I have 50000 rows of data, how can I easily go to row specific row? And if I have multiple random rows selected, how can I quickly page down to those selected rows?
A: Row>Row Selection> and then type in the row you are looking for. You can also see the rows you have selected by going to the Rows panel on the left> right click on "Selected" > "Data View". This will put all of the selected rows in a new data table.
Q: Is there a shortcut to move between selected rows?
A: F6 and F7 move you between selected rows, up and down.
Q: When transforming a table to another table, is there a way to average data from two rows to a single row if a primary key is used? For example, if sample 1 appears twice, is it possible to average the sample 1 to just one row.
A: You can do the analysis in several ways. Analyze>Tabulate, then sample on left, mean of response in the middle. Or Also: Tables>Summary>Mean.
Q: How do I search within Rows:
A: Click in the search box to get options for how to search the values you put in search box.
Q: Is there a way to make the Horizontal layout default in the distribution module?
A: Yes. Go to File> Preferences>Platforms>Distribution>Horizontal Layout.
Resources
- Blog by JMP SE @JerryFish on handling outliers that Laura mentions in her video. This blog contains links to Jerry's three other blogs on outliers.
- Videos on Workflow Builder. Part 1 and Part 2.
There are a couple of questions that were brought up, and I would like to address them:
Q: For very large datasets, is there a way to set spec limits for all columns? Can spec limits be imported from an external file/table?
A: This is a good way to approach it: Analyze > Quality and Process > Manage Limits > Select the columns and click Process Variables > OK > Load from Limits Table > Choose the table that has the limits > OK
