reshape data

[jessica cheng]

Dear Statalist users,

I have been benefited a lot from this forum.

I have some data that look like this:

	AAL.O	AAL.O	ACB.O	ACB.O
Timestamp	Trade Close	Trade Volume	Trade Close	Trade Volume
Timestamp	AAL.O_close	AAL.O_volume	ACB.O_close	ACB.O_volume
2/1/2020	29.09	6,275,633.00	24.24	2,666,621.75
3/1/2020	27.65	14,020,066.00	24.00	1,597,244.67

I need to organize it into:

stock_code	time_stampe	Trade Close	Trade Volume
AAL.O	2/1/2020	29.09	6,275,633.00
AAL.O	3/1/2020	27.65	14,020,066.00
ACB.O	2/1/2020	24.24	2,666,621.75
ACB.O	3/1/2020	24.00	1,597,244.67

Is there a easy way to do it?

Any advice is highly appreciated!

[Jared Greathouse]

Please use dataex to show us exactly how the data look.

[jessica cheng]

Thank you, Jared, for your reply.

The data look like this:

"" "AAL.O" "AAL.O" "ACB.O" "ACB.O" "AG.N" "AG.N"
"Timestamp" "Trade Close" "Trade Volume" "Trade Close" "Trade Volume" "Trade Close" "Trade Volume"
"Timestamp" "AAL.O_close" "AAL.O_volume" "ACB.O_close" "ACB.O_volume" "AG.N_close" "AG.N_volume"
" 1/2/2020" "29.09" "6275633" "24.24" "2666621.75" "12.22" "821130"
" 1/3/2020" "27.65" "14020066" "24" "1597244.666666667" "11.97" "840132"
" 1/6/2020" "27.32" "6108646" "22.8" "2570956.416666667" "11.65" "1169081"
" 1/7/2020" "27.22" "6197079" "21.96" "2121672.583333333" "11.95" "833014"
" 1/8/2020" "27.84" "10497296" "20.88" "3321788.833333333" "11.18" "1432161"
" 1/9/2020" "27.95" "6901065" "22.32" "2463460.166666667" "10.85" "935321"
" 1/10/2020" "27.32" "8108419" "19.8" "3051681.416666667" "11.01" "827923"

I would like to organize it into something like this:

stock_code	time_stampe	Trade Close	Trade Volume
AAL.O	2/1/2020	29.09	6,275,633.00
AAL.O	3/1/2020	27.65	14,020,066.00
ACB.O	2/1/2020	24.24	2,666,621.75
ACB.O	3/1/2020	24.00	1,597,244.67

Any advice is highly appreciated.

[jessica cheng]

Let me retry using dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input int Timestamp double AALO_close long AALO_volume double(ACBO_close ACBO_volume)
21916 29.09  6275633 24.240000000000002         2666621.75
21917 27.65 14020066                 24 1597244.6666666667
21920 27.32  6108646 22.799999999999997 2570956.4166666665
21921 27.22  6197079              21.96 2121672.5833333335
21922 27.84 10497296              20.88 3321788.8333333335
end
format %td Timestamp

I would like to organize it into sth like this:

stock_code	time_stampe	Trade Close	Trade Volume
AAL.O	2/1/2020	29.09	6,275,633.00
AAL.O	3/1/2020	27.65	14,020,066.00
ACB.O	2/1/2020	24.24	2,666,621.75
ACB.O	3/1/2020	24.00	1,597,244.67

any advice is highly appreciated

[Clyde Schechter]

gen `c(obs_t)' obs_no = _n
reshape long @_close @_volume, i(obs_no) j(stock_code) string
rename (_*) trade=
replace stock_code = substr(stock_code, 1, 3) + "." + substr(stock_code, 4, 1)
drop obs_no

[jessica cheng]

Great thanks, Clyde!

Your code works like magic.