Reshaping World Development Indicators/ Observations in v5- v27 are non-numeric

[Hugo Rocha]

Dear all,

I downloaded the following series for a subset of countries from the World Development Indicators. Although I watched one youtube video about reshaping the data for WDI information, I think the series shown are old, so the formatting is not the same as today. Hence, the reshaping process is different.

I am trying to reshape this data to have one column year (observations from 1972-2021) alongside columns with each series as variables, columns for countries, and so on.

In addition, observations from v5 to v27 are non-numeric. When I apply encode on each one, I get, for instance, v27: contains nonnumeric characters; no replace. How can I come about solving these two issues?

 * Example generated by -dataex-. To install: ssc install dataex
clear
input str72 variable str15 country str12 countrycode str11(v5 v6 v7 v8 v9 v10 v11 v12 v13 v14)
"Series Name"                                                              "Country Name"    "Country Code" "1972"        "1973"        "1974"        "1975"        "1976"        "1977"        "1978"        "1979"        "1980"        "1981"       
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Cyprus"          "CYP"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in industry (% of total employment) (modeled ILO estimate)"    "Malta"           "MLT"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Cyprus"          "CYP"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in agriculture (% of total employment) (modeled ILO estimate)" "Malta"           "MLT"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Cyprus"          "CYP"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employers, total (% of total employment) (modeled ILO estimate)"          "Malta"           "MLT"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Cyprus"          "CYP"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Employment in services (% of total employment) (modeled ILO estimate)"    "Malta"           "MLT"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Cyprus"          "CYP"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Services, value added (% of GDP)"                                         "Malta"           "MLT"          "33.83763926" "31.78927926" "29.40245766" "27.41839901" "25.69963429" "26.74008513" "27.98347804" "29.45679524" "30.49190174" "32.29612044"
"Manufacturing, value added (% of GDP)"                                    "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Manufacturing, value added (% of GDP)"                                    "Cyprus"          "CYP"          ".."          ".."          ".."          "14.31905766" "17.25066696" "17.77357132" "18.2823311"  "17.59288274" "17.54570283" "17.60273646"
"Manufacturing, value added (% of GDP)"                                    "Malta"           "MLT"          "19.73929961" "21.13808816" "23.42250357" "25.78382863" "27.62220361" "27.71768178" "27.84705358" "28.09467493" "26.94342672" "25.46110353"
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Hungary"         "HUN"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Poland"          "POL"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Czechia"         "CZE"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Slovak Republic" "SVK"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Estonia"         "EST"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Latvia"          "LVA"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Lithuania"       "LTU"          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."          ".."         
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Cyprus"          "CYP"          ".."          ".."          ".."          "15.7198437"  "15.90295915" "13.21200322" "10.91807019" "10.241347"   "9.588316752" "9.25799218" 
"Agriculture, forestry, and fishing, value added (% of GDP)"               "Malta"           "MLT"          "6.686666583" "6.507734585" "6.490089131" "5.709301525" "5.767803587" "5.525901623" "4.20351554"  "3.636335584" "3.4837001"   "3.540379525"
end

Thank you much!

[Clyde Schechter]

Your data set is not a conventional Stata data set. The first observation appears to contain what should be variable names. So that has to be fixed before proceeding. After that, it is a question of creating legal variable names out of the observations in ("variable"), and then doing two -reshape-s.

//  CONVERT TO TO CONVENTIONAL STATA DATA SET ORGANIZATION
foreach v of varlist v5-v14 {
    replace `v' = "" if `v' == ".."
    rename `v' _`=`v'[1]'
}
drop in 1
destring _*, replace

//  CREATE LEGAL VARIABLE NAMES FOR THE END RESULT
gen series = strtoname(lower(substr(variable, 1, 31)))
by series (variable), sort: assert variable[1] == variable[_N] // VERIFY SERIES KEPT DISTINCT
drop variable

//  RESHAPE TWICE
reshape long _, i(series country) j(year)
reshape wide _, i(country year) j(series) string
rename _* *

[Hugo Rocha]

Your data set is not a conventional Stata data set. The first observation appears to contain what should be variable names. So that has to be fixed before proceeding. After that, it is a question of creating legal variable names out of the observations in ("variable"), and then doing two -reshape-s.

// CONVERT TO TO CONVENTIONAL STATA DATA SET ORGANIZATION
foreach v of varlist v5-v14 {
replace `v' = "" if `v' == ".."
rename `v' _`=`v'[1]'
}
drop in 1
destring _*, replace

// CREATE LEGAL VARIABLE NAMES FOR THE END RESULT
gen series = strtoname(lower(substr(variable, 1, 31)))
by series (variable), sort: assert variable[1] == variable[_N] // VERIFY SERIES KEPT DISTINCT
drop variable

// RESHAPE TWICE
reshape long _, i(series country) j(year)
reshape wide _, i(country year) j(series) string
rename _* *

Yes, it is very unconventional. I am spending a couple of hours trying to think on reshaping this dataset. Thank you so much! I am trying to understand the first loop, why the varlist goes from v5-v14? It shouldn't be from v5-27?

[Clyde Schechter]

In your example data, v14 is the last variable. If the real data set extends all the way to v27, then, yes, change that first loop.

[Hugo Rocha]

In your example data, v14 is the last variable. If the real data set extends all the way to v27, then, yes, change that first loop.

I would just like to thank you for your help. It was really important