Preparing Your Data

This session uses a case study on Medical Treatment Types to show how you examine your data, determine a goal for the analysis, and then prepare the data table to accomplish that goal.

See how to:

• Examine data from three different tables
  • Table 1: Morning Results for April and May
  • Table 2: Afternoon Results for April and May
  • Table 3: All June Results
• Join Tables(Tables>Join) and select common Variable ID and an inner join to capture overlapping/common data from both tables
• Apply Modeling Types
• Standardize Attributes (Cols>Standardize Attributes>Column Properties)
• Stack Table (Tables>Stack)
• Concatenate Tables (Tables>Concatenate)
• Use Text to Columns
• Add a Formula Column
• Recode
• Use Value Ordering to define order of variables on all charts
• Hide Rows
  • When you Hide rows, they are hidden in all plots, including all open plots
  • Hiding rows does not exclude them from calculations and analyses
• Exclude Columns
  • Excluded columns are not considered in your analysis
  • Excluded columns remain visible in the data grid

Tip: When preparing JMP data (join, concatenate, stack, split, etc.) check Keep Dialog Open. If you want to check your work, check Create Source Column to give info to help you during checking.  You can delete the Source Column from final table later.

Check Create Source Column to add column to table; check Keep Dialog Open in case you want to check your work.

Final Prepared Data Table

Questions answered by Alisa @alisa_h_lowery and Olivia @O_Lippincott  during the live webinars:

Q: Why did she concatenate rather than join?

A: Concatenate adds rows. Join adds columns.

Q: Can you give some ideas of what is the differences in output to expect in some common platforms when I model numeric data as ordinal vs continuous?

A: If the modeling type is continuous=blue triangle, JMP will treat that like anything you can report as a decimal for or report as an average. The red or green bars means it's nominal or ordinal data & JMP treats that like categories. Often JMP will count that data where continuous data, you can report the average.

Q: What is the difference between join and stacking?

A: Stacking is making the data tall, or stacking variable columns on top of each other.  Joining is adding columns or variables to another table. You can rearrange your JMP data table by stacking two or more columns into a single new column, preserving the values from the other columns. Or, you can stack a set of columns into multiple groups. The various ways that you can stack columns are explained in Stack Options.  See: https://www.jmp.com/support/help/en/17.2/#page/jmp/stack-columns-in-data-tables.shtml

Q: With Concatenate, do you not risk duplication of data and how would minimize this?

A: You can use Rows>Rows Selection>Duplicate Rows if you suspect you have duplicates.

Q: I'm wondering how to best prepare data for JMP to carry out repeated measures ANOVA and subsequent multivariate analysis - long format or wide? Does JMP prefer one over the other? (R seems to prefer long). Context: I am doing a longitudinal mouse experiment with 50+ baseline measurements, and all of them are repeated every X months until they die.

A: You can analyze in long or wide format for repeated measures. Check out https://www.jmp.com/en_us/learning-library/topics/mixed-models-and-repeated-measures.html

Q: How do you add "Notes Fictional Dataset",

A: See: https://community.jmp.com/t5/Discussions/Notes-in-Data-Table/td-p/266172

Q: Can we transfer data from MATLAB to JMP so we skip excel step?

A: Yes, if you have a file use File>Open> then choose the MATLAB file type.  Make sure you are showing all file types (All Files).

Q: If the data is a black dot in Control Chart it won’t show in the analysis. It will if it is zero.

A: Correct. Depending on your situation and what problem you are trying to solve, you can choose to recode missing data to 0 or something that makes sense for your context, to be safe in a new column so you keep the original data. 

Q: If you merge columns with the same name and their contents are different, will the resulting column just have a comma between the two values and treat it as unstructured text?

A: In the example, she created a column ID and joined by the column ID. Basic joining by columns will not merge text from two different tables into the same column. 

Q: Does fixed decimal change the values, or just change the way the values are displayed in the table?

A: Just the way it is displayed on the table. If you have lots of values after the decimal, they will be retained and when you change the FIXED DEC>WIDTH DEC value you to a larger number you will see more decimal places.

Q: If we use Join, does is join columns side by side, compared to Concatenate?

A: Joining is adding columns, concatenate is adding rows.

Q: Is there a way to hide data columns that have been delimited/separated already and aren't going to be used anymore? Hide rather than delete.

A: Yes, you can hide columns from the columns panel. Start by r-clicking the column name.

Q: How did you split Column name split (Date/Time) into 2 columns?

A: Utilities>Text to Column

Q: Could you change model type from continuous to discrete? Could you change it if one instance would round up or down and another instance the discrete values should just drop the decimal place entirely and not round up?

A: Not in the data & model types. You can use a formula to round up or round down.

Q: Will you go over some other recode options?

A: See short video: 

Resources

• Recode option list and explanations
• Documentation on Assigning Characteristics to Rows and Columns
• Documentation on joining tables
• Documentation you can work through sequentially to learn about restructuring your data