How to Convert Data that is available in rows into Column for further Panel Data Analysis

[Hassan Raza]

Dears;

I have time series data of 220 companies in rows (Each row for each company for same date), please help me how to convert this data into single column with an extra panel variable.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 name float(nestlepakistan pakistantobacco engro karachielectricsupp) str6 luckycement float(marigas mcbbank)
"1/16/1997"    70  32.5 25.56 19.18 "NA" 7.17 5.91
"2/16/1997" 70.67 37.46 29.63 23.12 "NA" 9.33 7.47
"3/16/1997" 73.33 37.01 33.33 17.71 "NA" 8.17 6.88
"4/16/1997" 70.67 29.34 34.95 15.25 "NA" 7.83  6.2
"5/16/1997" 72.67 27.62 35.88 14.71 "NA" 7.25 6.33
end

[Rich Goldstein]

neither your question nor your data setup are clear to me; however, I suggest you examine:

help reshape
help datetime

it is very unlikely that you want to leave your dates as strings

[Hassan Raza]

Dear Sir Rich Goldstein;

I read all the help regarding reshape, the basic issue is that I have no ID series in column form as my ID series (Name of Companies) are in row form, while time series is in column form, I want to have my share prices of companies Panel forms instead of time series format. Please help

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(month_year gulistantextilemillssuspsusp2801 habibadmlimited halaenterprises habibsugar) str5 gulistanspngmillssuspsusp280116 float gulahmedtextilemills
444 24.98 .03   .7 2.52 "NA"    2.48
445 26.44 .03    2 2.52 "NA"    2.62
446 26.44 .03    2 3.02 "NA"    2.58
447 26.44 .03  2.5 2.82 "NA"    2.67
448 25.52 .03  2.5 2.64 "NA"    2.84
449 27.35 .03  2.5 2.76 "NA"    3.11
450 30.08 .03  2.5 2.68 "NA"    3.14
451 30.08 .03  2.5 2.58 "NA"    4.12
452 36.46 .03  2.5 2.03 "14.68" 4.12
453  39.2 .03 1.15 2.14 "22.2"  4.04
end
format %tm month_year

While Needed Format is like;

* Example generated by -dataex-. To install: ssc install dataex
clear
input float month_year byte id_firm float adjclose
456 1 1.178817
457 1 1.455098
458 1 2.161163
459 1 2.873362
460 1 2.841129
461 1 2.841129
462 1 3.637751
463 1  2.57866
464 1  2.55103
465 1 2.983878
end
format %tm month_year

[William Lisowski]

To begin, we need to correct two problems with your data.

First, you have one variable that should be numeric but has "NA" values so it is stored as string. Variables like that need to be converted to numeric values, with the "NA" replaced by a Stata missing value. You can accomplish that for the one variable in your example with

destring gulistanspngmillssuspsusp280116, replace force

or perhaps it would be easier to fix in the source of your data, replacing "NA" with "." before importing it into Stata.

Second, for our purposes your variable names are too long - we need them to be 30 characters or less for the code below to work. I've manually shortened them for my example.

With that done, here's my approach.

. * Example generated by -dataex-. To install: ssc install dataex
. clear

. input month_year gulistantextilemills habibadmlimited halaenterprises habibsugar gulahmedtexti
> lemills

     month_y~r  gulista~s  habibad~d  halaent~s  habibsu~r  gulahme~s
  1. 444 24.98 .03   .7 2.52     . 2.48
  2. 445 26.44 .03    2 2.52     . 2.62
  3. 446 26.44 .03    2 3.02     . 2.58
  4. 447 26.44 .03  2.5 2.82     . 2.67
  5. 448 25.52 .03  2.5 2.64     . 2.84
  6. 449 27.35 .03  2.5 2.76     . 3.11
  7. 450 30.08 .03  2.5 2.68     . 3.14
  8. 451 30.08 .03  2.5 2.58     . 4.12
  9. 452 36.46 .03  2.5 2.03 14.68 4.12
 10. 453  39.2 .03 1.15 2.14  22.2 4.04
 11. end

. format %tm month_year

. rename (gulistantextilemills-gulahmedtextilemills) (v_=)

. reshape long v_, i(month_year) j(firm) string
(note: j = gulahmedtextilemills gulistantextilemills habibadmlimited habibsugar halaenterprises)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       10   ->      50
Number of variables                   6   ->       3
j variable (5 values)                     ->   firm
xij variables:
v_gulahmedtextilemills v_gulistantextilemills ... v_halaenterprises->v_
-----------------------------------------------------------------------------

. rename v_ value

. list if month_year<=445, sepby(month_year)

     +-----------------------------------------+
     | month_~r                   firm   value |
     |-----------------------------------------|
  1. |   1997m1   gulahmedtextilemills       . |
  2. |   1997m1   gulistantextilemills   24.98 |
  3. |   1997m1        habibadmlimited     .03 |
  4. |   1997m1             habibsugar    2.52 |
  5. |   1997m1        halaenterprises      .7 |
     |-----------------------------------------|
  6. |   1997m2   gulahmedtextilemills       . |
  7. |   1997m2   gulistantextilemills   26.44 |
  8. |   1997m2        habibadmlimited     .03 |
  9. |   1997m2             habibsugar    2.52 |
 10. |   1997m2        halaenterprises       2 |
     +-----------------------------------------+

. encode firm, generate(id) label(id_name) 

. describe

Contains data
  obs:            50                          
 vars:             4                          
 size:         1,600                          
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
month_year      float   %tm                   
firm            str20   %20s                  
value           float   %9.0g                 
id              long    %20.0g     id_name    
------------------------------------------------------------------------------------------------
Sorted by: month_year  firm
     Note: Dataset has changed since last saved.

. label list id_name
id_name:
           1 gulahmedtextilemills
           2 gulistantextilemills
           3 habibadmlimited
           4 habibsugar
           5 halaenterprises

. sort id month_year

. list if id==4, clean nolabel

       month_~r         firm   value   id  
 31.     1997m1   habibsugar    2.52    4  
 32.     1997m2   habibsugar    2.52    4  
 33.     1997m3   habibsugar    3.02    4  
 34.     1997m4   habibsugar    2.82    4  
 35.     1997m5   habibsugar    2.64    4  
 36.     1997m6   habibsugar    2.76    4  
 37.     1997m7   habibsugar    2.68    4  
 38.     1997m8   habibsugar    2.58    4  
 39.     1997m9   habibsugar    2.03    4  
 40.    1997m10   habibsugar    2.14    4  

. drop firm

. list if id==4, clean

       month_~r   value           id  
 31.     1997m1    2.52   habibsugar  
 32.     1997m2    2.52   habibsugar  
 33.     1997m3    3.02   habibsugar  
 34.     1997m4    2.82   habibsugar  
 35.     1997m5    2.64   habibsugar  
 36.     1997m6    2.76   habibsugar  
 37.     1997m7    2.68   habibsugar  
 38.     1997m8    2.58   habibsugar  
 39.     1997m9    2.03   habibsugar  
 40.    1997m10    2.14   habibsugar  

.

[Hassan Raza]

Thanks a lot