This article will focus on how the data can be transposed. Matrix transposing is the process of switching the order of rows and columns. The values will be transposed into a column for each row in the original data and vice versa. In this article, we are going to use three datasets. The first dataset is shown below:
Using this dataset, we will see how the row can be transposed into the columns and columns into the rows. To do so, the below command can be used:
xpose, clear
xpose is the command to transpose from rows to columns or columns to rows. The data after the transposition will look as shown in the below figure.
However, after the transposed, variable names i.e. firmid, year2000 etc. have gone missing. To avoid this issue, we can use the varname option as explained below.
xpose, clear varname
The varname option will be added at the end of the command to keep the names of the variables. The new variable will be generated and named as _varname.
The firmid and all other years’ data were numerical in the last dataset. The string data or any string variable in the dataset can also be transposed. We will use the second dataset that contains the string variable (symbol). The dataset is shown below:
We will face an issue if we use the previous command for the transposed. The issue is that the values of the string variable will turn into the missing values, as shown below figure:
To avoid this issue, we use sxpose rather than xpose. Before using the command, it is a must to install the sxpose using the below command:
ssc install sxpose
After installing the command, use the below command:
sxpose , clear force
sxpose is the command. Option force has been used in the command because the dataset contains numeric values too.
The data will be transposed into the format shown below figure:
The limitation of our second dataset is that the string variable in our dataset has a dot in its values. Due to this, we failed to use the symbol variable values or names as the variable’s names. For your understanding, let’s run the below command on the second dataset. The command is given below:
sxpose , clear firstnames force
On running the above command, we will get the data in the format that is shown below:
The symbol names do not appear as the variable’s names. We will use the third dataset in which we will use the same command, but this time, the symbol names will not have dots. The third dataset is shown below:
We will run the same command on this dataset.
sxpose , clear firstnames force
Here, you will see that the symbol names will appear as the variable’s names (A1 to A7). One of the limitations of the sxpose command is that it does not provide us with a separate column for the variable names. Therefore, in that case, we use another command that is sxpose2. This command also needs to the install below running. The command to install sxpose2 is given below:
ssc install sxpose2 sxpose2 , clear firstnames varname force
After running the above command, the data will be transposed into the below format:
