Order variables based on their mean

[Antoine Dupont]

Hi all,

I have the following problem: I have a large dataset that contains many variables and I have to order all variables based on their mean. The variable with the largest mean should be at the beginning of the dataset. For example, assume I have the following data set:

  
Observation
Var1
Var2
Var3
Var4
Var5

1
3
10
100
1
200

2
3
12
80
2
300

3
4
11
90
1
250

4
4
9
100
2
400

Then, the code should order my variables as follows:

 
Observation
Var5
Var3
Var2
Var1
Var4

1
200
100
10
3
1

2
300
80
12
3
2

3
250
90
11
4
1

4
400
100
9
4
2

I was playing around with collapse/reshape, but did not come up with a solution. Is there an (efficient) way to achieve this task?

Would really appreciate your help.

Best,
Antoine

[Clyde Schechter]

Since the data as shown do not lend themselves to importation into Stata, I've developed and tested this code on the auto data set that comes with your Stata installation. You need only modify it by replacing the -ds price-foreign- command with one containing the actual names of the variables you need to reorder by mean, and in the final line substitute the Observation variable for make.

set more off

sysuse auto, clear
ds price-foreign
local vlist `r(varlist)'

preserve

collapse (mean) `vlist'
local i = 1
foreach v of local vlist {
    gen name`i' = "`v'"
    rename `v' mean`i'
    local ++i
}
gen _i = 1
reshape long mean name, i(_i) j(_j)
sort mean
replace _j = _n
replace name = name + " " + name[_n-1] if _n > 1
local ordered_vlist = name[_N]
restore
order `ordered_vlist', after(make)

In the future, please help those who want to help you by using the -dataex- command to post example data. That makes it possible to simply copy and paste what you post into Stata's do-editor and get a 100% faithful replica of your example data set, making testing of solutions possible. You also benefit in that you are likely to get a response sooner, and it will be one that is tailored to your data--not one that fails and has to be revised because the data you have are different from what the responder worked with. You can get the -dataex- command by running -ssc install dataex-. Instructions for using it are in -help dataex-.

[Antoine Dupont]

Thank you! Your reply was extremely useful for me.

[Kabira Namit]

This code is incredibly useful. Many thanks!

Since the data as shown do not lend themselves to importation into Stata, I've developed and tested this code on the auto data set that comes with your Stata installation. You need only modify it by replacing the -ds price-foreign- command with one containing the actual names of the variables you need to reorder by mean, and in the final line substitute the Observation variable for make.

set more off

sysuse auto, clear
ds price-foreign
local vlist `r(varlist)'

preserve

collapse (mean) `vlist'
local i = 1
foreach v of local vlist {
gen name`i' = "`v'"
rename `v' mean`i'
local ++i
}
gen _i = 1
reshape long mean name, i(_i) j(_j)
sort mean
replace _j = _n
replace name = name + " " + name[_n-1] if _n > 1
local ordered_vlist = name[_N]
restore
order `ordered_vlist', after(make)

In the future, please help those who want to help you by using the -dataex- command to post example data. That makes it possible to simply copy and paste what you post into Stata's do-editor and get a 100% faithful replica of your example data set, making testing of solutions possible. You also benefit in that you are likely to get a response sooner, and it will be one that is tailored to your data--not one that fails and has to be revised because the data you have are different from what the responder worked with. You can get the -dataex- command by running -ssc install dataex-. Instructions for using it are in -help dataex-.

[daniel klein]

FWIW, I do not think that this kind of data manipulation is very useful. It seems pretty close to spreadsheet-thinking, which is typically not the best way of handling problems in Stata.

Having said that, also, see vorter from SSC.