Panel data format: varlist as IDs

[Julien Boudreau]

Hello Statalisters,

I have the following daily data shape:
date day city_1_price_A ... city_70_price_A city_1_price_B ... city_70_price_B Crude1 Crude2 avg_price_A
04jun2007 monday 115.4 ... 115.4 114.4 ... 113.9 44.05 47.20 112.51
05jun2007 tuesday 115.4 ... 115.4 114.4 ... 113.4 43.90 47.73 112.02
06jun2007 wednesday 115.4 ... 115.4 114.4 ... 113.4 43.89 47.57 111.69

where the variables city_[i]_price_A and city_[i]_price_B correspond to the average daily gas price before and after taxes and margins for the city i; Crude[i] is the daily price of crude i; and where avg_price_A is the daily average of city_1_price_A through city_70_price_A.

I am trying to reshape my data so it becomes compatible with the command xtset. I tried using reshape long, but it does not work as I do not have a common price_A variable for all cities, but instead a price variable for each city. In the same vein, I do not have an ID variable specific to each city. Could any of you give me an advice?

Thank you!

Julien

[William Lisowski]

Welcome to Statalist.

I simplified your data to 2 cities to provide this example code that I think does what you want.

cls
clear
* Example generated by -dataex-. To install: ssc install dataex
clear
input float date str12 day float(city_1_price_A city_2_price_A city_1_price_B city_2_price_B Crude1 Crude2 avg_price_A)
17321 "monday"    115.4 115.4 114.4 113.9 44.05  47.2 112.51
17322 "tuesday"   115.4 115.4 114.4 113.4  43.9 47.73 112.02
17323 "wednesday" 115.4 115.4 114.4 113.4 43.89 47.57 111.69
end
format %td date

rename (city_#_price_?) (price_?[2]#[1])
describe
generate id = _n
reshape long price_A price_B Crude, i(id) j(city)
drop id
sort city date
list, noobs sepby(city) abbreviate(12)
xtset city date

. rename (city_#_price_?) (price_?[2]#[1])

. describe

Contains data
  obs:             3                          
 vars:             9                          
 size:           132                          
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
date            float   %td                   
day             str12   %12s                  
price_A1        float   %9.0g                 
price_A2        float   %9.0g                 
price_B1        float   %9.0g                 
price_B2        float   %9.0g                 
Crude1          float   %9.0g                 
Crude2          float   %9.0g                 
avg_price_A     float   %9.0g                 
------------------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

. generate id = _n

. reshape long price_A price_B Crude, i(id) j(city)
(note: j = 1 2)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                        3   ->       6
Number of variables                  10   ->       8
j variable (2 values)                     ->   city
xij variables:
                      price_A1 price_A2   ->   price_A
                      price_B1 price_B2   ->   price_B
                          Crude1 Crude2   ->   Crude
-----------------------------------------------------------------------------

. drop id

. sort city date

. list, noobs sepby(city) abbreviate(12)

  +------------------------------------------------------------------------+
  | city        date         day   price_A   price_B   Crude   avg_price_A |
  |------------------------------------------------------------------------|
  |    1   04jun2007      monday     115.4     114.4   44.05        112.51 |
  |    1   05jun2007     tuesday     115.4     114.4    43.9        112.02 |
  |    1   06jun2007   wednesday     115.4     114.4   43.89        111.69 |
  |------------------------------------------------------------------------|
  |    2   04jun2007      monday     115.4     113.9    47.2        112.51 |
  |    2   05jun2007     tuesday     115.4     113.4   47.73        112.02 |
  |    2   06jun2007   wednesday     115.4     113.4   47.57        111.69 |
  +------------------------------------------------------------------------+

. xtset city date
       panel variable:  city (strongly balanced)
        time variable:  date, 04jun2007 to 06jun2007
                delta:  1 day