4
$\begingroup$

I have the following input:

ClearAll[rawdata1, datas1, rawdata2, datas2, rawdata3, datas3, rawdata4, datas4, chl, mean];

(* import data from four Excel worksheets *)

(*
rawdata1=Import["C:\\Users\\ttemel\\Downloads\\LDC_data.xlsx",{"Data", 13}];
rawdata2=Import["C:\\Users\\ttemel\\Downloads\\LDC_data.xlsx",{"Data", 14}];
rawdata3=Import["C:\\Users\\ttemel\\Downloads\\LDC_data.xlsx",{"Data", 15}];
rawdata4=Import["C:\\Users\\ttemel\\Downloads\\LDC_data.xlsx",{"Data", 16}];
*)

For easiness of illustration, here are the 4 datasets imported from four Excel worksheets:

rawdata1 = {{"time", "country", "region", "subregion", "x1", "x2", "x3"},
    {"t1", "c1", "R1", "SR1", 1.`, 2.`, 3.`}, {"t1", "c2", "R1", "SR2", 4.`, 2.`, 8.`},
    {"t1", "c3", "R2", "SR1", 5.`, 7.`, 6.`}, {"t1", "c4", "R2", "SR2", 4.`, 8.`, 1.`}};
rawdata2 = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t2", "c1", "R1", "SR1", 0.`, 2.`, 4.`}, {"t2", "c2", 
    "R1", "SR2", 1.`, 2.`, 1.`}, {"t2", "c3", "R2", "SR1", 3.`, 5.`, 
    8.`}, {"t2", "c4", "R2", "SR2", 4.`, 8.`, 3.`}};
rawdata3 = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t3", "c1", "R1", "SR1", 3.`, 2.`, 5.`}, {"t3", "c2", 
    "R1", "SR2", 1.`, 3.`, 2.`}, {"t3", "c3", "R2", "SR1", 2.`, 5.`, 
    1.`}, {"t3", "c4", "R2", "SR2", 7.`, 3.`, 8.`}};
rawdata4 = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t4", "c1", "R1", "SR1", 6.`, 3.`, 1.`}, {"t4", "c2", 
    "R1", "SR2", 2.`, 2.`, 5.`}, {"t4", "c3", "R2", "SR1", 6.`, 8.`, 
    1.`}, {"t4", "c4", "R2", "SR2", 1.`, 2.`, 9.`}};

Then, I created individual databases, one for each period (because sometimes I use only one database for the analysis):

datas1 = Dataset[AssociationThread[First@rawdata1 -> #] & /@ Rest@rawdata1];
datas2 = Dataset[AssociationThread[First@rawdata2 -> #] & /@ Rest@rawdata2];
datas3 = Dataset[AssociationThread[First@rawdata3 -> #] & /@ Rest@rawdata3];
datas4 = Dataset[AssociationThread[First@rawdata4 -> #] & /@ Rest@rawdata4];

See the output format of the four databases:

{TableForm[datas1], TableForm[datas2], TableForm[datas3], TableForm[datas4]}

Since I have time, country, region and subregion indices, combining individual databases as a single database gives me flexibility in programming:

jdata = Join[datas1, datas2, datas3, datas4];

from which I want to retrieve data to plot various charts such as BarCharts and LinePlots. Here is an example for retrieving data for x3 across countries and time:

(* needs improvement in selection method *)
tsC1x3 = jdata[{1, 5, 9, 13}][[All, "x3"]];  (* retrieve time series data for a single country 1 and a single variable x3 *)
tsC2x3 = jdata[{2, 6, 10, 14}][[All, "x3"]]; (* retrieve time series data for a single country 2 and a single variable x3 *)
tsC3x3 = jdata[{3, 7, 11, 15}][[All, "x3"]];
tsC4x3 = jdata[{4, 8, 12, 16}][[All, "x3"]];

Now, I draw Barchart

ClearAll[chl];
chl = {"Bhutan", "Cambodia", "Tonga", "Lao PDR"};
BarChart[
 Join[tsC1x3, tsC2x3, tsC3x3, tsC4x3],
 TicksStyle -> Directive[FontFamily -> "Arial", FontSize -> 12],
 ChartLayout -> "Grouped",
 ChartLegends -> Placed[{"t=1", "t=2", "t=3", "t=4"}, Below],
 ChartLabels -> {Placed[chl, {{0.5, 0}, {0.9, 1}}, Rotate[#, Pi/4] &],
    None},
 ChartStyle -> {
   RGBColor[{157, 135, 122}/255 // N],
   RGBColor[{251, 195, 011}/255 // N],
   RGBColor[{198, 030, 044}/255 // N],
   RGBColor[{027, 150, 212}/255 // N]
   },
 PlotTheme -> "Business",
 PlotLabel -> Style["X3 across countries and time", Black, Bold, 12],
 BarOrigin -> Bottom,
 BarSpacing -> {0.1, 4},
 FrameLabel -> {None, Row[{Spacer[1], Style["USD", Bold, 11]}]},
 ImageSize -> 400,
 Background -> Lighter[Gray, .9],
 AspectRatio -> 1/2
 ]

My problems start here.

  1. The Barchart produced using the above code does not group four countries across four years. You will see it when you plot the bar chart.
  2. Country labels for each time series group are not shown.
  3. Last but most difficult is to make the selection of cross-section (for a given time) and time-series (for a given variable) as is seen from the definitions tsC1x3, tsC2x3, tsC3x3, tsC4x3. What I want to achieve is simple. For example, I want to draw a single Barchart for all countries across all years for a given variable. OR I want to draw a time-series trend of a variable over time for a single country and so on.
  4. I am looking for an effective way to select desired data using the 4 indices (time, country, region, and subregion). Currently, it is not efficient nor doable for very large datasets. A systematic method for the extraction of data is most welcome.

I would appreciate any help.

$\endgroup$
2
  • 2
    $\begingroup$ A better way to select country/variable tsC1x3 = jdata[Select[#country == "c1" &], "x3"] $\endgroup$ Oct 25, 2021 at 14:16
  • $\begingroup$ @Rohit: For one country, BarChart[jdata[Select[#country == "c1" &], "x3"]] works fine. For all the countries jdata[GroupBy["country"], BarChart[ #]&, "x3"] works fine. If I change grouping to GroupBy["time"] I do the same across time. My biggest problem is to combine individual country bar charts as a single bar chart. Imagine a bar chart showing each country bar chart on the same plot. $\endgroup$ Oct 25, 2021 at 15:20

2 Answers 2

6
$\begingroup$
Clear["Global`*"]

$Version

(* "12.3.1 for Mac OS X x86 (64-bit) (June 19, 2021)" *)

To simplify your code use indexed variables

rawdata[1] = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t1", "c1", "R1", "SR1", 1.`, 2.`, 3.`}, {"t1", "c2", "R1", "SR2",
     4.`, 2.`, 8.`}, {"t1", "c3", "R2", "SR1", 5.`, 7.`, 6.`}, {"t1", "c4", 
    "R2", "SR2", 4.`, 8.`, 1.`}};
rawdata[2] = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t2", "c1", "R1", "SR1", 0.`, 2.`, 4.`}, {"t2", "c2", "R1", "SR2",
     1.`, 2.`, 1.`}, {"t2", "c3", "R2", "SR1", 3.`, 5.`, 8.`}, {"t2", "c4", 
    "R2", "SR2", 4.`, 8.`, 3.`}};
rawdata[3] = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t3", "c1", "R1", "SR1", 3.`, 2.`, 5.`}, {"t3", "c2", "R1", "SR2",
     1.`, 3.`, 2.`}, {"t3", "c3", "R2", "SR1", 2.`, 5.`, 1.`}, {"t3", "c4", 
    "R2", "SR2", 7.`, 3.`, 8.`}};
rawdata[4] = {{"time", "country", "region", "subregion", "x1", "x2", 
    "x3"}, {"t4", "c1", "R1", "SR1", 6.`, 3.`, 1.`}, {"t4", "c2", "R1", "SR2",
     2.`, 2.`, 5.`}, {"t4", "c3", "R2", "SR1", 6.`, 8.`, 1.`}, {"t4", "c4", 
    "R2", "SR2", 1.`, 2.`, 9.`}};

datas[n_Integer?Positive] := 
  Dataset[AssociationThread[First@rawdata[n] -> #] & /@ Rest@rawdata[n]];

jdata = Join @@ (datas /@ Range[4]);

ts[ctry_, var_] := jdata[Select[#country == ctry &], var]

cs[t_, var_] := jdata[Select[#time == t &], var]

countries = {"Bhutan", "Cambodia", "Tonga", "Lao PDR"};

Use Manipulate to control the data display

Manipulate[
 BarChart[
  If[group === "Country",
   Normal[ts[#, var]] & /@ {"c1", "c2", "c3", "c4"},
   Normal[cs[#, var]] & /@ {"t1", "t2", "t3", "t4"}], 
  TicksStyle -> Directive[FontFamily -> "Arial", FontSize -> 12], 
  ChartLayout -> "Grouped", 
  ChartLegends -> Placed[If[group === "Country",
     {"t=1", "t=2", "t=3", "t=4"}, countries], Below],
  ChartLabels -> {If[group === "Country", countries,
     {"t1", "t2", "t3", "t4"}], None}, 
  ChartStyle -> {
    RGBColor[{157, 135, 122}/255 // N], 
    RGBColor[{251, 195, 011}/255 // N], 
    RGBColor[{198, 030, 044}/255 // N], 
    RGBColor[{027, 150, 212}/255 // N]}, 
  PlotTheme -> "Business", 
  PlotLabel -> 
    Style[var <> " across countries and time", Black, Bold, 12], 
  BarOrigin -> Bottom, 
  BarSpacing -> {0.1, 4}, 
  FrameLabel -> {None, Row[{Spacer[1], Style["USD", Bold, 11]}]}, 
  ImageSize -> 400, 
  Background -> Lighter[Gray, .9], 
  AspectRatio -> 1/2],
 Row[{
   Control[{{group, "Country"}, {"Country", "Time"}}],
   Spacer[50],
   Control[{{var, "x3"}, {"x1", "x2", "x3"}}]}]]

enter image description here

$\endgroup$
1
  • $\begingroup$ This is really great. I was imagining this answer but was afraid of asking it. You read my mind. I really appreciate your effort and code. $\endgroup$ Oct 25, 2021 at 16:46
3
$\begingroup$
jdata = Join @@ Map[a |->
 Dataset[AssociationThread[First @ a, #] & /@ Rest[a]]][
  {rawdata1, rawdata2, rawdata3, rawdata4}];

groupeddata = jdata[GroupBy[#country &], GroupBy[#time &], All, {"x1", "x2", "x3"}];

Row[{jdata, groupeddata}, Spacer[20]]

enter image description here

Define a Query that constructs a BarChart from selected columns of groupeddata:

ClearAll[barChart, bcQ]

barChart[opts : OptionsPattern[]] := BarChart[#, opts,
    ChartLabels -> Automatic, ChartLegends -> Automatic] &;

bcQ[opts : OptionsPattern[]][var_String, countryslices_: All, 
  timeslices_: All, f_: Identity] := Query[
   (countryslices /. {All -> Identity, x_List :> KeyTake[x]}) /* 
     (barChart[opts, PlotLabel -> var][f[#]] &), 
   timeslices /. x_List :> KeyTake[x], 
   First@*First@*Values@* KeyTake[var]]

bcQ[opts : OptionsPattern[]][var_List, rest___] := 
 Row[Append[#[[All, 1]], #[[1, -1, 1, 1]]] & @
    Through[(bcQ[opts][#, rest] & /@ var) @ #], Spacer[10]] &

Examples:

bcQ[ImageSize -> 400, 
   Frame -> True, 
   Joined -> ({Mean @ #[[1]], Max @ #[[2]]} &), 
   ChartStyle -> "Rainbow"][{"x1"}] @ groupeddata

enter image description here

Take countries "c1", "c2" and "c4" and group by time and then by country:

bcQ[ImageSize -> 400, 
   Frame -> True, 
   Joined -> ({Mean @ #[[1]], Max @ #[[2]]} &), 
   ChartStyle -> "Rainbow"][{"x3"},  {"c1", "c2", "c4"}, All, Transpose]@groupeddata

enter image description here

Take multiple measures (say, "x1" and "x3") for countries {"c1", "c2", "c4"}:

bcQ[ImageSize -> 400, Frame -> True, 
   Joined -> ({Mean @ #[[1]], Max @ #[[2]]} &), 
   ChartStyle -> "Rainbow"][{"x1", "x3"},  {"c1", "c2", "c4"}]@groupeddata

enter image description here

Interactively select measures, times and countries using Manipulate:

tlist = Normal @ Keys @ Transpose @ groupeddata;
clist = Normal @ Keys @ groupeddata
xlist = First @ Normal @ Keys @ groupeddata[1, 1];

Manipulate[
 bcQ[ImageSize -> 400, 
    Frame -> True, 
    Joined -> ({Mean@#[[1]], Max@#[[2]]} &), 
   ChartStyle -> "Rainbow"][Sort@vars, countries, times, gorder]@groupeddata,
 {{vars, xlist[[{1}]], "measures"}, xlist, TogglerBar},
 {{countries, clist, "countries"}, clist, TogglerBar},
 {{times, tlist, "times"}, tlist, TogglerBar},
 {{gorder, Identity, "grouping order"}, 
     {Identity -> Column[{ "country", "time"}, Alignment -> Center], 
      Transpose -> Column[{"time", "country"}, Alignment -> Center]}}]

enter image description here

$\endgroup$
5
  • $\begingroup$ Really, the level of flexibility in producing different charts is amazing. I need a few days to digest your high-level code. I was busy preparing a different dataset with both irregular time and irregular variables to produce similar charts. I will post a new question today. $\endgroup$ Oct 26, 2021 at 9:25
  • $\begingroup$ Somehow, I have a problem with jdata = Join @@ Map[a |-> ... where a|-> seems to be the problem. Maybe there is a typo in that line. $\endgroup$ Oct 26, 2021 at 9:45
  • 1
    $\begingroup$ @TugrulTemel, try using \[Function] in place of |->. $\endgroup$
    – kglr
    Oct 26, 2021 at 9:56
  • $\begingroup$ Maybe you can correct another typo in your code above, which is a missing comma (,) after rawdata1 in the jdata = Join @@ Map[a |-> Dataset[AssociationThread[First @ a, #] & /@ Rest[a]]][{rawdata1 rawdata2, rawdata3, rawdata4}]; Then the code will work nicely $\endgroup$ Oct 26, 2021 at 10:29
  • 1
    $\begingroup$ I added the missing comma. The form |-> is added in version 12.2 (see Function) $\endgroup$
    – kglr
    Oct 26, 2021 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.