Help in reshaping

[Pulak Mishra]

Hi I have a dataset of industrial clusters which are segmented into districts, states and products (as shown in table.1 below). I want to reshape the data by states to show which prod has how many clusters in that particular state (as shown in table.2 below). I have assigned numbers to the observations where clusters are unique for each observation and the rest have repeated occurrences. I tried

reshape wide clus dist, i(st)

j(prod) but I am getting

variable prod is string; specify string option

error. Please help me.


Table.1

Cluster	clus	District	dist	State	st	Product	prod
Nellore Aerated Water	100001	Nellore	1	Andhra Pradesh	a	Aerated Water	1001
Hindupur Agarbatti	100002	Anantapur	2	Andhra Pradesh	a	Agarbatti	1002
Prakasam Agarbatti	100003	Prakasam	3	Andhra Pradesh	a	Agarbatti	1002
Kakopothar Incense Stick Making	100004	Tinsukia	4	Assam	b	Agarbatti	1002
Gaya Agarbatti	100005	Gaya	5	Bihar	c	Agarbatti	1002
Jahanabad Agarbatti	100006	Jahanabad	6	Bihar	c	Agarbatti	1002
Shikaripura Agri Seeds	100027	Shimoga	25	Karnataka	e	Agri Seeds	1003
Raigarh Agricultural Implements	100028	Raigarh	26	Chhattisgarh	l	Agricultural Implements	1004
Hubli Agricultural Implements	100067	Dharwad	59	Karnataka	e	Agricultural Implements	1005
Sirmour Agro Processing	100068	Sirmour	60	Himachal Pradesh	r	Agro Processing	1006
Kishtwar Agro Processing	100069	Kishtwar	61	Jammu & Kashmir	s	Agro Processing	1006
Atapur Utencils	100070	Keonjhar	62	Odisha	t	Aluminium Utensils	1007
Pratapgarh Amla	100071	Pratapgarh	63	Uttar Pradesh	j	Amla	1008
Vijayawada Auto Components	100072	Krishna	64	Andhra Pradesh	a	Auto Components	1009
Mayapuri Auto Components	100073	West Delhi	65	Delhi	u	Auto Components	1009

Table.2

State	Aerated Water	Agarbatti	Agri Seeds	Agricultural Implements	Agro Processing	Aluminium Utensils	Amla	Auto Components
Andhra Pradesh	1	2						1
Assam		1
Bihar		2
Karnataka			1	1
Chhattisgarh				1
Himachal Pradesh					1
Jammu & Kashmir					1
Odisha						1
Uttar Pradesh							1
Delhi								1

[Andrew Musau]

Please use dataex in the future to present data examples (see FAQ Advice #12 for details).

reshape wide clus dist, i(st)
j(prod) but I am getting
variable prod is string; specify string option

The error implies that you simply need to add the -string- option to your command. However, no usable data example, so no way to say that this will solve your issue.

reshape wide clus dist, i(st) j(prod) string

[Pulak Mishra]

Please use dataex in the future to present data examples (see FAQ Advice #12 for details).



The error implies that you simply need to add the -string- option to your command. However, no usable data example, so no way to say that this will solve your issue.

reshape wide clus dist, i(st) j(prod) string

Thanks but as you presumed, it didn't solve the purpose. I revised the data (table 1) and was able to get the results (table 2) in excel using COUNTIFS formula. How can I get this result in STATA?

Table 1
input str27 state str23 product
"Andaman and Nicobar Islands" ""
"Andhra Pradesh" "Aerated Water"
"Andhra Pradesh" "Agarbatti"
"Andhra Pradesh" "Agarbatti"
"Arunachal Pradesh" ""
"Assam" "Agarbatti"
"Assam" ""
"Bihar" "Agarbatti"
"Bihar" "Agarbatti"
"Chhattisgarh" "Agricultural Implements"
"Delhi" ""
"Goa" ""
"Gujarat" "Agricultural Implements"
"Gujarat" "Agricultural Implements"
"Gujarat" "Agricultural Implements"
"Gujarat" "Agarbatti"


Table 2
input str27 state str1(aeratedwater agarbatti agriseeds agriculturalimplements)
"Andaman and Nicobar Islands" "0" "0" "0" "0"
"Andhra Pradesh" "1" "2" "0" "0"
"Arunachal Pradesh" "0" "0" "0" "0"
"Assam" "0" "1" "0" "0"
"Bihar" "0" "2" "0" "0"
"Chhattisgarh" "0" "0" "0" "1"
"Delhi" "0" "0" "0" "0"
"Goa" "0" "0" "0" "0"
"Gujarat" "0" "1" "0" "3"

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str27 state str23 product
"Andaman and Nicobar Islands" ""                       
"Andhra Pradesh"              "Aerated Water"          
"Andhra Pradesh"              "Agarbatti"              
"Andhra Pradesh"              "Agarbatti"              
"Arunachal Pradesh"           ""                       
"Assam"                       "Agarbatti"              
"Assam"                       ""                       
"Bihar"                       "Agarbatti"              
"Bihar"                       "Agarbatti"              
"Chhattisgarh"                "Agricultural Implements"
"Delhi"                       ""                       
"Goa"                         ""                       
"Gujarat"                     "Agricultural Implements"
"Gujarat"                     "Agricultural Implements"
"Gujarat"                     "Agricultural Implements"
"Gujarat"                     "Agarbatti"              
end

contract state product, nomiss
replace product= strtoname(product)
reshape wide _freq, i(state) j(product) string
rename _freq* *

Res.:

. l

     +-------------------------------------------------+
     |          state   Aerate~r   Agarba~i   Agricu~s |
     |-------------------------------------------------|
  1. | Andhra Pradesh          1          2          . |
  2. |          Assam          .          1          . |
  3. |          Bihar          .          2          . |
  4. |   Chhattisgarh          .          .          1 |
  5. |        Gujarat          .          1          3 |
     +-------------------------------------------------+

[Pulak Mishra]

* Example generated by -dataex-. For more info, type help dataex
clear
input str27 state str23 product
"Andaman and Nicobar Islands" ""
"Andhra Pradesh" "Aerated Water"
"Andhra Pradesh" "Agarbatti"
"Andhra Pradesh" "Agarbatti"
"Arunachal Pradesh" ""
"Assam" "Agarbatti"
"Assam" ""
"Bihar" "Agarbatti"
"Bihar" "Agarbatti"
"Chhattisgarh" "Agricultural Implements"
"Delhi" ""
"Goa" ""
"Gujarat" "Agricultural Implements"
"Gujarat" "Agricultural Implements"
"Gujarat" "Agricultural Implements"
"Gujarat" "Agarbatti"
end

contract state product, nomiss
replace product= strtoname(product)
reshape wide _freq, i(state) j(product) string
rename _freq* *

Res.:

. l

+-------------------------------------------------+
| state Aerate~r Agarba~i Agricu~s |
|-------------------------------------------------|
1. | Andhra Pradesh 1 2 . |
2. | Assam . 1 . |
3. | Bihar . 2 . |
4. | Chhattisgarh . . 1 |
5. | Gujarat . 1 3 |
+-------------------------------------------------+

Thank you so much. It was quite helpful. What do I have to do if have to sort the data by two variables? Have a look at the below tables for the reference. Sorry for asking too many questions, I am quite new to STATA.

Data
input str27 state str11 district str23 product
"Andaman and Nicobar Islands" "Nicobar" "Aerated Water"
"Andaman and Nicobar Islands" "Nicobar" "Aerated Water"
"Andaman and Nicobar Islands" "Andaman" "Agarbatti"
"Andhra Pradesh" "Hyderabad" "Agarbatti"
"Andhra Pradesh" "Hyderabad" "Agricultural Implements"
"Andhra Pradesh" "Vizag" "Agricultural Implements"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Assam" "Guwahati" "Agarbatti"
"Assam" "Guwahati" "Agricultural Implements"
"Chhattisgarh" "Ranchi" "Aerated Water"
"Delhi" "South Delhi" "Agricultural Implements"
"Delhi" "North Delhi" "Agricultural Implements"
"Delhi" "North Delhi" "Agarbatti"
"Goa" "Panaji" "Aerated Water"


Result
input str27 state str11 district str1(aeratedwater agarbatti agriculturalimplements)
"Andaman and Nicobar Islands" "Nicobar" "2" "" ""
"Andaman and Nicobar Islands" "Andaman" "" "1" ""
"Andhra Pradesh" "Hyderabad" "" "1" "1"
"Andhra Pradesh" "Vizag" "" "3" "1"
"Assam" "Guwahati" "" "1" "1"
"Chhattisgarh" "Ranchi" "1" "" ""
"Delhi" "South Delhi" "" "" "2"
"Delhi" "North Delhi" "" "1" ""
"Goa" "Panaji" "1" "" ""

[Pulak Mishra]

Thank you so much. It was quite helpful. What do I have to do if have to sort the data by two variables? Have a look at the below tables for the reference. Sorry for asking too many questions, I am quite new to STATA.

Data
input str27 state str11 district str23 product
"Andaman and Nicobar Islands" "Nicobar" "Aerated Water"
"Andaman and Nicobar Islands" "Nicobar" "Aerated Water"
"Andaman and Nicobar Islands" "Andaman" "Agarbatti"
"Andhra Pradesh" "Hyderabad" "Agarbatti"
"Andhra Pradesh" "Hyderabad" "Agricultural Implements"
"Andhra Pradesh" "Vizag" "Agricultural Implements"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Andhra Pradesh" "Vizag" "Agarbatti"
"Assam" "Guwahati" "Agarbatti"
"Assam" "Guwahati" "Agricultural Implements"
"Chhattisgarh" "Ranchi" "Aerated Water"
"Delhi" "South Delhi" "Agricultural Implements"
"Delhi" "North Delhi" "Agricultural Implements"
"Delhi" "North Delhi" "Agarbatti"
"Goa" "Panaji" "Aerated Water"


Result
input str27 state str11 district str1(aeratedwater agarbatti agriculturalimplements)
"Andaman and Nicobar Islands" "Nicobar" "2" "" ""
"Andaman and Nicobar Islands" "Andaman" "" "1" ""
"Andhra Pradesh" "Hyderabad" "" "1" "1"
"Andhra Pradesh" "Vizag" "" "3" "1"
"Assam" "Guwahati" "" "1" "1"
"Chhattisgarh" "Ranchi" "1" "" ""
"Delhi" "South Delhi" "" "" "2"
"Delhi" "North Delhi" "" "1" ""
"Goa" "Panaji" "1" "" ""

It worked with the same method. I used the following commands. Thank you.

contract state district product, nomiss
replace product= strtoname(product)
reshape wide _freq, i(state district) j(product) string
rename _freq* *