Swapping order of columns in a matrix

[Chris Larkin]

Hi all,

I'm looking to swap the order of columns in a matrix, and make all bar one of the columns cumulative of another. For example, I'd like to make matrix reg look like matrix mean

sysuse auto, clear
replace foreign= 2 if _n<14

mean price, over(foreign) level(95)
matrix define mean = r(table)

regress price i.foreign
matrix define reg = r(table)
mat def reg = reg[1..rowsof(reg),2...] //this gets rid of the base column
mat def reg = reg[invorder(reg),.] //this does not work...

mat li mean

mean[9,3]
            price:     price:     price:
         Domestic    Foreign  _subpop_3
     b   .5610282  .63846818  .74588462
    se  .04053582  .05589942  .11667455
     t  13.840308  11.421732  6.3928649
pvalue  4.229e-22  6.520e-18  1.361e-08
    ll  .48024044   .5270608  .51335258
    ul  .64181597  .74987556  .97841665
    df         73         73         73
  crit  1.9929971  1.9929971  1.9929971
 eform          0          0          0

mat li reg

reg[9,3]
                 1.          2.            
           foreign     foreign       _cons
     b   .07743998   .18485641    .5610282
    se   .06911881   .12151304   .04084935
     t   1.1203893   1.5212887    13.73408
pvalue   .26632347   .13262877   1.128e-21
    ll  -.06037902   -.0574337   .47957692
    ul   .21525897   .42714653   .64247949
    df          71          71          71
  crit   1.9939434   1.9939434   1.9939434
 eform           0           0           0

So, the first step is to swap the order of the columns, and the second is to make 1.foreign = 1.foreign + _cons and 2.foreign = 2.foreign + _cons. Any pointers / code would be appreciated! I've tried looking at invorder() but I cannot get it to work, and don't really understand the examples in the matrix help files.

Out of curiosity, and on a similar note, i'd be interested to know why the SEs from regress price i.foreign are not the same as the results from mean price, over(foreign)

[Chris Larkin]

Update: it's one of those things where as soon as you post something on Statalist you make progress in your own search. Alas, just after i made this post i stumbled upon matselrc (search matselrc)

matselrc reg reg2, c(`=colsof(reg)'/1)

I've now reversed the order of my matrix columns using the below code, but am still looking for a function / command to make 1.foreign and 2.foreign cumulative

[Chris Larkin]

If anyone had any thoughts on this, i'd be keen to hear them. No worries if not though - i'm sure i'll get there in the end

[John Mullahy]

Chris: If you are willing to do these calculations in Mata it should be pretty straightforward. You can bring your matrix into Mata using -st_matrix(...)-. Now suppose z is your Mata matrix. Reversing the order of columns:

zr=z[.,range(cols(z),1,1)]

Adding the last column of zr to the first (c-1) columns but not to the last column itself:

zrnew=(zr[.,1..cols(zr)-1]:+zr[.,cols(zr)]),zr[.,cols(zr)]

I'm not sure this is exactly what you want, but this should give you some idea of calculations you can use to get what you want.

[John Mullahy]

P.S. Chris: I realized I might have misunderstood what you mean by "swap". E.g. if you want to make columns 2 through c equal to columns 1 through (c-1) and column 1 equal to column c, then:

zr=z[.,2..cols(z)],z[.,1]

Whatever rearrangement you want to do is easy once you figure out the column indexes.

[Chris Larkin]

Thanks a lot John. This is really helpful. I've not used Mata before so this is all new stuff for me. I've now got the below:

sysuse auto, clear
replace foreign= 2 if _n<14
lab def foreign 0"Domestic" 1"Foreign" 2"Some other cat"
lab val foreign foreign
expand 3

regress price i.foreign, r
matrix define reg = r(table)
mat def reg = reg[1..rowsof(reg),2...]
matselrc reg reg, c(`=colsof(reg)'/1)
mat li reg


mata
reg = st_matrix("reg")
reg2 = (reg[.,2..cols(reg)]:+reg[.,1]),reg[.,1]
reg2
st_matrix("reg2",reg2)
end
matselrc reg2 reg2, c(`=colsof(reg2)'/1) //not sure why I need to repeat this line. It seems the mata code has flipped the columns back.


mat li reg2
mat li mean

I fear I was not very clear in my previous post though. I'll try and reframe the problem. I want to transform the matrix stored from

regress price i.foreign, r
matrix define reg = r(table)

to look the same as the matrix stored from

mean price, over(foreign) level(95)
matrix define mean = r(table)

Only with one small modification: that the coefficients (the first row of the matrix reg) for the independent variable sum with the constant term. By this I mean I want the value of the beta for foreign==1 to be equal to the value of beta for foreign==1 + the value of the constant. This is what the matrix mean shows in the above and #1. All the other rows of the matrix I want to stay the same

[John Mullahy]

Chris: See if this does the trick:

[your Stata estimation code, then…]
mata
regnew=st_matrix("reg")
regnew=(regnew[1,4],(regnew[1,2..3]:+regnew[1,4]))\regnew[2..rows(regnew),(4,2,3)]

It's not very elegant (e.g. in general one might want to make the indexing arguments generic rather than specific to the 3- and 4-column matrixes you have), but if that's not an issue then this should be workable.

(P.S. Ever since Mata became incorporated into Stata several versions ago I abandoned for the most part using the on-board matrix commands in Stata. I find Mata to be incredibly flexible, so much so that in some instances—e.g. simulations, bootstrapping, etc.—I use Mata as the "master" language and Stata as the "slave" language. Anyway, so much for my unsolicited editorial.)

[Chris Larkin]

John, this is great. Thanks a lot. The code does what I want, but you're right i will need to spend some time making it more abstract - it's definitely got me on the right path though!

Yes, i've not worked with mata before so it's a new world for me. I see the benefits for this little project, and i'm sure will return to it again in the future

[Chris Larkin]

I have a follow up question if you're still keen to give advice.

I've abstracted the Mata code, and now want to integrate it within a Stata loop. Unfortunately, this is proving difficult as the end at the end of the Mata code exits the loop. The below code is self contained and runs fine outside of a loop. Any idea how to proceed with this?

sysuse auto, clear
replace foreign= 2 if _n<14
lab def foreign 0"Domestic" 1"Foreign" 2"Some other cat"
lab val foreign foreign
gen over2var1 = mod(_n, 3)
lab def over2var1 0"Label 1" 1"Label 2" 2"Label 3"
lab val over2var1 over2var1
expand 3


qui tab foreign  //replace 'foreign' here with call to over1 var
local cols = `r(r)'
di "`cols'"

loc seq1
loc seq2

forvalues i = 1/`cols'{
    if `i' == `cols'{
        exit
    }
    if `i' != 1{
        loc comma ,
    }
    loc seq1 `seq1'`comma'`i'
    loc seq2 `seq2'`comma'`=`i'+1'
}
di "`seq1'"
di "`seq2'"

local END end
summ over2var1, meanonly
forvalues l = 0(1)`=`r(max)'-1'{
    regress price i.foreign mpg rep78 headroom if over2var1==`l'
    mat def covreg`l' = r(table)
    
    mata
    regnew=st_matrix("covreg`l'")
    regnew`l' = (regnew[1,cols(regnew)], regnew[`seq1'..cols(regnew)-(cols(regnew)-`cols')]:+regnew[1,cols(regnew)])\regnew[2..rows(regnew),(cols(regnew),`seq2')]
    regnew`l'=st_matrix("regnew", regnew`l')
    `END'
}

mat li regnew0
mat li regnew1
mat li regnew2

[John Mullahy]

Chris: I'm not sure of the best way to handle this, but this recent thread should be helpful: https://www.statalist.org/forums/for...erated-in-mata

[Chris Larkin]

Perfect! Many, many thanks John. I'm well on my way now, and really appreciate the time you've given my post