Transpose multiple variables to multiple records

This function was used to transpose variables to records. 

Following two examples will demonstrate the common use of this function:

1.       Example I

A longitudinal study followed up 200 newborn babies’ height, weight at different age.  Maximum number of follow up visits is 10.  Data was organized as each child had one record (one line), each record had following variables:

·         lgth.a lgth.b …, lgth.j, representing height at visit 1, 2, …, 10;

·         lbs.a, lbs.b, …, lbs.j, representing weight at visit 1, 2, …, 10;

·         age.a, age.b, …, age.j, representing age at visit 1, 2, …, 10 respectively. 

 

Now you want to reshape the data as each line for each visit, and each visit has age, height and weight variables.  You original data had 200 lines, and you want a new data that had 200*10=2000 lines.

Click here to download the sampledata.xls for exercise.

A screen shot of sample input window:

Click the “Show index code” button, you will see following new variables and the visit’s coding for each visit.

View output files

 

2.      Example II

For multivariate analysis, you may want to transpose multi-variates to multi-records.  For example, data file: demo.xls, each subject had one record (one line), each line had four respiratory symptoms: cough, phlegm, wheeze and sob (shortness of breath).  You want to reshape the data to each subject had 4 lines and each line had one symptom and one indicator variable.  A screen shot of this example is as below:

A screen shot of sample input window:

Notes:

1.       The left variables list originally lists all variables.  Select the variable and then click “>” or “<” to move the variables between “variables” list to hightlighted “variables for transpose” list or “variables to keep” list. There are 4 columns of “variables for transpose” representing 4 measurements.  To highlight a column, click it.

2.       Name new variable name for each column of transposed variables.

3.       Name an index variable (default is “NEW.VIDX”) to indicate the visit.

 

 

To illustrate the data record and variables in new output data, below list 2 records of original data in example II and corresponding new data lines in new data:

Original data lines

SUBJ

COUGH

PHLEGM

WHEEZE

SOB

SEX

AGE

1

0

1

0

0

1

25

2

1

0

1

0

2

30

 

 

 

 

 

 

 

New data lines

SUBJ

symptom

NEW.VIDX

SEX

AGE

1

0

1

1

25

1

1

2

1

25

1

0

3

1

25

1

0

4

1

25

2

1

1

2

30

2

0

2

2

30

2

1

3

2

30

2

0

4

2

30

 

 

 

 

 

“NEW.VIDX” = 1, 2, 3, 4 represents original variable “COUGH”, “PHLEGM”,”WHEEZE”, “SOB” respectively.

                                                                                                                                 

4.       “SUBJ”, “SEX”, “AGE”, etc are variables to keep.  These variables’ value will be duplicated in each line for each subject.

 

Click “Run”, a new sub-node (node text is the output file name) will be added to left tree view under node “Data”. 

 

View output files

 

Right click the node, and then select the output file to view.