reshaping OECD panel data with not unique within i(var1 var2) problem

[Enzo Wu]

Hi, Stata masters. I have a problem with reshaping the common panel data which shows " with not unique within i(var1 var2) problem" on the output screen.

I know there are a lot of solutions on the internet; however, none of them are helpful.

Here's my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(countryid year) float fdistock str7 fdistock_measure str3 fdistock_subject
1 2009  2124.148 "MLN_USD" "CHN"
1 2010  6829.962 "MLN_USD" "CHN"
1 2011   6524.39 "MLN_USD" "CHN"
1 2012  8969.823 "MLN_USD" "CHN"
1 2013         . "MLN_USD" "CHN"
1 2014 10307.453 "MLN_USD" "CHN"
1 2015 10150.508 "MLN_USD" "CHN"
1 2016  9672.938 "MLN_USD" "CHN"
1 2017  10492.16 "MLN_USD" "CHN"
1 2018  9812.253 "MLN_USD" "CHN"
1 2019 10867.372 "MLN_USD" "CHN"
1 2009  89786.51 "MLN_USD" "USA"
1 2010  97319.85 "MLN_USD" "USA"
1 2011  101819.1 "MLN_USD" "USA"
1 2012 109685.74 "MLN_USD" "USA"
1 2013  110407.8 "MLN_USD" "USA"
1 2014  109386.8 "MLN_USD" "USA"
1 2015  80235.26 "MLN_USD" "USA"
1 2016  89867.59 "MLN_USD" "USA"
1 2017 104398.25 "MLN_USD" "USA"
1 2018   91698.9 "MLN_USD" "USA"
1 2019 102163.52 "MLN_USD" "USA"
2 2012  3288.918 "MLN_USD" "CHN"
2 2013  2785.823 "MLN_USD" "CHN"
2 2014  2445.197 "MLN_USD" "CHN"
2 2015  2481.219 "MLN_USD" "CHN"
2 2016  2615.158 "MLN_USD" "CHN"
2 2017   3445.67 "MLN_USD" "CHN"
2 2018  3764.598 "MLN_USD" "CHN"
2 2019  3756.459 "MLN_USD" "CHN"
2 2020  3568.536 "MLN_USD" "CHN"
2 2012  6683.377 "MLN_USD" "USA"
2 2013  8441.594 "MLN_USD" "USA"
2 2014 10376.913 "MLN_USD" "USA"
2 2015 10890.582 "MLN_USD" "USA"
2 2016 10451.144 "MLN_USD" "USA"
2 2017 11849.364 "MLN_USD" "USA"
2 2018 13234.486 "MLN_USD" "USA"
2 2019 13385.756 "MLN_USD" "USA"
2 2020 13818.873 "MLN_USD" "USA"
2 2012  1.038 "PC_FDI" "CAN"
2 2013   .985 "PC_FDI" "CAN"
2 2014   .643 "PC_FDI" "CAN"
2 2015   .614 "PC_FDI" "CAN"
2 2016   .501 "PC_FDI" "CAN"
2 2017   .219 "PC_FDI" "CAN"
2 2018   .066 "PC_FDI" "CAN"
2 2019   .089 "PC_FDI" "CAN"
2 2020   .104 "PC_FDI" "CAN"
2 2012 14.604 "PC_FDI" "DEU"
2 2013 14.178 "PC_FDI" "DEU"
2 2014 13.046 "PC_FDI" "DEU"
2 2015   12.8 "PC_FDI" "DEU"
2 2016 14.331 "PC_FDI" "DEU"
2 2017 15.647 "PC_FDI" "DEU"
2 2018 14.407 "PC_FDI" "DEU"
2 2019 16.261 "PC_FDI" "DEU"
2 2020 18.732 "PC_FDI" "DEU"
2 2012  1.312 "PC_FDI" "FRA"
2 2013  1.074 "PC_FDI" "FRA"
2 2014  2.093 "PC_FDI" "FRA"
2 2015  2.021 "PC_FDI" "FRA"
2 2016   2.12 "PC_FDI" "FRA"
2 2017  2.113 "PC_FDI" "FRA"
2 2018  1.708 "PC_FDI" "FRA"
2 2019   .928 "PC_FDI" "FRA"
2 2020   .955 "PC_FDI" "FRA"
2 2012  2.445 "PC_FDI" "GBR"
2 2013  3.366 "PC_FDI" "GBR"
2 2014  3.591 "PC_FDI" "GBR"
2 2015  3.712 "PC_FDI" "GBR"
2 2016  4.156 "PC_FDI" "GBR"
2 2017  3.328 "PC_FDI" "GBR"
2 2018  2.663 "PC_FDI" "GBR"
2 2019  2.529 "PC_FDI" "GBR"
2 2020  2.746 "PC_FDI" "GBR"
2 2012  1.719 "PC_FDI" "ITA"
2 2013  1.419 "PC_FDI" "ITA"
2 2014  1.122 "PC_FDI" "ITA"
2 2015  1.203 "PC_FDI" "ITA"
2 2016  1.606 "PC_FDI" "ITA"
2 2017  1.621 "PC_FDI" "ITA"
2 2018  1.951 "PC_FDI" "ITA"
2 2019  1.861 "PC_FDI" "ITA"
2 2020  2.233 "PC_FDI" "ITA"
2 2012    .03 "PC_FDI" "JPN"
2 2013   .032 "PC_FDI" "JPN"
2 2014   .042 "PC_FDI" "JPN"
2 2015   .065 "PC_FDI" "JPN"
2 2016   .089 "PC_FDI" "JPN"
2 2017   .073 "PC_FDI" "JPN"
2 2018   .129 "PC_FDI" "JPN"
2 2019   .144 "PC_FDI" "JPN"
2 2020   .164 "PC_FDI" "JPN"
2 2012   3.19 "PC_FDI" "USA"
2 2013  3.641 "PC_FDI" "USA"
2 2014  4.758 "PC_FDI" "USA"
2 2015  5.306 "PC_FDI" "USA"
2 2016  5.305 "PC_FDI" "USA"
2 2017  5.069 "PC_FDI" "USA"
2 2018  5.755 "PC_FDI" "USA"
2 2019  5.594 "PC_FDI" "USA"
2 2020  5.816 "PC_FDI" "USA"
end

And the following data are the form that I wash for.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte countryid int year double fdistock_mln_usd str3 fdistock_subject_mln_usd double fdistock_pc_fdi str3 fdistock_subject_pc_fdi
1 2009    2124.14794921875 "CHN"                   . ""  
1 2010     6829.9619140625 "CHN"                   . ""  
1 2011    6524.39013671875 "CHN"                   . ""  
1 2012     8969.8232421875 "CHN"                   . ""  
1 2013                   . "CHN"                   . ""  
1 2014        10307.453125 "CHN"                   . ""  
1 2015       10150.5078125 "CHN"                   . ""  
1 2016     9672.9384765625 "CHN"                   . ""  
1 2017      10492.16015625 "CHN"                   . ""  
1 2018     9812.2529296875 "CHN"                   . ""  
1 2019    10867.3720703125 "CHN"                   . ""  
1 2009       89786.5078125 "USA"                   . ""  
1 2010       97319.8515625 "USA"                   . ""  
1 2011      101819.1015625 "USA"                   . ""  
1 2012      109685.7421875 "USA"                   . ""  
1 2013       110407.796875 "USA"                   . ""  
1 2014       109386.796875 "USA"                   . ""  
1 2015       80235.2578125 "USA"                   . ""  
1 2016         89867.59375 "USA"                   . ""  
1 2017           104398.25 "USA"                   . ""  
1 2018       91698.8984375 "USA"                   . ""  
1 2019      102163.5234375 "USA"                   . ""  
2 2012       3288.91796875 "CHN"                   . ""  
2 2013   2785.822998046875 "CHN"                   . ""  
2 2014   2445.197021484375 "CHN"                   . ""  
2 2015   2481.218994140625 "CHN"                   . ""  
2 2016   2615.157958984375 "CHN"                   . ""  
2 2017      3445.669921875 "CHN"                   . ""  
2 2018   3764.597900390625 "CHN"                   . ""  
2 2019      3756.458984375 "CHN"                   . ""  
2 2020   3568.535888671875 "CHN"                   . ""  
2 2012      6683.376953125 "USA"                   . ""  
2 2013          8441.59375 "USA"                   . ""  
2 2014    10376.9130859375 "USA"                   . ""  
2 2015      10890.58203125 "USA"                   . ""  
2 2016    10451.1435546875 "USA"                   . ""  
2 2017    11849.3642578125 "USA"                   . ""  
2 2018     13234.486328125 "USA"                   . ""  
2 2019     13385.755859375 "USA"                   . ""  
2 2020     13818.873046875 "USA"                   . ""  
2 2012  1.0379999876022339 "CAN"  1.0379999876022339 "CAN"
2 2013   .9850000143051148 "CAN"   .9850000143051148 "CAN"
2 2014   .6430000066757202 "CAN"   .6430000066757202 "CAN"
2 2015   .6140000224113464 "CAN"   .6140000224113464 "CAN"
2 2016   .5009999871253967 "CAN"   .5009999871253967 "CAN"
2 2017  .21899999678134918 "CAN"  .21899999678134918 "CAN"
2 2018  .06599999964237213 "CAN"  .06599999964237213 "CAN"
2 2019  .08900000154972076 "CAN"  .08900000154972076 "CAN"
2 2020  .10400000214576721 "CAN"  .10400000214576721 "CAN"
2 2012  14.604000091552734 "DEU"  14.604000091552734 "DEU"
2 2013  14.178000450134277 "DEU"  14.178000450134277 "DEU"
2 2014  13.045999526977539 "DEU"  13.045999526977539 "DEU"
2 2015  12.800000190734863 "DEU"  12.800000190734863 "DEU"
2 2016  14.331000328063965 "DEU"  14.331000328063965 "DEU"
2 2017  15.647000312805176 "DEU"  15.647000312805176 "DEU"
2 2018  14.406999588012695 "DEU"  14.406999588012695 "DEU"
2 2019   16.26099967956543 "DEU"   16.26099967956543 "DEU"
2 2020   18.73200035095215 "DEU"   18.73200035095215 "DEU"
2 2012   1.312000036239624 "FRA"   1.312000036239624 "FRA"
2 2013  1.0740000009536743 "FRA"  1.0740000009536743 "FRA"
2 2014  2.0929999351501465 "FRA"  2.0929999351501465 "FRA"
2 2015  2.0209999084472656 "FRA"  2.0209999084472656 "FRA"
2 2016   2.119999885559082 "FRA"   2.119999885559082 "FRA"
2 2017    2.11299991607666 "FRA"    2.11299991607666 "FRA"
2 2018  1.7079999446868896 "FRA"  1.7079999446868896 "FRA"
2 2019   .9279999732971191 "FRA"   .9279999732971191 "FRA"
2 2020   .9549999833106995 "FRA"   .9549999833106995 "FRA"
2 2012   2.444999933242798 "GBR"   2.444999933242798 "GBR"
2 2013   3.365999937057495 "GBR"   3.365999937057495 "GBR"
2 2014  3.5910000801086426 "GBR"  3.5910000801086426 "GBR"
2 2015  3.7119998931884766 "GBR"  3.7119998931884766 "GBR"
2 2016   4.156000137329102 "GBR"   4.156000137329102 "GBR"
2 2017   3.328000068664551 "GBR"   3.328000068664551 "GBR"
2 2018  2.6630001068115234 "GBR"  2.6630001068115234 "GBR"
2 2019  2.5290000438690186 "GBR"  2.5290000438690186 "GBR"
2 2020   2.746000051498413 "GBR"   2.746000051498413 "GBR"
2 2012   1.718999981880188 "ITA"   1.718999981880188 "ITA"
2 2013  1.4190000295639038 "ITA"  1.4190000295639038 "ITA"
2 2014   1.121999979019165 "ITA"   1.121999979019165 "ITA"
2 2015  1.2029999494552612 "ITA"  1.2029999494552612 "ITA"
2 2016  1.6059999465942383 "ITA"  1.6059999465942383 "ITA"
2 2017   1.621000051498413 "ITA"   1.621000051498413 "ITA"
2 2018  1.9509999752044678 "ITA"  1.9509999752044678 "ITA"
2 2019  1.8609999418258667 "ITA"  1.8609999418258667 "ITA"
2 2020  2.2330000400543213 "ITA"  2.2330000400543213 "ITA"
2 2012 .029999999329447746 "JPN" .029999999329447746 "JPN"
2 2013  .03200000151991844 "JPN"  .03200000151991844 "JPN"
2 2014 .041999999433755875 "JPN" .041999999433755875 "JPN"
2 2015  .06499999761581421 "JPN"  .06499999761581421 "JPN"
2 2016  .08900000154972076 "JPN"  .08900000154972076 "JPN"
2 2017   .0729999989271164 "JPN"   .0729999989271164 "JPN"
2 2018   .1289999932050705 "JPN"   .1289999932050705 "JPN"
2 2019  .14399999380111694 "JPN"  .14399999380111694 "JPN"
2 2020    .164000004529953 "JPN"    .164000004529953 "JPN"
2 2012   3.190000057220459 "USA"   3.190000057220459 "USA"
2 2013  3.6410000324249268 "USA"  3.6410000324249268 "USA"
2 2014   4.757999897003174 "USA"   4.757999897003174 "USA"
2 2015   5.306000232696533 "USA"   5.306000232696533 "USA"
2 2016   5.304999828338623 "USA"   5.304999828338623 "USA"
2 2017   5.068999767303467 "USA"   5.068999767303467 "USA"
end

I am grateful for your reading and hope anyone can give me suggestions or feedback.

[Nick Cox]

What reshape command did you try?

[Enzo Wu]

Nick Cox I used the following code at first.

reshape wide fdistock fdistock_subject , i( countryid year ) j( fdistock_measure ) string 

Then, of course, it reported an " fdistock_measure not unique within countryid year" error.

Thus, I looked for the solutions and this is the reference I used:
https://stackoverflow.com/questions/...iable-in-stata

And this is the code I modified based on the reference.

bysort fdistock_measure: gen id = _n
reshape wide fdistock fdistock_subject fdistock_measure , i( year countryid ) j( id )

But the output is not what I expected.

[Nick Cox]

Does this help?

. rename fdistock Y

. reshape wide Y , i(countryid year fdistock_subject) j(fdistock_measure) string 
(j = MLN_USD PC_FDI)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations              103   ->   94          
Number of variables                   5   ->   5           
j variable (2 values)  fdistock_measure   ->   (dropped)
xij variables:
                                      Y   ->   YMLN_USD YPC_FDI
-----------------------------------------------------------------------------

. rename Y* * 

. 
. list 

     +------------------------------------------------+
     | countr~d   year   fdisto~t    MLN_USD   PC_FDI |
     |------------------------------------------------|
  1. |        1   2009        CHN   2124.148        . |
  2. |        1   2009        USA   89786.51        . |
  3. |        1   2010        CHN   6829.962        . |
  4. |        1   2010        USA   97319.85        . |
  5. |        1   2011        CHN    6524.39        . |
     |------------------------------------------------|
  6. |        1   2011        USA   101819.1        . |
  7. |        1   2012        CHN   8969.823        . |
  8. |        1   2012        USA   109685.7        . |
  9. |        1   2013        CHN          .        . |
 10. |        1   2013        USA   110407.8        . |
     |------------------------------------------------|
 11. |        1   2014        CHN   10307.45        . |
 12. |        1   2014        USA   109386.8        . |
 13. |        1   2015        CHN   10150.51        . |
 14. |        1   2015        USA   80235.26        . |
 15. |        1   2016        CHN   9672.938        . |
     |------------------------------------------------|
 16. |        1   2016        USA   89867.59        . |
 17. |        1   2017        CHN   10492.16        . |
 18. |        1   2017        USA   104398.3        . |
 19. |        1   2018        CHN   9812.253        . |
 20. |        1   2018        USA    91698.9        . |
     |------------------------------------------------|
 21. |        1   2019        CHN   10867.37        . |
 22. |        1   2019        USA   102163.5        . |
 23. |        2   2012        CAN          .    1.038 |
 24. |        2   2012        CHN   3288.918        . |
 25. |        2   2012        DEU          .   14.604 |
     |------------------------------------------------|
 26. |        2   2012        FRA          .    1.312 |
 27. |        2   2012        GBR          .    2.445 |
 28. |        2   2012        ITA          .    1.719 |
 29. |        2   2012        JPN          .      .03 |
 30. |        2   2012        USA   6683.377     3.19 |
     |------------------------------------------------|
 31. |        2   2013        CAN          .     .985 |
 32. |        2   2013        CHN   2785.823        . |
 33. |        2   2013        DEU          .   14.178 |
 34. |        2   2013        FRA          .    1.074 |
 35. |        2   2013        GBR          .    3.366 |
     |------------------------------------------------|
 36. |        2   2013        ITA          .    1.419 |
 37. |        2   2013        JPN          .     .032 |
 38. |        2   2013        USA   8441.594    3.641 |
 39. |        2   2014        CAN          .     .643 |
 40. |        2   2014        CHN   2445.197        . |
     |------------------------------------------------|
 41. |        2   2014        DEU          .   13.046 |
 42. |        2   2014        FRA          .    2.093 |
 43. |        2   2014        GBR          .    3.591 |
 44. |        2   2014        ITA          .    1.122 |
 45. |        2   2014        JPN          .     .042 |
     |------------------------------------------------|
 46. |        2   2014        USA   10376.91    4.758 |
 47. |        2   2015        CAN          .     .614 |
 48. |        2   2015        CHN   2481.219        . |
 49. |        2   2015        DEU          .     12.8 |
 50. |        2   2015        FRA          .    2.021 |
     |------------------------------------------------|
 51. |        2   2015        GBR          .    3.712 |
 52. |        2   2015        ITA          .    1.203 |
 53. |        2   2015        JPN          .     .065 |
 54. |        2   2015        USA   10890.58    5.306 |
 55. |        2   2016        CAN          .     .501 |
     |------------------------------------------------|
 56. |        2   2016        CHN   2615.158        . |
 57. |        2   2016        DEU          .   14.331 |
 58. |        2   2016        FRA          .     2.12 |
 59. |        2   2016        GBR          .    4.156 |
 60. |        2   2016        ITA          .    1.606 |
     |------------------------------------------------|
 61. |        2   2016        JPN          .     .089 |
 62. |        2   2016        USA   10451.14    5.305 |
 63. |        2   2017        CAN          .     .219 |
 64. |        2   2017        CHN    3445.67        . |
 65. |        2   2017        DEU          .   15.647 |
     |------------------------------------------------|
 66. |        2   2017        FRA          .    2.113 |
 67. |        2   2017        GBR          .    3.328 |
 68. |        2   2017        ITA          .    1.621 |
 69. |        2   2017        JPN          .     .073 |
 70. |        2   2017        USA   11849.36    5.069 |
     |------------------------------------------------|
 71. |        2   2018        CAN          .     .066 |
 72. |        2   2018        CHN   3764.598        . |
 73. |        2   2018        DEU          .   14.407 |
 74. |        2   2018        FRA          .    1.708 |
 75. |        2   2018        GBR          .    2.663 |
     |------------------------------------------------|
 76. |        2   2018        ITA          .    1.951 |
 77. |        2   2018        JPN          .     .129 |
 78. |        2   2018        USA   13234.49    5.755 |
 79. |        2   2019        CAN          .     .089 |
 80. |        2   2019        CHN   3756.459        . |
     |------------------------------------------------|
 81. |        2   2019        DEU          .   16.261 |
 82. |        2   2019        FRA          .     .928 |
 83. |        2   2019        GBR          .    2.529 |
 84. |        2   2019        ITA          .    1.861 |
 85. |        2   2019        JPN          .     .144 |
     |------------------------------------------------|
 86. |        2   2019        USA   13385.76    5.594 |
 87. |        2   2020        CAN          .     .104 |
 88. |        2   2020        CHN   3568.536        . |
 89. |        2   2020        DEU          .   18.732 |
 90. |        2   2020        FRA          .     .955 |
     |------------------------------------------------|
 91. |        2   2020        GBR          .    2.746 |
 92. |        2   2020        ITA          .    2.233 |
 93. |        2   2020        JPN          .     .164 |
 94. |        2   2020        USA   13818.87    5.816 |
     +------------------------------------------------+

[Enzo Wu]

Nick Cox Yes, it's really helpful!

I really appreciate your solution. Thank again for your time and quick reply.