CODEWITHSUNDEEP
stata
coinbase_wells
coinbase_wells

Reputation: 57

Reshaping data from wide to long in stata

I have a dataset that looks like the following:

FIPS Year jan feb mar apr may jun jul aug sep oct nov dec
1001 2000 10  20  30  40  50  60  30  30  20  30  40  50
1001 2001 10  20  30  40  50  60  30  30  20  30  40  50 
1003 2000 10  20  30  40  50  60  30  30  20  30  40  50
1003 2001 10  20  30  40  50  60  30  30  20  30  40  50

Previously, the data had FIPS in one column and then month-year in each other column. I reshaped it into the current format using reshape long. I am trying to reshape the data so that there is both a year and month column so that each row is a year-month combination for a specific FIPS code. I used the following code:

foreach var in jan feb mar apr may jun jul aug sep oct dec nov {
    rename `var' v_`var'

reshape long v_, i(fips year) j(time)

However, I received the following error:

variable time contains all missing values

Then, I tried:

variable year already exists

and got the error:

variable year already exists
    Data are already long.

How I can reuse the reshape long code to get it into the desired format?

Upvotes: 0

Views: 422

Answers (2)

TheIceBear
TheIceBear

Reputation: 3255

You need to indicate that the values in j (jan, feb, etc.) are expected to be strings. Like this:

reshape long v_, i(fips year) j(time) string

Upvotes: 0

Nick Cox
Nick Cox

Reputation: 37183

Your data layout looks like wide layout to me.

The immediate first problem with your syntax is not specifying the string option of reshape as the suffixes jan ... dec are surely not numeric. But it's best to get fundamentals right as soon as possible as you will need the months to be numeric values 1 to 12 to do anything useful easily.


* Example generated by -dataex-. 
clear
input int(FIPS Year) byte(jan feb mar apr may jun jul aug sep oct nov dec)
1001 2000 10 20 30 40 50 60 30 30 20 30 40 50
1001 2001 10 20 30 40 50 60 30 30 20 30 40 50
1003 2000 10 20 30 40 50 60 30 30 20 30 40 50
1003 2001 10 20 30 40 50 60 30 30 20 30 40 50
end

rename (jan-dec) y#, addnumber 

reshape long y, i(FIPS Year) j(Month)

gen mdate = ym(Year, Month)
format mdate %tm 

list in 1/24, sep(12) 

    +------------------------------------+
     | FIPS   Year   Month    y     mdate |
     |------------------------------------|
  1. | 1001   2000       1   10    2000m1 |
  2. | 1001   2000       2   20    2000m2 |
  3. | 1001   2000       3   30    2000m3 |
  4. | 1001   2000       4   40    2000m4 |
  5. | 1001   2000       5   50    2000m5 |
  6. | 1001   2000       6   60    2000m6 |
  7. | 1001   2000       7   30    2000m7 |
  8. | 1001   2000       8   30    2000m8 |
  9. | 1001   2000       9   20    2000m9 |
 10. | 1001   2000      10   30   2000m10 |
 11. | 1001   2000      11   40   2000m11 |
 12. | 1001   2000      12   50   2000m12 |
     |------------------------------------|
 13. | 1001   2001       1   10    2001m1 |
 14. | 1001   2001       2   20    2001m2 |
 15. | 1001   2001       3   30    2001m3 |
 16. | 1001   2001       4   40    2001m4 |
 17. | 1001   2001       5   50    2001m5 |
 18. | 1001   2001       6   60    2001m6 |
 19. | 1001   2001       7   30    2001m7 |
 20. | 1001   2001       8   30    2001m8 |
 21. | 1001   2001       9   20    2001m9 |
 22. | 1001   2001      10   30   2001m10 |
 23. | 1001   2001      11   40   2001m11 |
 24. | 1001   2001      12   50   2001m12 |
     +------------------------------------+

(Please use dataex in Stata to give examples. This is explained in the stata tag wiki.)

Upvotes: 1

Related Questions