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.
- The
Barchartproduced using the abovecodedoes not group four countries across four years. You will see it when you plot the bar chart. - Country labels for each time series group are not shown.
- 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 singleBarchartfor 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. - 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.
tsC1x3 = jdata[Select[#country == "c1" &], "x3"]$\endgroup$BarChart[jdata[Select[#country == "c1" &], "x3"]]works fine. For all the countriesjdata[GroupBy["country"], BarChart[ #]&, "x3"]works fine. If I change grouping toGroupBy["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$