Reshape long

[Sartaj Hussain]

* Example generated by -dataex-. For more info, type help dataex
clear
input str54 company_name str10(ind_grp_code prod_grp prod_code nic_name nic_code v8 v9 v10 v11 v12)
"Company Name"                                  "01-04-2005" "01-05-2005" "01-06-2005" "01-07-2005" "01-08-2005" "01-09-2005" "01-10-2005" "01-11-2005" "01-12-2005" "01-01-2006"
""                                              "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"        
"3M India Ltd."                                 "547.7"      "702.1"      "632.75"     "794.2"      "801.1"      "876"        "730"        "777"        "852.6"      "920"       
"A D F Foods Ltd."                              "13.55"      "20.25"      "20.1"       "19.65"      "25.1"       "18"         "16.5"       "17.3"       "16.7"       "18.25"     
"A F Enterprises Ltd."                          ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A G I Greenpac Ltd."                           "50.14"      "64.5"       "60.38"      "61.86"      "73.06"      "91.52"      "88.18"      "89.54"      "88.05"      "112.2"     
"A I A Engineering Ltd."                        ""           ""           ""           ""           ""           ""           ""           ""           "107.75"     "123.94"    
"A I Champdany Inds. Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A K I India Ltd."                              ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A K Spintex Ltd."                              ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A M D Industries Ltd."                         ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A M I Organics Ltd."                           ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A N G Lifesciences India Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A P L Apollo Tubes Ltd."                       ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A P M Industries Ltd."                         "6.2"        "9.11"       "8.75"       "8.98"       "11.8"       "10.47"      "7.98"       "8.67"       "8.46"       "8.56"      
"A P T Packaging Ltd."                          ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A V T Natural Products Ltd."                   "4.36"       "5.86"       "4.18"       "4.6"        "5.05"       "4.58"       "5"          "6.03"       "6.86"       "5.9"       
"A Y M Syntex Ltd."                             "7"          "8.17"       "8.25"       "10.91"      "15.31"      "12.75"      "9.7"        "10.55"      "9.46"       "9.52"      
"Aanchal Ispat Ltd."                            ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aartech Solonics Ltd."                         ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aarti Drugs Ltd."                              "15.32"      "14.01"      "19.86"      "20.24"      "18.91"      "17.34"      "13.22"      "13.68"      "12.7"       "12.49"     
"Aarti Industries Ltd."                         "11.61"      "13.59"      "14.9"       "14.94"      "17.69"      "18.44"      "17.62"      "18.28"      "18.69"      "19.93"     
"Aarvee Denims & Exports Ltd."                  "75.15"      "107.45"     "118.55"     "130.3"      "158.8"      "133.2"      "127.65"     "131.1"      "100.95"     "126.3"     
"Abbott India Ltd."                             "654.35"     "637.7"      "601.6"      "662.65"     "660.1"      "654.25"     "649.2"      "729.85"     "707.05"     "681.35"    
"Acknit Industries Ltd."                        "44.15"      "51.4"       "50.7"       "49.9"       "72.65"      "64.2"       "62.4"       "67.45"      "70.2"       "68"        
"Action Construction Equipment Ltd."            ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Active Clothing Co. Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Adani Wilmar Ltd."                             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Adarsh Plant Protect Ltd."                     "3.7"        "4.49"       "4"          "4.6"        "8.24"       "6.24"       "5.22"       "4.63"       "4.4"        "4.4"       
"Adeshwar Meditex Ltd."                         ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aditya Forge Ltd."                             "6.15"       "9.5"        "7.85"       "7.25"       "15.94"      "23.55"      "19"         "12.15"      "8.96"       "8.34"      
"Aditya Ispat Ltd."                             "8"          "7.51"       "6.74"       "7.45"       "10.65"      "7.99"       "6.19"       "7"          "4.72"       "5"         
"Aditya Spinners Ltd."                          ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Ador Fontech Ltd."                             "6.58"       "9.2"        "8.85"       "8.7"        "10.62"      "8.49"       "8.35"       "10.63"      "10.05"      "9.8"       
"Ador Multiproducts Ltd."                       "7.2"        "10.4"       "16.32"      "18.65"      "18.7"       "12.7"       "11.97"      "12.8"       "9.69"       "9.91"      
"Ador Welding Ltd."                             "136.3"      "167.3"      "185.7"      "235.2"      "275.75"     "259.1"      "270.35"     "385.75"     "389.35"     "377.6"     
"Advance Petrochemicals Ltd."                   ""           ""           ""           "22.64"      "26"         ""           ""           ""           ""           ""          
"Advance Syntex Ltd."                           ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Advanced Enzyme Technologies Ltd."             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Advik Laboratories Ltd."                       "8.87"       "13.41"      "14.11"      "13.85"      "15.42"      "10.85"      "9.74"       "8.9"        "8.3"        "7.9"       
"Aether Industries Ltd."                        ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Affordable Robotic & Automation Ltd."          ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Agarwal Industrial Corpn. Ltd."                ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Agro Tech Foods Ltd."                          "72.65"      "85.6"       "76.55"      "86.25"      "100.6"      "103.9"      "98.7"       "124.05"     "125.1"      "119.8"     
"Aikyam Intellectual Property Consultancy Ltd." ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aimco Pesticides Ltd."                         "18.35"      "19.85"      "18.8"       "19.9"       "23.5"       "26.35"      "27.15"      "26.9"       "33.45"      "32.05"     
"Ajanta Pharma Ltd."                            "5.02"       "7.22"       "7.77"       "7.9"        "8.25"       "7.46"       "5.82"       "7.42"       "6.96"       "7.51"      
"Ajanta Soya Ltd."                              "1.86"       "2.22"       "1.95"       "2.3"        "2.74"       "2.04"       "1.34"       "1.42"       "1.19"       "1.3"       
"Akar Auto Inds. Ltd."                          "5.63"       "7.99"       "8"          "9.75"       "15.07"      "17.8"       "13.73"      "16.57"      "18.52"      "21.95"     
"Aksh Optifibre Ltd."                           "46.95"      "55.95"      "57.55"      "60.35"      "69.75"      "61.35"      "54.85"      "70.75"      "73.2"       "72.7"      
"Akshar Spintex Ltd."                           ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aksharchem (India) Ltd."                       "16"         "18.1"       "23.15"      "22.6"       "26.7"       "21.4"       "16.15"      "20.1"       "17.7"       "16.9"      
"Akzo Nobel India Ltd."                         "227.2"      "257.6"      "264.15"     "310.05"     "332.15"     "333.7"      "317"        "315"        "332.35"     "369"       
"Albert David Ltd."                             "80.15"      "86.45"      "87"         "91.6"       "139.65"     "116.35"     "95.4"       "114.2"      "127.9"      "110.35"    
"Alchemist Corporation Ltd."                    ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alembic Pharmaceuticals Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alfa Ica (India) Ltd."                         "9.4"        "10"         "9"          "15.01"      "16.6"       "13.6"       "9.85"       "9.72"       "8.5"        "8.5"       
"Alfa Transformers Ltd."                        "20.5"       "30"         "26.45"      "28.45"      "34.85"      "36.85"      "35.1"       "38.2"       "57.9"       "58.65"     
"Alicon Castalloy Ltd."                         "81.13"      "121.4"      "115.6"      "125.58"     "154.15"     "133.7"      "127.83"     "140"        "158.05"     "145.35"    
"Alka India Ltd."                               "1.04"       "1.12"       "1.18"       "1.2"        "2.88"       "1.47"       "1.16"       "1.07"       "0.97"       "0.91"      
"Alkali Metals Ltd."                            ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alkem Laboratories Ltd."                       ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alkosign Ltd."                                 ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alkyl Amines Chemicals Ltd."                   "5.8"        "7.28"       "7.59"       "8.35"       "10.15"      "9.15"       "8.04"       "8.22"       "8.8"        "9.52"      
"Alliance Integrated Metaliks Ltd."             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alok Industries Ltd."                          "54.88"      "61"         "56.24"      "57.42"      "74.15"      "70.66"      "64.08"      "67.03"      "61.91"      "68.93"     
"Alpa Laboratories Ltd."                        ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alpha Hi-Tech Fuel Ltd."                       "11.71"      "15.15"      "14.36"      "15.76"      "25.4"       "21.75"      "14.5"       "14.53"      "13.22"      "10.42"     
"Alphalogic Industries Ltd."                    ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Alps Industries Ltd."                          "44.88"      "59.17"      "64.7"       "70.8"       "82.85"      "69.75"      "59.63"      "56.27"      "64.55"      "74.03"     
"Alufluoride Ltd."                              "14"         "14.2"       "13.85"      "23.23"      "29.25"      "20.8"       "17.95"      "16.5"       "14.85"      "13.71"     
"Amal Ltd."                                     "3.57"       "4.9"        "5.66"       "8.68"       "8.9"        "7.39"       "5.15"       "4.9"        "4.14"       "4.1"       
"Amara Raja Batteries Ltd."                     "7.13"       "7.64"       "9.67"       "9.67"       "10.37"      "10.33"      "9.53"       "10.39"      "12.87"      "15.65"     
"Amarjothi Spinning Mills Ltd."                 "96.9"       "118.9"      "104.7"      "101.95"     "95.35"      "83.95"      "68.6"       "69"         "68.5"       "64.55"     
"Ambar Protein Inds. Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Amber Enterprises India Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Ambika Cotton Mills Ltd."                      "199.1"      "275.8"      "250.65"     "278.3"      "308.35"     "289.75"     "261.4"      "270.6"      "261.55"     "287.05"    
"Ambition Mica Ltd."                            ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Ambo Agritec Ltd."                             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Amco India Ltd."                               "14"         "17.69"      "21.4"       "31.7"       "31.95"      "24.75"      "24.05"      "26.65"      "23.2"       "30"        
"Amin Tannery Ltd."                             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Amines & Plasticizers Ltd."                    "3.6"        "3.55"       "3.46"       "4.41"       "4.06"       "3.3"        "2.6"        "2.7"        "4.2"        "3.7"       
"Amrutanjan Health Care Ltd."                   "11.25"      "11.74"      "12.6"       "16.09"      "18.93"      "19.8"       "16.19"      "16.62"      "19.93"      "20.12"     
"Andhra Paper Ltd."                             "86.26"      "93.05"      "89.67"      "92.35"      "105.74"     "95.33"      "91.16"      "93.58"      "102.8"      "101.57"    
"Andhra Petrochemicals Ltd."                    "15"         "15.19"      "14.15"      "15.44"      "19.15"      "15.45"      "12.45"      "14.24"      "13.7"       "13.66"     
"Andhra Pradesh Tanneries Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Andhra Sugars Ltd."                            ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Andrew Yule & Co. Ltd."                        "22.45"      "29.05"      "31.85"      "34.35"      "37.95"      "30.9"       "22.7"       "28"         "28.1"       "28"        
"Angel Fibers Ltd."                             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Anjani Foods Ltd."                             ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Anjani Portland Cement Ltd."                   "9.02"       "9.03"       "9.42"       "10.34"      "13.54"      "10.21"      "8.77"       "8.4"        "8.28"       "7.98"      
"Anjani Synthetics Ltd."                        "6.47"       "12"         "11"         "9.8"        "13.23"      "11.4"       "9.5"        "10.95"      "12.55"      "12.2"      
"Ankit Metal & Power Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Anuh Pharma Ltd."                              "13.75"      "14.24"      "17.64"      "17.27"      "20.31"      "18.37"      "16.75"      "19.68"      "18.57"      "25.89"     
"Anup Engineering Ltd."                         ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Anupam Rasayan India Ltd."                     ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Anuroop Packaging Ltd."                        ""           ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Apar Industries Ltd."                          "88.13"      "104.32"     "111.94"     "125.63"     "146.4"      "169.01"     "161.59"     "172.46"     "163.16"     "150.15"    
"Apcotex Industries Ltd."                       "4.64"       "5.55"       "6.71"       "6.61"       "7.29"       "6.89"       "5.89"       "5.78"       "6.03"       "6"         
"Apex Buildsys Ltd."                            "7"          "8.8"        "8.4"        "8.89"       "24.14"      "17.7"       "18.05"      "16.3"       "17"         "39.05"     
end

.
On the above data example, I use the below appended code to reshape long. It needs to be debugged as there is error in running the code:

rename v1 company_name
rename v2 ind_grp
rename v3 ind_grp_code
rename v4 prod_grp
rename v5 prod_code
rename v6 nic_name
rename v7 nic_code
ds company_name ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, not
local headings `r(varlist)'
local newnames
foreach h of local headings {
    local date = (daily(`h'[1], "DMY"))
    local prefix = `h'[2]
    display `"`prefix'"', `date'
    local newnames `newnames' `prefix'`date'
}
rename (`headings') (`newnames')
drop in 1/2
reshape long pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, i(company_name) j(date)
format date %td
destring pr nd_grp ind_grp_code prod_grp prod_code nic_name nic_code, replace
format pr nd_grp ind_grp_code prod_grp prod_code nic_name nic_code %3.2f

.

[Clyde Schechter]

First, the -rename- commands for v1 through v7 must go, because those variables do not exist. Moreover, if they did, there would still be a problem because the variables company_name throug nic_code already do exist, so you couldn't rename v1 through v7 to these. (I'm guessing that actually the -dataex- you show was run after that series of -rename-s.)

The problem with the -reshape- command is that you are including in the varlist variables that do not end in dates: these cannot be -reshape-d. I imagine that you did that because you want them to be carried along into the newly created observations. But that "comes for free" when you just -reshape- the variables that do end in dates. Finallyl, the -destring- and -format- command at the end both refer to a non-existent variable nd_grp. I presume this is a typo for ind_grp.

So I think the following does what you want:

ds company_name ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, not
local headings `r(varlist)'
local newnames
foreach h of local headings {
    local date = (daily(`h'[1], "DMY"))
    local prefix = `h'[2]
    display `"`prefix'"', `date'
    local newnames `newnames' `prefix'`date'
}
rename (`headings') (`newnames')
drop in 1/2
reshape long pr, i(company_name) j(date) // N.B.  NO OTHER VARIABLES NEED, NOR CAN, BE LISTED HERE.
format date %td
destring pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, replace
format pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code %3.2f

[Sartaj Hussain]

The new code #2 is not correctly reshaping the data. For example, ind_grp and prod_grp are string variables. The will have repeated values for each section/company in the panel. So is the case with ind_grp_code, prod_code, nic_name, nic_code, all these will have repeated values. The unique values are for only pr.

[Clyde Schechter]

But that is exactly what the code in #2 does.

. list in 1/10, abbrev(12) noobs clean

        company_name        date   ind_grp_code   prod_grp   prod_code   nic_name   nic_code       pr  
       3M India Ltd.   01sep2005         547.70     702.10      632.75     794.20     801.10   876.00  
       3M India Ltd.   01oct2005         547.70     702.10      632.75     794.20     801.10   730.00  
       3M India Ltd.   01nov2005         547.70     702.10      632.75     794.20     801.10   777.00  
       3M India Ltd.   01dec2005         547.70     702.10      632.75     794.20     801.10   852.60  
       3M India Ltd.   01jan2006         547.70     702.10      632.75     794.20     801.10   920.00  
    A D F Foods Ltd.   01sep2005          13.55      20.25       20.10      19.65      25.10    18.00  
    A D F Foods Ltd.   01oct2005          13.55      20.25       20.10      19.65      25.10    16.50  
    A D F Foods Ltd.   01nov2005          13.55      20.25       20.10      19.65      25.10    17.30  
    A D F Foods Ltd.   01dec2005          13.55      20.25       20.10      19.65      25.10    16.70  
    A D F Foods Ltd.   01jan2006          13.55      20.25       20.10      19.65      25.10    18.25

The variables ind_grp_code, prod_grp, prod_code, nic_name, and nic_code repeat within company name, but each observation of pr has a different value.

So I don't see what the problem is.

[Sartaj Hussain]

ok. I changed the code slightly to make it compatible with the actual data set:

rename v1 company_name
rename v207 ind_grp
rename v208 ind_grp_code
rename v209 prod_grp
rename v210 prod_code
rename v211 nic_name
rename v212 nic_code

ds company_name ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, not
local headings `r(varlist)'
local newnames
foreach h of local headings {
    local date = (daily(`h'[1], "DMY"))
    local prefix = `h'[2]
    display `"`prefix'"', `date'
    local newnames `newnames' `prefix'`date'
}
rename (`headings') (`newnames')
drop in 1/2
reshape long pr, i(company_name) j(date) // N.B.  NO OTHER VARIABLES NEED, NOR CAN, BE LISTED HERE.
format date %td
destring pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code, replace
format pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code %3.2f

But after executing the following error is encountered:

format pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code %3.2f
varlist must contain all numeric or all string variables
r(109);

Kindly debug the issue.

[Clyde Schechter]

Well, the problem is that one or more of the variables pr ind_grp ind_grp_code prod_grp prod_code nic_name nic_code is not numeric when you reach that format command. As you changed the code, it is no longer compatible with the posted example in #1, so I cannot directly find out which it is. But you will be able to see it easily: look at the output that followed the -destring- command. There will be one or more variables for which Stata gave you the message:

variable_name: contains nonnumeric characters; no replace

That or those variables are not numeric and you must remove them from the -format- command. Of course, if they are supposed to be numeric variables, then there is a problem with the data for that variable which you will need to fix. To find the offending observations, run:

browse if missing(real(variable_name))

Then review and fix the data management up to this point that led to the inclusion of non-numeric material in those observations of the variable(s) in question.

[Sartaj Hussain]

Well, let me tell you, ind_grp, prod_grp and nic_name are non numeric.

[Clyde Schechter]

So just remove them from the -format- command and you'll be fine.

[Sartaj Hussain]

The problem is that in actual csv delimited file these three: ind_grp_code, prod_code and nic_code are numeric values. While being imported, stata shows them up as string. How can i retain their numeric property or else change in stata from string to numeric for them.

[Clyde Schechter]

So, I'm guessing from what you showed in the example in #1 that the reason that these variables are being imported as string is because of the string content in the second row of the .csv file. If I have that right, then after the -drop in 1/2- command that you already have, you should be able to -destring- them with

destring ind_grp_code prod_code nic_code, replace

Now if there is, for some reason, other non-numeric material in these variables, -destring- will not make the conversion and will give you a message saying that. At that point you have to identify the values that are causing problems and somehow fix it. The code shown in #6 will do that. How to go about fixing it depends on what you find there, so I can't say anything more about it. Common sources of this kind of problem are missing values entered in the csv file as "N/A" or "-" or " ". Another way this can happen is if somewhere in the middle or end of the csv file there are rows of non-numeric material. For example, I have encountered such files where there is a whole line of dashes in the middle of the data, or there is a "summary row" at the bottom that is preceded by a row with words like "Mean" "Sum" "Standard Deviation" or the like. Sometimes there are just typos in the numeric values like the upper case symbol instead of the lower case digit on the same key, or somebody puts in two decimal points instead of just one. You just have to see what comes up and fix whatever it is.