Wrangling data

[Ishto Stanfield]

Hello,

I have a dataset that looks like this:

input str6 country str17 category float indicatora byte(indicatorb indicatorc)
"Japan " "GDP Output"                54  42  14
"Japan " "Growth rate: cont"          3  40  12
"Japan " "GDP growth"                65  38  10
"USA"    "GDP Output"                54   3  -2
"USA"    "Growth rate: cont"         76  24  -4
"USA"    "GDP growth"                45  22  -6
"KOREA " "GDP Output"        -1.5238096  10 -18
"KOREA " "Growth rate: cont"  -8.952381   8 -20
"KOREA " "GDP growth"        -16.380953   6 -22
"INDIA"  "GDP Output"         -60.95238  -6 -34
"INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"INDIA"  "GDP growth"        -75.809525 -10 -38
"UK"     "GDP Output"        -120.38095 -22 -50
"UK"     "Growth rate: cont" -127.80952 -24 -52
"UK"     "GDP growth"         -135.2381 -26 -54
"ITALY"  "GDP Output"        -179.80952 -38 -66
"ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"ITALY"  "GDP growth"        -194.66667 -42 -70
end

And I would like to have a Stata code to transform the dataset as to have “JAPON”, “USA”,”KOREA”, “INDIA”, “UK”, “ITALY” successively in front of each observation in the dataset. I would like the result to look like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str6(countryp country) str17 category float indicatora byte(indicatorb indicatorc)
"Japan " "Japan " "GDP Output"                54  42  14
"Japan " "Japan " "Growth rate: cont"          3  40  12
"Japan " "Japan " "GDP growth"                65  38  10
"Japan " "USA"    "GDP Output"                54   3  -2
"Japan " "USA"    "Growth rate: cont"         76  24  -4
"Japan " "USA"    "GDP growth"                45  22  -6
"Japan " "KOREA " "GDP Output"        -1.5238096  10 -18
"Japan " "KOREA " "Growth rate: cont"  -8.952381   8 -20
"Japan " "KOREA " "GDP growth"        -16.380953   6 -22
"Japan " "INDIA"  "GDP Output"         -60.95238  -6 -34
"Japan " "INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"Japan " "INDIA"  "GDP growth"        -75.809525 -10 -38
"Japan " "UK"     "GDP Output"        -120.38095 -22 -50
"Japan " "UK"     "Growth rate: cont" -127.80952 -24 -52
"Japan " "UK"     "GDP growth"         -135.2381 -26 -54
"Japan " "ITALY"  "GDP Output"        -179.80952 -38 -66
"Japan " "ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"Japan " "ITALY"  "GDP growth"        -194.66667 -42 -70
"USA"    "Japan " "GDP Output"                54  42  14
"USA"    "Japan " "Growth rate: cont"          3  40  12
"USA"    "Japan " "GDP growth"                65  38  10
"USA"    "USA"    "GDP Output"                54   3  -2
"USA"    "USA"    "Growth rate: cont"         76  24  -4
"USA"    "USA"    "GDP growth"                45  22  -6
"USA"    "KOREA " "GDP Output"        -1.5238096  10 -18
"USA"    "KOREA " "Growth rate: cont"  -8.952381   8 -20
"USA"    "KOREA " "GDP growth"        -16.380953   6 -22
"USA"    "INDIA"  "GDP Output"         -60.95238  -6 -34
"USA"    "INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"USA"    "INDIA"  "GDP growth"        -75.809525 -10 -38
"USA"    "UK"     "GDP Output"        -120.38095 -22 -50
"USA"    "UK"     "Growth rate: cont" -127.80952 -24 -52
"USA"    "UK"     "GDP growth"         -135.2381 -26 -54
"USA"    "ITALY"  "GDP Output"        -179.80952 -38 -66
"USA"    "ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"USA"    "ITALY"  "GDP growth"        -194.66667 -42 -70
"KOREA"  "Japan " "GDP Output"                54  42  14
"KOREA"  "Japan " "Growth rate: cont"          3  40  12
"KOREA"  "Japan " "GDP growth"                65  38  10
"KOREA"  "USA"    "GDP Output"                54   3  -2
"KOREA"  "USA"    "Growth rate: cont"         76  24  -4
"KOREA"  "USA"    "GDP growth"                45  22  -6
"KOREA"  "KOREA " "GDP Output"        -1.5238096  10 -18
"KOREA"  "KOREA " "Growth rate: cont"  -8.952381   8 -20
"KOREA"  "KOREA " "GDP growth"        -16.380953   6 -22
"KOREA"  "INDIA"  "GDP Output"         -60.95238  -6 -34
"KOREA"  "INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"KOREA"  "INDIA"  "GDP growth"        -75.809525 -10 -38
"KOREA"  "UK"     "GDP Output"        -120.38095 -22 -50
"KOREA"  "UK"     "Growth rate: cont" -127.80952 -24 -52
"KOREA"  "UK"     "GDP growth"         -135.2381 -26 -54
"KOREA"  "ITALY"  "GDP Output"        -179.80952 -38 -66
"KOREA"  "ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"KOREA"  "ITALY"  "GDP growth"        -194.66667 -42 -70
"INDIA"  "Japan " "GDP Output"                54  42  14
"INDIA"  "Japan " "Growth rate: cont"          3  40  12
"INDIA"  "Japan " "GDP growth"                65  38  10
"INDIA"  "USA"    "GDP Output"                54   3  -2
"INDIA"  "USA"    "Growth rate: cont"         76  24  -4
"INDIA"  "USA"    "GDP growth"                45  22  -6
"INDIA"  "KOREA " "GDP Output"        -1.5238096  10 -18
"INDIA"  "KOREA " "Growth rate: cont"  -8.952381   8 -20
"INDIA"  "KOREA " "GDP growth"        -16.380953   6 -22
"INDIA"  "INDIA"  "GDP Output"         -60.95238  -6 -34
"INDIA"  "INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"INDIA"  "INDIA"  "GDP growth"        -75.809525 -10 -38
"INDIA"  "UK"     "GDP Output"        -120.38095 -22 -50
"INDIA"  "UK"     "Growth rate: cont" -127.80952 -24 -52
"INDIA"  "UK"     "GDP growth"         -135.2381 -26 -54
"INDIA"  "ITALY"  "GDP Output"        -179.80952 -38 -66
"INDIA"  "ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"INDIA"  "ITALY"  "GDP growth"        -194.66667 -42 -70
"UK"     "Japan " "GDP Output"                54  42  14
"UK"     "Japan " "Growth rate: cont"          3  40  12
"UK"     "Japan " "GDP growth"                65  38  10
"UK"     "USA"    "GDP Output"                54   3  -2
"UK"     "USA"    "Growth rate: cont"         76  24  -4
"UK"     "USA"    "GDP growth"                45  22  -6
"UK"     "KOREA " "GDP Output"        -1.5238096  10 -18
"UK"     "KOREA " "Growth rate: cont"  -8.952381   8 -20
"UK"     "KOREA " "GDP growth"        -16.380953   6 -22
"UK"     "INDIA"  "GDP Output"         -60.95238  -6 -34
"UK"     "INDIA"  "Growth rate: cont"  -68.38095  -8 -36
"UK"     "INDIA"  "GDP growth"        -75.809525 -10 -38
"UK"     "UK"     "GDP Output"        -120.38095 -22 -50
"UK"     "UK"     "Growth rate: cont" -127.80952 -24 -52
"UK"     "UK"     "GDP growth"         -135.2381 -26 -54
"UK"     "ITALY"  "GDP Output"        -179.80952 -38 -66
"UK"     "ITALY"  "Growth rate: cont"  -187.2381 -40 -68
"UK"     "ITALY"  "GDP growth"        -194.66667 -42 -70
"ITALY"  "Japan " "GDP Output"                54  42  14
"ITALY"  "Japan " "Growth rate: cont"          3  40  12
"ITALY"  "Japan " "GDP growth"                65  38  10
"ITALY"  "USA"    "GDP Output"                54   3  -2
"ITALY"  "USA"    "Growth rate: cont"         76  24  -4
"ITALY"  "USA"    "GDP growth"                45  22  -6
"ITALY"  "KOREA " "GDP Output"        -1.5238096  10 -18
"ITALY"  "KOREA " "Growth rate: cont"  -8.952381   8 -20
"ITALY"  "KOREA " "GDP growth"        -16.380953   6 -22
"ITALY"  "INDIA"  "GDP Output"         -60.95238  -6 -34
end

Thank you

[Daniel Feenberg]

see -joinby- https://www.stata.com/manuals/djoinby.pdf

[Bruce Weaver]

I was thinking -levelsof-. The following does not show the records in the same order you did in your second dataset, but if the order of the records does not matter, it works. If the order does matter, I suppose you could recode variable c as needed before sorting.

levelsof country, local(levels)
local i = 0
foreach c of local levels {
    local i = `i'+1
    generate countryp`i' = "`c'"
}

generate i = _n
reshape long countryp, i(i) j(c)
order countryp country-indicatorc i c
sort c i

[Ishto Stanfield]

Thank you very much, Bruce.