reshape long-form to wide-form

[Jane Quan]

HI! I need to use the penal-data multinomial logit model in my analysis, and before I use the "xtmlogit" command, I should convert my long-form penal data to wide-form.
I thought I could do that with "reshape" command, but I still can't get the data transformation that I want.

following was my command:
" reshape wide stratumid_all clusterid_all HHnumberid_all finalwgt hv005 v005 gender healthcon v746 v035 farmsize age numbchildren landown edulevel religion curworking occutype beatingj1 beatingj2 beatingj3 beatingj4 beatingj5 decision1 decision2 decision4 decision5 v732 v739 v745a,i(country) j(year)"

I also attached 200 obs of my data sample below with "dataex" command: (well since the original variables are too many, so I only selected a few of them)

dataex country year stratumid_all clusterid_all HHnumberid_all finalwgt hv005 v005 gender healthcon age numbchildren edulevel religion curworking occutype beatingj1 beatingj2 decision1 decision2 in 1/500

1 2015 423 1 197718 1.203191 1203191 1086089 2 1 40 8 0 1 1 3 0 0 1 0
1 2015 423 1 205448 1.203191 1203191 1086089 2 0 25 4 0 1 1 3 0 0 0 1
1 2015 423 1 112887 1.203191 1203191 1031521 1 . 23 2 1 1 1 5 1 1 0 0
1 2015 423 1 95439 1.203191 1203191 1031521 1 . 15 0 1 1 1 3 0 0 . .
1 2015 423 1 190026 1.203191 1203191 1031521 1 . 34 0 1 1 1 3 0 0 . .
1 2015 423 1 8946 1.203191 1203191 1086089 2 0 40 6 0 1 1 3 0 0 1 0
1 2015 423 1 5 1.203191 1203191 1031521 1 . 39 9 1 1 1 3 0 0 0 0
1 2015 423 1 26195 1.203191 1203191 1086089 2 0 25 6 0 1 1 3 0 0 1 1
1 2015 423 1 112887 1.203191 1203191 1086089 2 1 21 2 0 1 1 3 0 0 0 1
1 2015 423 1 156174 1.203191 1203191 1086089 2 0 21 4 1 1 1 3 0 0 0 0
1 2015 423 1 181697 1.203191 1203191 1086089 2 0 29 6 0 1 1 3 0 0 . .
1 2015 423 1 8946 1.203191 1203191 1031521 1 . 43 12 1 1 1 3 1 . 0 1
1 2015 423 1 104195 1.203191 1203191 1086089 2 0 31 8 0 1 1 3 0 0 1 0
1 2015 423 1 5 1.203191 1203191 1031521 1 . 17 0 1 1 1 3 0 1 . .
1 2015 423 1 147402 1.203191 1203191 1086089 2 1 25 3 0 1 1 3 0 1 0 1
1 2015 423 1 5 1.203191 1203191 1086089 2 0 38 7 0 1 1 3 0 0 0 1
1 2015 423 1 212470 1.203191 1203191 1086089 2 0 20 1 1 1 1 3 0 0 0 0
1 2015 423 1 164692 1.203191 1203191 1086089 2 1 27 4 0 1 1 3 0 0 0 1
1 2015 423 1 26195 1.203191 1203191 1031521 1 . 30 6 1 1 1 3 0 0 0 0
1 2015 423 1 130159 1.203191 1203191 1086089 2 1 44 10 1 1 1 3 0 0 0 0
1 2015 423 1 8946 1.203191 1203191 1086089 2 1 16 1 0 1 1 3 0 0 . .
1 2015 423 1 17271 1.203191 1203191 1031521 1 . 42 12 0 1 1 3 0 0 0 1
1 2015 423 1 17271 1.203191 1203191 1086089 2 0 43 8 1 1 1 3 0 0 0 0
1 2015 423 1 17271 1.203191 1203191 1031521 1 . 19 0 1 1 1 3 0 0 . .
1 2015 423 1 43492 1.203191 1203191 1086089 2 0 26 4 0 1 1 3 0 0 . .
1 2015 423 1 138734 1.203191 1203191 1086089 2 0 33 3 0 1 1 3 0 0 . .
1 2015 423 1 86841 1.203191 1203191 1031521 1 . 21 1 1 1 1 3 1 1 . .
1 2015 423 1 69494 1.203191 1203191 1086089 2 0 18 3 0 1 1 3 0 0 . .
1 2015 423 1 60983 1.203191 1203191 1086089 2 0 46 9 0 1 1 3 1 1 0 0
1 2015 423 1 60983 1.203191 1203191 1031521 1 . 15 0 2 1 1 3 1 1 . .
1 2015 423 1 212470 1.203191 1203191 1031521 1 . 23 1 1 1 1 3 0 0 0 0
2 2010 573 2 26196 .74296 742960 730967 2 1 35 7 0 3 1 5 0 0 0 1
2 2010 573 2 164693 .74296 742960 730967 2 1 25 1 0 3 1 5 0 0 0 1
2 2010 573 2 52248 .74296 742960 730967 2 0 41 4 0 2 1 3 0 0 1 1
2 2010 573 2 121442 .74296 742960 736151 1 . 57 10 0 1 1 3 0 1 0 0
2 2010 573 2 95440 .74296 742960 730967 2 1 25 4 0 3 1 3 1 0 1 1
2 2010 573 2 121442 .74296 742960 736151 1 . 16 0 0 1 1 3 0 1 . .
2 2010 573 2 233945 .74296 742960 730967 2 1 28 3 0 3 1 5 0 0 0 1
2 2010 573 2 60984 .74296 742960 730967 2 0 15 0 1 3 1 3 0 1 . .
2 2010 573 2 78279 .74296 742960 730967 2 0 31 7 0 3 1 5 0 1 0 1
2 2010 573 2 52248 .74296 742960 736151 1 . 52 8 0 2 1 3 0 0 0 0
2 2010 573 2 17272 .74296 742960 736151 1 . 30 0 0 3 1 3 0 0 . .
2 2010 573 2 223791 .74296 742960 730967 2 1 18 0 0 3 1 5 1 0 1 0
2 2010 573 2 121442 .74296 742960 730967 2 0 33 5 0 1 1 5 1 1 0 0
2 2010 573 2 8947 .74296 742960 730967 2 0 46 9 0 3 1 5 1 1 0 0
2 2010 573 2 52248 .74296 742960 730967 2 0 42 7 0 2 1 2 1 1 1 1
2 2010 573 2 60984 .74296 742960 736151 1 . 51 9 0 3 1 3 0 0 0 0
2 2010 573 2 17272 .74296 742960 730967 2 0 47 5 0 3 1 3 1 1 1 1
2 2010 573 2 212471 .74296 742960 730967 2 1 33 4 0 3 1 3 0 1 0 1
2 2010 573 2 147403 .74296 742960 730967 2 1 29 3 0 3 1 5 0 0 0 1
2 2010 573 2 205449 .74296 742960 736151 1 . 26 1 2 3 1 3 0 0 0 0
2 2010 573 2 69495 .74296 742960 730967 2 0 38 1 0 3 1 5 1 1 0 1
2 2010 573 2 223791 .74296 742960 730967 2 1 43 6 0 3 1 2 1 1 . .
2 2010 573 2 34740 .74296 742960 730967 2 0 44 6 0 3 1 3 1 0 . .
2 2010 573 2 138735 .74296 742960 736151 1 . 48 8 0 3 1 3 0 1 0 0
2 2010 573 2 223791 .74296 742960 736151 1 . 21 0 2 3 1 3 0 0 0 1
2 2010 573 2 205449 .74296 742960 730967 2 1 23 2 2 1 1 1 0 0 1 0
2 2010 573 2 17272 .74296 742960 736151 1 . 56 7 0 3 1 3 0 0 0 0
2 2010 573 2 69495 .74296 742960 730967 2 0 48 3 0 3 1 5 0 0 0 1
2 2010 573 2 212471 .74296 742960 736151 1 . 53 10 0 3 1 3 0 1 0 0
2 2010 573 2 8947 .74296 742960 730967 2 0 17 0 0 1 1 5 1 1 0 0
2 2010 573 2 104196 .74296 742960 730967 2 0 17 0 2 1 0 0 0 0 . .
2 2010 573 2 164693 .74296 742960 730967 2 1 46 7 0 3 1 5 0 0 . .
2 2010 573 2 233945 .74296 742960 730967 2 1 25 2 1 1 1 2 0 0 0 1
2 2010 573 2 121442 .74296 742960 730967 2 0 48 5 0 1 1 2 0 0 0 1
2 2010 573 2 104196 .74296 742960 730967 2 1 38 9 0 1 1 5 0 0 0 0
2 2010 573 2 86842 .74296 742960 730967 2 0 25 5 0 2 1 2 1 1 1 1
2 2010 573 2 156175 .74296 742960 730967 2 1 27 3 1 1 1 5 0 0 0 1
2 2010 573 2 138735 .74296 742960 730967 2 1 40 8 0 3 1 5 0 0 1 1
3 2012 3 3 173261 1.188466 1188466 1384444 1 . 23 0 0 1 1 3 0 0 . .
3 2018 28 3 354683 1.129772 1129772 1134416 2 1 30 5 0 1 1 3 1 1 1 1
3 2012 3 3 197719 1.188466 1188466 1384444 1 . 39 0 2 1 1 5 0 0 . .
3 2018 28 3 352787 1.129772 1129772 1134416 2 1 48 10 0 1 1 2 0 0 1 1
3 2012 3 3 121443 1.188466 1188466 1279426 2 0 32 2 0 0 0 0 0 0 1 1
3 2018 28 3 344982 1.129772 1129772 1130086 1 . 29 5 2 1 1 3 0 0 0 0
3 2018 28 3 349186 1.129772 1129772 1134416 2 0 26 4 0 1 0 0 . 0 0 0
3 2018 28 3 312318 1.129772 1129772 1134416 2 0 17 0 0 0 1 2 1 1 . .
3 2012 3 3 181698 1.188466 1188466 1279426 2 0 22 4 1 2 1 2 0 0 0 1
3 2018 28 3 352787 1.129772 1129772 1130086 1 . 60 11 0 1 1 3 0 0 0 0
3 2018 28 3 260272 1.129772 1129772 1134416 2 0 46 2 0 1 1 2 0 0 0 0
3 2018 28 3 282368 1.129772 1129772 1134416 2 0 29 3 0 1 1 2 1 1 1 1
3 2018 28 3 324101 1.129772 1129772 1134416 2 1 17 1 0 1 1 2 0 0 1 1
3 2012 3 3 156176 1.188466 1188466 1279426 2 0 16 1 0 2 0 0 0 0 0 1
3 2012 3 3 43493 1.188466 1188466 1279426 2 0 28 5 0 2 0 0 0 0 1 1
3 2018 28 3 312318 1.129772 1129772 1134416 2 1 31 3 0 0 1 2 1 1 1 1
3 2012 3 3 78280 1.188466 1188466 1279426 2 0 30 5 0 2 1 3 0 0 0 1
3 2018 28 3 69496 1.129772 1129772 1134416 2 0 33 5 0 1 1 3 1 1 1 1
3 2018 28 3 337066 1.129772 1129772 1134416 2 1 25 2 0 1 0 0 0 0 1 1
3 2018 28 3 333767 1.129772 1129772 1134416 2 1 18 0 0 1 0 0 0 0 0 0
3 2018 28 3 121443 1.129772 1129772 1134416 2 1 39 6 0 1 1 2 1 1 1 1
3 2018 28 3 312318 1.129772 1129772 1134416 2 0 49 5 0 0 1 2 1 1 1 1
3 2012 3 3 173261 1.188466 1188466 1384444 1 . 15 0 2 1 1 0 0 0 . .
3 2012 3 3 173261 1.188466 1188466 1384444 1 . 60 0 0 1 1 3 0 1 0 0
3 2012 3 3 181698 1.188466 1188466 1279426 2 0 20 2 1 2 0 0 0 0 0 1
3 2018 28 3 354683 1.129772 1129772 1134416 2 0 28 3 0 1 1 3 1 1 1 1
3 2012 3 3 104197 1.188466 1188466 1279426 2 0 31 5 0 2 1 3 0 0 0 1
3 2018 28 3 312318 1.129772 1129772 1134416 2 1 46 7 0 0 1 3 1 1 1 1
3 2018 28 3 312318 1.129772 1129772 1130086 1 . 55 27 0 1 1 3 0 0 0 0
3 2018 28 3 324101 1.129772 1129772 1130086 1 . 48 13 0 1 1 3 0 0 0 0
3 2018 28 3 272608 1.129772 1129772 1134416 2 0 48 8 0 1 1 2 0 0 0 0

I hope my pooled data could be transformed as below: or you can refer to the first big table of next link: https://www.stata.com/new-in-stata/p...inomial-logit/

country

year

stratumid_all1

clusterid_all1

HHnumberid_all1

gender

other var

stratumid_all1

clusterid_all2

HHnumberid_all1

gender

gender

other var

stratumid_all1

clusterid_all2

HHnumberid_all2

gender

other var

1

2010

1

2015

2

2010

3

2010

Any guide or thought would be helpful!
Thank you：）

[Justin Niakamal]

I can't advise on xtmlogit as I don't have Stata 17. However, more often than not a long layout is needed for estimation. So your setup should work fine as is since your data is in long format, you'll just need to xtset your data -- xtset country year, and then run xtmlogit. You can see that in the example dataset used for xtmlogit a long layout is used.

[Jane Quan]

I can't advise on xtmlogit as I don't have Stata 17. However, more often than not a long layout is needed for estimation. So your setup should work fine as is since your data is in long format, you'll just need to xtset your data -- xtset country year, and then run xtmlogit. You can see that in the example dataset used for xtmlogit a long layout is used.

Hi Justin, thank you for your reply. but if I don't reshape my data, then when I set "xtset", it says "repeated time values within panel". So I can't use xtmlogit command.
and as you can see that my data is multilevel penal, so if the group(or id) is country, then I do need to reshape them.

[Rich Goldstein]

remember that inclusion of the "timevar" in the -xtset- command is optional - the panel var is required but not the time var; as far as I know, the only effect of not including a time var is that you are unable to use the automatic ability in Stata to get time-related variables such as lags and leads - if you don't plan on using those anyway, then use only a panel var

further, to sensibly do a reshape wide there needs to be an analysis related reasons (something is easier in wide format or, better, something can only be done with that switch); your reason is data related and you have not told us anything about why there are repeated time values (maybe they should be there but maybe they shouldn't); if they should be there, then the previous paragraph applies; if they should not be there, then we need more information to give advice on what is the best way to get rid of them (but -reshape wide- is unlikely to be the best way)