Hi Tester:
The source data file, which is listed in your reply, was downloaded from kaggle.com (one needs to register to get dataset).
master = Import["C:\\suicide-rates-overview-1985-to-2016\\master.csv"];
mastercountriesraw = DeleteDuplicates[Rest[master[[All, 1]]]];
Now one needs the UN's version of their M49 methodology file. In a webbrowser navigate to this URL page
[https://unstats.un.org/unsd/methodology/m49/] with "Geographic Regions" selected from left menu-like items under the "M49 Standard"
Title:
Click on "Search and Download:" "Full view"
Leads to [https://unstats.un.org/unsd/methodology/m49/overview/]
There is a CSV export (button) which is much better than trying to gather the data from Import[URL, "Data"]
If one were to explore this branch it may lead to more clean up work than currently necessary for purposes of providing a possible solution path.
data = Import["https://unstats.un.org/unsd/methodology/m49/overview/", "Data"];
Part[data, 2, 2, 1, 1]
Gets the header data
Part[data, 2, 2, 1, 2]
Gets the rest but not all rows contains same amount of columns so this information is ragged. Can be fix but not worth the detour.
So download the CSV file default name is "UNSD -- Methodology.csv".
UNmethoddata = Import["C:\\suicide-rates-overview-1985-to-2016\\UNSD \[LongDash] \
Methodology.csv"];
Now back to mastercountriesraw. Let's see if we can resolve the distinct countries as Entities in Wolfram Language (WL). If possible, then we will have Entity["Country","CountryName"] such that we could query for the WL CountryData property "UNNumber".
The simplest way to let the WL automatically resolve the raw country names from the master file to a WL Entity is as follows:
mastercountriesinter = Map[Interpreter["Country"][#] &, mastercountriesraw];
The above is a bit slower for getting country data, but for this particular file yields no relevant issues. However, the following functions get us there and creates a dataset that one can explore
Clear[master, mastercountriesraw, UNmethoddata, addcountryprop,
UNcountryprop, groupUNcountryprop, ATgroupUNcountryprop, dataset];
master = Import[
"C:\\suicide-rates-overview-1985-to-2016\\master.csv"];
mastercountriesraw = DeleteDuplicates[Rest[master[[All, 1]]]];
UNmethoddata =
Import["C:\\suicide-rates-overview-1985-to-2016\\UNSD \
\[LongDash] MethodologyFixed.csv"];
addcountryprop[cnty_] :=
Module[{countryraw, countryent, continent, unnumber, globalcode,
globalname, regioncode, regionname, subregioncode, subregionname,
intermediateregioncode, intermediateregionname, countryorarea,
M49code, bdata}, countryraw = cnty;
countryent = Quiet[CountryData[cnty]];
If[ToString[Head[countryent]] == "CountryData",
countryent = Interpreter["Country"][cnty]];
continent = CanonicalName[CountryData[countryent, "Continent"]];
unnumber = CountryData[countryent, "UNNumber"];
{globalcode, globalname, regioncode, regionname, subregioncode,
subregionname, intermediateregioncode, intermediateregionname,
countryorarea, M49code} =
Take[SelectFirst[UNmethoddata, (#[[10]] == unnumber) &], 10];
bdata = Select[master, (#[[1]] == countryraw) &];
{globalname, regionname, subregionname,
If[intermediateregionname == "", subregionname,
intermediateregionname] , continent, countryraw, Rest /@ bdata}];
UNcountryprop = Map[addcountryprop[#] &, mastercountriesraw];
groupUNcountryprop =
GatherBy[SortBy[
UNcountryprop, {#[[1]], #[[2]], #[[3]], #[[4]]} &], {#[[1]], \
#[[2]], #[[3]], #[[4]]} &];
makeassoc[item_] := Module[{},
{Association[
Rule[item[[1]][[1]],
Association[
Rule[item[[1]][[2]],
Association[
Rule[item[[1]][[3]],
Association[
Rule[item[[1]][[4]],
Association[
Rule[item[[1]][[5]],
Association[
Rule[item[[1]][[6]],
Flatten[
Map[AssociationThread[{"year", "sex", "age",
"suicides", "population", "suicidesper100kpop",
"countryyear", "HDIforyear", "gdpforyear",
"gdppercapita", "generation"}, #] &,
item[[1]][[7]]]]
]
]
]
]
]
]
]
]
]
]
]
]
}];
ATgroupUNcountryprop = Map[makeassoc[#] &, groupUNcountryprop];
dataset = Dataset[ATgroupUNcountryprop]
Now in your initial post you really didn't ask to do any calculations but need to add data to the master file. And in your second post I assume from the image you would like an accordion like UI to just navigate the data. WL Dataset[] function produces an accordion like UI,
You can click on say "World"
You can click on the Association cell and
Leave it to poster to explore Query[] function.
The Fixed csv file fix is to put double quotes around some entries which have commas in them the "China, Hong Kong ..." and the like and "Bonaire,...".
