In data analysis, we often need to merge multiple columns of indicators into one column. For example, we might need to convert a 100-column, 5-column data table representing 100 people across 5 observation points into a 500-column, 1-column data table to meet further needs for graphing and analysis. So, how can this be quickly achieved in JMP? This article will introduce techniques for merging multi-column data in JMP.
When analyzing data, we often encounter situations where an individual has multiple key analytical indicators (such as data from multiple time points in follow-up data). When importing the data, these indicators are usually imported into different columns (e.g., data from 5 time points are imported into 5 columns). While this import method seems intuitive, sometimes it is necessary to place these indicators in the same column during analysis.
If it's just 5 columns, copying them one by one won't take much time. But what if there are 20 columns or even more? Not only is it time-consuming and laborious, but copying and pasting is also prone to errors. Today, we'll learn how to use the "stacking" function in data cleanup to quickly merge multiple columns into one column, while adding a new identifier variable to display different metrics.
Below, we'll use the sample data included with JMP to demonstrate how to implement data stacking. You can find the sample data in the top menu "Help " --> " Sample Data Library," as shown in Figure 1 below.
Figure 1: JMP Help Documentation and Sample Data
This time, we will use the Blood Pressure.jmp data file from the sample database as an example. The overall data structure is shown in Figure 2.
Figure 2 Sample Materials
The 20 participants were divided into four groups. The variable BP 8M refers to the blood pressure measurement taken at 8:00 AM on Monday. Therefore, this data records the blood pressure measurements of each participant at three different times each day: Monday, Wednesday, and Friday. Yes, this is standard repeated measurement data, and many doctors in clinical practice collect data in this format.
first step:Stack multiple columns of indicators into one column.
First, imagine this scenario: after obtaining the data, you want to compare whether there are differences in the blood pressure measurements of the four groups of subjects. In this case, the blood pressure measurement value becomes the dependent variable y, and the measurement time point and grouping become the independent variables x1 and x2. The current data format obviously does not meet the requirements of our subsequent analysis.
We need to stack the nine time points into two columns: one for the time points and the other for the measurement values, ensuring a one-to-one correspondence. How can we do this in JMP? Just one step!
Figure 3 - Selecting " Stack " in the "Tables" menu will bring up a stacking dialog box.
- Stack columns – Select the columns you want to stack.
- Output table name – Input the name of the stacked table
- Stacked Data Column / Source Label Column - After stacking, two new variables will be created: one is the time point (source label column), and the other is the blood pressure measurement value (stacked data column). We can set variable names for them respectively. If no changes are made, the new variables will use the system default names 【 Data 】 and 【 Label 】 as variable names.
The operation process is shown in Figure 4:
Figure 4 - Demonstration of column stacking operation process
The newly generated data table is shown in Figure 5:
Figure 5 - Newly generated data table after column stacking
Blood pressure was measured at 9 time points for each of the 20 participants, resulting in a newly generated data table with 180 columns of observations. The labels and data are the original time points and blood pressure values, respectively. This allows us to proceed with subsequent analysis.
Step 2: Stack multi-column indicators into a single multi-column indicator.
For different analytical purposes, you might be thinking: the operation is the same as before.
However, the difference lies in the needs of the analytical purpose.
Figure 6 - Multi-column stacking
Because the variables are stacked in three consecutive columns, forming a total of three columns, we set the column number to 3, check "Consecutive," and click OK, as shown in Figure 7 below:
Figure 7
The generated data table is shown in Figure 8. Each participant's daily data is presented in one column:
Figure 8
In daily data organization and summarization, besides stacking multiple columns into one, there are also situations where a single column is split into multiple columns. In a future article, we will introduce the reverse operation of "stacking"—splitting. What is the significance of splitting? And what are some points to note? Stay tuned for the upcoming # 10DataAnalysisTips column!
Original article link: Data Cleaning with "Stacking"—A Powerful Tool for Merging and Processing Multi-Column Data
Recommended reading:
Note: This is the fifth article in this series. Click #10DataAnalysisTips to review other articles.
