Stack/Reshape Data

[George Ford]

I'm looking for a clean way to convert this data to long-style format, but it's more of a stack/append than a reshape.

The final data should be period, return, mkt (r_GSPC), so I need to stack [period, r_firm, r_GSPC] by firm.

One way is to create individual files for each firm, then stack using append. But I'm curious if there's a more straightforward way.

I've tried stack, but couldn't get it to do what I wanted. I can imagine some other kludgy ways to do it, but curious if there's something really simple I'm missing.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(period r_AMZN r_GOOG r_T r_AAPL r_TSLA r_GSPC)
21186             .           .            .             .           .    .
21187    .012694394  .016279915   -.02336369  -.0001746598  -.01028583    .006378433
21188    .004466025  .003614757    .00925308   .0046343626 -.008324531    .004020543
21189    .016033294  .014465924   .003677456    .011321095  .006210416    .007009146
21192    .014321633 .0042640576   .002880042  -.0037213564    .0607547    .0016609638
21193    .004664812 -.000614502  -.002610749 -.00011473173 -.008118245    .0013020835
21194   .0013003597 -.003304841   -.02347964 -.00022931755  .003320894    -.0011128458
21195    .017661398  .002635719 -.0038303896    .005664129   .00936466    .007009026
21196    .022093365   .01502866   .011447542    .010273386 -.005132258    .006726926
21200 -.00026062113 -.000445588  -.004890321   -.005095164  .011356387    -.003530713
end
format %td period
label var period "Day" 
label var r_AMZN "AMZN Continuously Compounded Ret" 
label var r_GOOG "GOOG Continuously Compounded Ret" 
label var r_T "T Continuously Compounded Ret" 
label var r_AAPL "AAPL Continuously Compounded Ret" 
label var r_TSLA "TSLA Continuously Compounded Ret" 
label var r_GSP "_GSPC Continuously Compounded Ret"

[Andrew Musau]

Does this do it?

rename r_GSPC rGSPC
reshape long r_, i(period) j(which) string

Res.:

. l, sep(0)

     +-------------------------------------------+
     |    period   which          r_       rGSPC |
     |-------------------------------------------|
  1. | 02jan2018    AAPL           .           . |
  2. | 02jan2018    AMZN           .           . |
  3. | 02jan2018    GOOG           .           . |
  4. | 02jan2018       T           .           . |
  5. | 02jan2018    TSLA           .           . |
  6. | 03jan2018    AAPL   -.0001747    .0063784 |
  7. | 03jan2018    AMZN    .0126944    .0063784 |
  8. | 03jan2018    GOOG    .0162799    .0063784 |
  9. | 03jan2018       T   -.0233637    .0063784 |
 10. | 03jan2018    TSLA   -.0102858    .0063784 |
 11. | 04jan2018    AAPL    .0046344    .0040205 |
 12. | 04jan2018    AMZN     .004466    .0040205 |
 13. | 04jan2018    GOOG    .0036148    .0040205 |
 14. | 04jan2018       T    .0092531    .0040205 |
 15. | 04jan2018    TSLA   -.0083245    .0040205 |
 16. | 05jan2018    AAPL    .0113211    .0070091 |
 17. | 05jan2018    AMZN    .0160333    .0070091 |
 18. | 05jan2018    GOOG    .0144659    .0070091 |
 19. | 05jan2018       T    .0036775    .0070091 |
 20. | 05jan2018    TSLA    .0062104    .0070091 |
 21. | 08jan2018    AAPL   -.0037214     .001661 |
 22. | 08jan2018    AMZN    .0143216     .001661 |
 23. | 08jan2018    GOOG    .0042641     .001661 |
 24. | 08jan2018       T      .00288     .001661 |
 25. | 08jan2018    TSLA    .0607547     .001661 |
 26. | 09jan2018    AAPL   -.0001147    .0013021 |
 27. | 09jan2018    AMZN    .0046648    .0013021 |
 28. | 09jan2018    GOOG   -.0006145    .0013021 |
 29. | 09jan2018       T   -.0026107    .0013021 |
 30. | 09jan2018    TSLA   -.0081182    .0013021 |
 31. | 10jan2018    AAPL   -.0002293   -.0011128 |
 32. | 10jan2018    AMZN    .0013004   -.0011128 |
 33. | 10jan2018    GOOG   -.0033048   -.0011128 |
 34. | 10jan2018       T   -.0234796   -.0011128 |
 35. | 10jan2018    TSLA    .0033209   -.0011128 |
 36. | 11jan2018    AAPL    .0056641     .007009 |
 37. | 11jan2018    AMZN    .0176614     .007009 |
 38. | 11jan2018    GOOG    .0026357     .007009 |
 39. | 11jan2018       T   -.0038304     .007009 |
 40. | 11jan2018    TSLA    .0093647     .007009 |
 41. | 12jan2018    AAPL    .0102734    .0067269 |
 42. | 12jan2018    AMZN    .0220934    .0067269 |
 43. | 12jan2018    GOOG    .0150287    .0067269 |
 44. | 12jan2018       T    .0114475    .0067269 |
 45. | 12jan2018    TSLA   -.0051323    .0067269 |
 46. | 16jan2018    AAPL   -.0050952   -.0035307 |
 47. | 16jan2018    AMZN   -.0002606   -.0035307 |
 48. | 16jan2018    GOOG   -.0004456   -.0035307 |
 49. | 16jan2018       T   -.0048903   -.0035307 |
 50. | 16jan2018    TSLA    .0113564   -.0035307 |
     +-------------------------------------------+

[George Ford]

Yep. I had to rename r_GSPC (to mkt) first so it kept it separate, but it worked perfectly.

Thanks Andrew.