Reshape or Stack

[Co Ar]

Dear users,
I have the data below. It is already in long format and the ID variable named `survey_id` is already duplicated in some households. My aim is to merge group 1 with group2 so that group 2 goes below group 1 and I will have just one variable called group.
I have tried to reshape long but I get an error of unique ID. I tried stack and it creates something a bit weird
What I need is as on this table below.

From this:

survey_id group1 group2
1 milk
1 grain pulse
1 grain pulse
2 milk
2 milk pulse

To This:

survey_id	group
1	milk
1	grain
1	pulse
1	grain
1	pulse
2	milk
2	milk
2	pulse

* Example generated by -dataex-. For more info, type help dataex
clear
input str21 survey_id str44 group1 str32 group2
"0111333496-261023" "Others"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Others"
"0111333496-261023" "Grains, white roots and tubers and plantains"
"0111333496-261023" "Others"
"0111333496-261023" "Others"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Dark green leafy vegetables"
"0111333496-261023" "Grains, white roots and tubers and plantains"
"0111333496-261023" "Others"
"0111333496-261023" "Others"
"0111333496-261023" "Milk and Dairy Products"
"0112183531-091123" "Dark green leafy vegetables"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Milk and Dairy Products"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Others"
"0112183531-311023" "Dark green leafy vegetables"
"0112183531-311023" "Grains, white roots and tubers and plantains"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Dark green leafy vegetables"
"0112183531-311023" "Grains, white roots and tubers and plantains"
"0112183531-311023" "Others"
"0112183531-311023" "Others"
"0112183531-311023" "Milk and Dairy Products"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Grains, white roots and tubers and plantains"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Dark green leafy vegetables"
"0112823730-111123" "Grains, white roots and tubers and plantains"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Milk and Dairy Products"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Grains, white roots and tubers and plantains"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Grains, white roots and tubers and plantains"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Other vegetables"
"0113375472-101123" "Other vegetables"
"0113375472-101123" "Others"
"0113375472-101123" "Dark green leafy vegetables"
"0113375472-101123" "Grains, white roots and tubers and plantains"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Milk and Dairy Products"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Dark green leafy vegetables"
"0113737613-131123" "Grains, white roots and tubers and plantains"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Grains, white roots and tubers and plantains"
"0113737613-261023" "Others"
end

[Nick Cox]

Your data example doesn't match your question. Your question implies that you have two variables indicating crops, but the data example has one such variable.

Similarly, the dataex code is inconsistent, implying that it is code to read in three variables, but only two are supplied.

stack is, I agree, not likely to be helpful here. reshape will be. We can't comment with total confidence on exactly what is wrong with your reshape code, because you don't show it. But survey_id clearly does not identify individual observations.

I am going to guess that commas separate, while "and" binds.

This is my guess at what you need. I hacked at your code so the data example would run.

The point of the tabulate is only to show compactly what the results are.

* Example generated by -dataex-. For more info, type help dataex
clear
input str21 survey_id str44 group1  
"0111333496-261023" "Others"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Others"
"0111333496-261023" "Grains, white roots and tubers and plantains"
"0111333496-261023" "Others"
"0111333496-261023" "Others"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Other vegetables"
"0111333496-261023" "Dark green leafy vegetables"
"0111333496-261023" "Grains, white roots and tubers and plantains"
"0111333496-261023" "Others"
"0111333496-261023" "Others"
"0111333496-261023" "Milk and Dairy Products"
"0112183531-091123" "Dark green leafy vegetables"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Other vegetables"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Grains, white roots and tubers and plantains"
"0112183531-091123" "Others"
"0112183531-091123" "Others"
"0112183531-091123" "Milk and Dairy Products"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Others"
"0112183531-311023" "Dark green leafy vegetables"
"0112183531-311023" "Grains, white roots and tubers and plantains"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Other vegetables"
"0112183531-311023" "Others"
"0112183531-311023" "Dark green leafy vegetables"
"0112183531-311023" "Grains, white roots and tubers and plantains"
"0112183531-311023" "Others"
"0112183531-311023" "Others"
"0112183531-311023" "Milk and Dairy Products"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Grains, white roots and tubers and plantains"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Other vegetables"
"0112823730-111123" "Dark green leafy vegetables"
"0112823730-111123" "Grains, white roots and tubers and plantains"
"0112823730-111123" "Others"
"0112823730-111123" "Others"
"0112823730-111123" "Milk and Dairy Products"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Grains, white roots and tubers and plantains"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Other vegetables"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-071123" "Grains, white roots and tubers and plantains"
"0113375472-071123" "Others"
"0113375472-071123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Other vegetables"
"0113375472-101123" "Other vegetables"
"0113375472-101123" "Others"
"0113375472-101123" "Dark green leafy vegetables"
"0113375472-101123" "Grains, white roots and tubers and plantains"
"0113375472-101123" "Others"
"0113375472-101123" "Others"
"0113375472-101123" "Milk and Dairy Products"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Dark green leafy vegetables"
"0113737613-131123" "Grains, white roots and tubers and plantains"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Others"
"0113737613-131123" "Grains, white roots and tubers and plantains"
"0113737613-261023" "Others"
end

gen long id = _n
split group1, parse(",")
drop group1
reshape long group1, i(id) j(which)
drop id which

tab group1 survey_id




                      |                             survey_id
               group1 | 0111333..  0112183..  0112183..  0112823..  0113375..  0113375.. |     Total
----------------------+------------------------------------------------------------------+----------
 white roots and tu.. |         2          3          2          2          2          1 |        14
Dark green leafy ve.. |         1          1          2          1          0          1 |         7
               Grains |         2          3          2          2          2          1 |        14
Milk and Dairy Prod.. |         1          1          1          1          0          1 |         5
     Other vegetables |         4          4          4          5          6          2 |        25
               Others |         6          6          6          8         10          6 |        49
----------------------+------------------------------------------------------------------+----------
                Total |        16         18         17         19         20         12 |       114


                      |       survey_id
               group1 | 0113737..  0113737.. |     Total
----------------------+----------------------+----------
 white roots and tu.. |         2          0 |        14
Dark green leafy ve.. |         1          0 |         7
               Grains |         2          0 |        14
Milk and Dairy Prod.. |         0          0 |         5
     Other vegetables |         0          0 |        25
               Others |         6          1 |        49
----------------------+----------------------+----------
                Total |        11          1 |       114

[Co Ar]

Thank you so much Nick Cox . Sorry for the inconsistencies. This is helpful