Reshaping data

[Ellen McGrane]

Hi,

I currently have data in this format showing the timing of bets over the day (HH= hour of the day using 24 hour clock, and the number represents the bet i.e. bet1 bet2 bet3...):

ID	HH1	HH2	HH3	HH4	HH5	HH6	HH7	HH8
1	10	12	15	15	.	.	.	.
2	9	12	13	19	21	.	.	.
3	15	18	19	19	22	23	.	.

How would I reshape this into long format so that I have one HH variable repeated for each bet for each individual:

ID	HH
1	10
1	12
1	15
1	15
2	9
2	12
2	13
2	19
2	21
3	15
3	18
3	19
3	19
3	22
3	23

I have looked at the reshape command:

reshape long HH1 HH2 HH3 HH4 HH5 HH6 HH7 HH8 HH9 HH10 HH11 HH12 HH13 HH14 HH15 HH16 HH17 HH18 HH19 HH20, i(ID) j(n)


I am unsure which variables to put in i/j and keep getting errors such as this:

variable ID already exists
Data are already long.
variable HH1 already defined

Any help would be appreciated - very lost!

[George Ford]

try

reshape long HH, i(ID) j(hour)

[William Lisowski]

Your problem is in part that the reshape long command wants, not variable names, but variable name stubs - the unchanging part of the variable names you want to reshape.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id hh1 hh2 hh3 hh4 hh5 hh6 hh7 hh8)
1 10 12 15 15  .  . . .
2  9 12 13 19 21  . . .
3 15 18 19 19 22 23 . .
end
reshape long hh, i(id) j(bet)
drop if missing(hh)
sort id bet
list, sepby(id) noobs

. reshape long hh, i(id) j(bet)
(j = 1 2 3 4 5 6 7 8)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations                3   ->   24          
Number of variables                   9   ->   3           
j variable (8 values)                     ->   bet
xij variables:
                        hh1 hh2 ... hh8   ->   hh
-----------------------------------------------------------------------------

. drop if missing(hh)
(9 observations deleted)

. sort id bet

. list, sepby(id) noobs

  +---------------+
  | id   bet   hh |
  |---------------|
  |  1     1   10 |
  |  1     2   12 |
  |  1     3   15 |
  |  1     4   15 |
  |---------------|
  |  2     1    9 |
  |  2     2   12 |
  |  2     3   13 |
  |  2     4   19 |
  |  2     5   21 |
  |---------------|
  |  3     1   15 |
  |  3     2   18 |
  |  3     3   19 |
  |  3     4   19 |
  |  3     5   22 |
  |  3     6   23 |
  +---------------+