Transpose parts of a matrix

[Arman Aksoy]

Hello,

I'm currently working on a project that requires me to extract a large amount of values from a matrix the current format is as follows:

ID	Var 1	Var 2	Var 3	Var 4	Var 5	...	Var N
1	String	Int	Int	Int	Int	...	other
2	String	Int	Int	Int	Int	...	other
3	String	Int	Int	Int	Int	...	other

The variables I want to extract are integers that represent a purchase date for items. so for instance Var 4 would take the value of 0, 1, 2 or 3 depending on the period they were purchased.

However, I dont need every variable in the dataset. Furthermore, the data format I require for my algorithm is as follows.

ID	Value	Var
1	Int	Var 3
1	Int	Var 4
1	Int	Var 5
2	Int	Var 3
2	Int	Var 5
2	Int	Var 7
2	Int	Var 8
3	Int	Var 4
3	Int	Var 5

In essence the second column would take the value of the period 1, 2 or 3 depending on when it was purchased and the third column would be the item identifier which was the row name.

So far the only solution I could came up with was to go through each entry one at a time and recode into a matrix. However this is time consuming and not a best practice as far as i know. Below is a sample code:

//Q8 -- Fill in the created matrix variables

local num = 8
local varlist "a b c d e"
local i = 1
local j = 1

gen Q`num'all=""

while `i'<=118679 {                     

    if IndexQ`num'[`i'] ==1 {
        foreach x of local varlist {
            if Q`num'`x'[`i'] ==1  {
                local let`j' = "`x'"
                local j = `j' + 1
            }
    }
    replace Q`num'all = "`let1'" in `i' 
    
}


    if IndexQ`num'[`i'] ==2 {
        foreach x of local varlist {
            if Q`num'`x'[`i'] ==1  {
                local let`j' = "`x'"
                local j = `j' + 1
            }
    }
    replace Q`num'all = "`let1',`let2'" in `i' // new variable to be created for transaction mode
    
}

    if IndexQ`num'[`i'] ==3 {
        foreach x of local varlist {
            if Q`num'`x'[`i'] ==1  {
                local let`j' = "`x'"
                local j = `j' + 1
            }
    }
    replace Q`num'all = "`let1',`let2',`let3'" in `i'
}
    
if IndexQ`num'[`i'] ==4 {
        foreach x of local varlist {
            if Q`num'`x'[`i'] ==1  {
                local let`j' = "`x'"
                local j = `j' + 1
            }
    }
    replace Q`num'all = "`let1',`let2',`let3',`let4'" in `i'
}

if IndexQ`num'[`i'] ==5 {
        foreach x of local varlist {
            if Q`num'`x'[`i'] ==1  {
                local let`j' = "`x'"
                local j = `j' + 1
            }
    }
    replace Q`num'all = "`let1',`let2',`let3',`let4',`let5'" in `i'
}        

    
local i = `i'+1
local j=1

}

I have looked into some solutions but can't figure this one out with the existing stata functions. Could someone point me towards the right direction ?

Thank you.

[William Lisowski]

Added in edit: I see that you have posted this in Statalist's Mata Forum, which is used for discussions of Stata's Mata language, which is different than Stata's command language, and different than Stata's matrix commands. Your question will see a more appropriate and much larger audience if you post it in Statalist's General Forum.

Realistically, it is unlikely that someone is going to look at your code and suggest improvements without being able to test their suggestions.

Descriptions of data are well-meant but insufficient to help those who want to help you. Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a helpful response, you need to show some example data.

Be sure to use the dataex command to do this. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data,

[Arman Aksoy]

Thank you very much for your answer, I'll keep that in mind for future questions. In the mean time, if anybody finds this tread and has a similar issue the solution I found was to use reshape. The solution was provided by chatgpt.