Centrālā statistikas pārvalde apkopo dažāda veida datus, kurus reizēm ir parocīgi iegūt Excel darba lapā. Turklāt pieslēdzoties vēlamajiem datiem kā ārējam datu avotam un taupot laiku saistībā ar datu atjaunošanas porcesu. Lūk, kā to izdarīt.
Ielūkojos pieejamajos datos https://data.csb.gov.lv/ un izvēlējos aplūkot mājokļa cenu indeksu, kur sanāca visnotaļ interesants gala rezultāts.
Vispirms atlasīju visus par to esošos datus.
Kad tabula izveidota, lapas apakšā jāatrod iespēja to iespējot lietojumprogrammā.
Tur redzama Tīmekļa adrese (URL) un JSON vaicājums.
Šie divi parametri ir tie, kas būs jāizmanto, lai tabulu ar atlasīto saturu pieprasītu no CSP lapas caur Excel vai Power BI. Turklāt šāda darbošanās būs laba ar to, ka jaunākos datus varēs iegūt tos atjaunojot caur attiecīgo programmu – tātad ietaupīs laiku.
Attiecīgā piemēra turpinājums notiek Excel 2016, izmantojot sadaļu Get&Transform, Power BI tas notiek līdzīgi, bet vecākām Excel versijām jāinstalē Power Query Add-in.
Dodoties uz Data -> New Query -> From Other Sources -> From Web atveras logs, kurā iekopē attiecīgo tīmekļa adresi.
Power Query Editor logā sadaļā View atver Advanced Editor, kur jāpielāgo tur redzamā funkcija Web.Contents, lai tā izmantotu arī JSON vaicājumu. To izdara izmantojot funkcijas parametru Content un attiecīgo JSON vaicājumu norādot binārā formā ar funkciju Text.ToBinary.
Tomēr vispirms jāpielāgo JSON vaicājumā esošo tekstu ar papildus pēdiņām un vaicājumā formātu norādot json. Ātri to būs iespējams izdarīt, piemēram, programmā Notepad ++ un aizvietojot ” ar “”.
Visbeidzot JSON vaicājumu var piešķirt mainīgajam un lūk, kā vajadzētu izskatīties rezultātam. Jāpievērš uzmanība visām pēdiņām un komatiem!
|
1 2 3 4 5 6 7 8 9 10 11 |
let Query = "{ ""query"": [], ""response"": { ""format"": ""json"" } }", Source = Json.Document(Web.Contents("http://data1.csb.gov.lv/api/v1/lv/ekfin/PCI/isterm/PC070c.px", [Content=Text.ToBinary(Query)])) in Source |
Pēc tam atliek izvēlēties attiecīgo komponenti tālākai aplūkošanai, šajā gadījumā klikšķis uz List iepretim Data. Turpmākie soļi var atšķirties atkarībā no izgūtās tabulas.
Listi pārveido par tabulu.
Izvērš attiecīgo kolonnu.
Un pēc tam arī nākošās kolonnas. Der pievērst uzmanību situācijām, kad vienā kolonnā veidojas saturs, kuru pēc tam vajadzēs sadalīt pa vairākām. Tādā gadījumā pēc izvēles Extract Values… papildus jānorāda arī atdalītājs.
Izmantojot atdalītāju sadala datus pa kolonnām.
Pielāgo kolonnu nosaukumus, ielūkojoties un salīdzinot ar saturu CSB lapā, bet pārējais jau ir atkarīgs no vēlmēm. Izvēlējos aplūkot mājokļa cenu indeksus attiecībā pret 2010.gadu, kad bijis zemākais cenu līmenis attiecīgajā periodā.
Attiecīgās tabulas dati grafiski izskatās šādi. Zemāk pieejams kods ar visiem izdarītajiem pārveidojumiem.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
let Query = "{ ""query"": [], ""response"": { ""format"": ""json"" } }", Source = Json.Document(Web.Contents("http://data1.csb.gov.lv/api/v1/lv/ekfin/PCI/isterm/PC070c.px", [Content=Text.ToBinary(Query)])), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "values"}, {"key", "values"}), #"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"values", each Text.Combine(List.Transform(_, Text.From)), type text}), #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"key", each Text.Combine(List.Transform(_, Text.From), ":"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "key", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"key.1", "key.2", "key.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"key.1", type text}, {"key.2", type text}, {"key.3", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"key.1", "Year&Q"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([key.3] = "I2010")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",".",",",Replacer.ReplaceText,{"values"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"values", type number}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[key.2]), "key.2", "values", List.Sum), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"H1", "Mājokļi (kopā)"}, {"H11", "Jaunie mājokļi"}, {"H12", "Lietotie mājokļi"}, {"key.3", "Index"}}) in #"Renamed Columns1" |