数据整理问题

现有数据如下（表一），根据num排名:4,2,5,7,10,9,6,1,3,13进行整理数据，希望得到表二的数据

name	num	value
10000	1	2
10000	2	5
10000	3	24
10000	4	35
10000	5	84
10000	6	6
10000	7	7
10000	8	10
10000	9	13
10000	10	52
10001	1	22
10001	2	24
10001	3	21
10001	5	42
10001	6	114
10001	7	87
10001	8	38
10001	10	51
10001	11	24
10001	13	33
10001	17	17
10002	1	5
....省略n个	...	...
10101	...	...

表二如下：

name/num	4	2	5	7	10	9	6	1	3	13
10000	35	5	84	7	52	13	6	2	24	.(missing)
10001	.(missing)	24	42	87	51	.(missing)	114	22	21	33

-表二value用粗体表示
-10002到10101还会继续，在此不多赘述。希望能有能循环可以从10000到10101的算法。

try this using reshape:


local order "4 2 5 7 10 9 6 1 3 13"
local no : word count `order'

bysort name : gen _j = _n
reshape wide num value, i(name) j(_j)
local nn = _N+1
set obs `nn'
local vs ""
forv i=1/`no' {
        local ii : word `i' of `order'
        gen v`ii' = .
        local vs "`vs' v`ii'"
        replace v`ii' = `ii' in l
        foreach vm of var num* {
                local jj = substr("`vm'",4,100)
                replace v`ii' = value`jj' if `vm' == `ii'
        }
}
keep name `vs'
order name `vs'
gen _v=1
replace _v=-9 in l
sort _v, stable
drop _v
export excel format2.xlsx, replace missing(".(missing)")

保存数据
keep if num<=10 & num~=.
tab name num [fw=value]

这个应该很简单
excel应该都可以哦

It's better to use pivot table in excel and format as you want.

jjjj6666 发表于 2014-3-11 22:26
It's better to use pivot table in excel and format as you want.

agreed.但是一定要用stata做怎么做？这不是在学习stata么。。{:3_60:}

dxystata already gave the command, you can do, will get you close to ehat you want, then output to excel and format.  Otherwise, using stata, you may have to use "file" related output to bold and format.

keep if num <= 10
tab num, matrow(num)
tab name num [fw=value], matrow(name) matcell(value)
local order "4 2 5 7 10 9 6 1 3 8"
drop _all
svmat name
gen name=string(name1)
drop name1
svmat value
local nrow = rowsof(num)
local nn = _N+1
set obs `nn'
gen _v=1
replace _v=-9 in l
local vs ""
forv i=1/`nrow' {
        local ii : word `i' of `order'
        replace value`i' = . if value`i' == 0
        local vs "`vs' value`ii'"
        replace value`i' = `i' in l
}
order name `vs'
replace name="name/num" in l
sort _v, stable
drop _v
export excel format.xlsx, replace missing(". (missing)")

jjjj6666 发表于 2014-3-12 01:14
dxystata already gave the command, you can do, will get you close to ehat you want, then output to e ...

厉害！请问如果并不是1-10内排序而是4 2 5 7 10 9 6 1 3 13那怎么办？因为我试了下如果第一句num<=10如果没有程序做出来结果是不对的？好像问题就出在svmat value，只能显示前10个value的matrix。如何能做到在横置之后显示更多的value？

其实就是要每一个name（10000，10001等等）根据几个规定的num（3，2，5，13等等）按照本身所带value进行筛选。继续等算法！谢谢！