Reshaping both long and wide together

[Scott Rick]

Hi. I have the following data which is both in wide and long format where the variables date_ymd status together identify unique observations. I am trying to reshape this to a long dataset where each of the st* variables is a row, with three new columns with the status variable values of Confirmed, Recovered, and Deceased. So each st variable will have one date against it and the corresponding values for that date in the Confirmed, Recovered, and Deceased variable columns.

I have tried the code below which yields output #2 below. This does not give me the three new columns with the status variable values of Confirmed, Recovered, and Deceased. Is there anyway I can do this with the reshape command or in a short manner without reshaping, then generating three separate variables for Confirmed, Recovered, and Deceased, replacing those values with the equivalent status variable value, and then copying values from multiple observations for the same date for the Confirmed, Recovered, and Deceased variables to bring it to one st-date observation?


reshape long st_, i(date_ymd status) j(tp, string)

1. original data

input str10 date_ymd str9 status long sttt int(st_an st_ap st_as st_br)
"2021-05-30" "Confirmed" 153396  20 13400 3245  1475
"2020-08-01" "Recovered"  55117  88  9276 1457  3521
"2020-10-02" "Confirmed"  79883  10  6555 1416  1431
"2020-09-15" "Confirmed"  91098  17  8846 2409  1575
"2020-04-26" "Confirmed"   1607   0    81    0    26
"2021-07-13" "Confirmed"  40314   3  2567 2169   102
"2020-06-07" "Confirmed"  10882   0   199  208   239
"2021-04-13" "Recovered" 185297   8  4228  590  4157
"2020-11-26" "Recovered"  43174   5  1031  150   682
"2020-11-01" "Confirmed"  45928   8  2618  166   777
"2021-04-28" "Confirmed" 379404  52 14669 3045 13374
"2020-08-02" "Confirmed"  52672  98  8555 1178  2762
"2021-01-18" "Deceased"   9987   5    81   33   144
"2021-03-28" "Deceased"  68206   2  1005   48   351
"2020-10-18" "Confirmed"  56519  25  3986  318  1152
"2020-05-06" "Confirmed"   3602   0    60    1     7
"2021-01-14" "Confirmed"  15677   7   179   19   314
"2021-01-01" "Recovered"  20159   1   326   40   463
"2020-07-28" "Confirmed"  49631  25  7948 1371  2480
"2020-05-27" "Confirmed"   7246   0   134  101    68
"2021-06-05" "Confirmed" 114488  17 10373 3781  1007
"2021-02-03" "Deceased"  12925   0    95   14    92
"2020-09-01" "Deceased"  78168  28 10368 2684  1928
"2021-10-27" "Confirmed"  16351   2   567  244     5



2. reshaped data 

​​​​​​​clear
input str10 date_ymd str2 state str9 status long st_
"2020-04-01" "an" "Deceased"    0
"2020-04-01" "an" "Confirmed"   0
"2020-04-01" "an" "Recovered"   0
"2020-04-01" "ap" "Confirmed"  67
"2020-04-01" "ap" "Recovered"   1
"2020-04-01" "ap" "Deceased"    0
"2020-04-01" "ar" "Confirmed"   0
"2020-04-01" "ar" "Deceased"    0
"2020-04-01" "ar" "Recovered"   0
"2020-04-01" "as" "Confirmed"  15
"2020-04-01" "as" "Recovered"   0
"2020-04-01" "as" "Deceased"    0
"2020-04-01" "br" "Confirmed"   3
"2020-04-01" "br" "Recovered"   0
"2020-04-01" "br" "Deceased"    0
"2020-04-01" "ch" "Recovered"   0
"2020-04-01" "ch" "Deceased"    0
"2020-04-01" "ch" "Confirmed"   2

[William Lisowski]

Perhaps this points you in a useful direction.

reshape long st_, i(date_ymd status) j(state) string
reshape wide st_, i(date_ymd state)  j(status) string
rename (st_*) (*)

. list in 17/24, sepby(date_ymd) abbreviate(16)

     +---------------------------------------------------------------+
     |   date_ymd   state   Confirmed   Deceased   Recovered    sttt |
     |---------------------------------------------------------------|
 17. | 2020-07-28      an          25          .           .   49631 |
 18. | 2020-07-28      ap        7948          .           .   49631 |
 19. | 2020-07-28      as        1371          .           .   49631 |
 20. | 2020-07-28      br        2480          .           .   49631 |
     |---------------------------------------------------------------|
 21. | 2020-08-01      an           .          .          88   55117 |
 22. | 2020-08-01      ap           .          .        9276   55117 |
 23. | 2020-08-01      as           .          .        1457   55117 |
 24. | 2020-08-01      br           .          .        3521   55117 |
     +---------------------------------------------------------------+

[Scott Rick]

Thanks, William. That really helped!