Mapa Polski w Excelu i Power BI Desktop

Mapa w Excelu i Power BI

Posted on Posted in Raport, wykresy

W pierwszym noworocznym wpisie, chciałbym poszerzyć temat jednego z najczęściej czytanych artykułów na blogu pod tytułem Mapa Polski w Excelu (kartogram). W poście tym przedstawiłem sposób na stworzenie mapy w postaci kartogramu dla województw w Polsce. Dziś pokażę jak poszerzyć kolekcję posiadanych map i wzbogacić ją o kolejne podziały geograficzne.

 

Wykorzystanie plików SHP

Znalezienie pliku SVG w postaci interesującej nas mapy, z określonym podziałem geograficznym często bywa niemożliwe. W tym wypadku należy skorzystać z pewnej furtki. Wystarczy posiadać format mapy o rozszerzeniu SHP do pobrania np. z Global Administrative Areas lub CODGiK. Dla potrzeb naszej prezentacji pobierzemy dane z CODGiK o oznaczeniu *PRG - jednostki administracyjne. Po rozpakowaniu folderu powinniśmy zobaczyć następujące pliki. Nas najbardziej interesują te o rozszerzeniu SHP.

 

Rozpakowane pliki do mapy

 

W kolejnym kroku, wybieramy kształt mapy jaki chcemy przedstawić w postaci kartogramu i przenosimy jego ikonkę, w postaci pliku SHP, na stronę mapshaper.org za pomocą, której "uformujemy" granice poszczególnych obszarów. Po przeniesieniu pliku pojawi się nam obraz wybranej mapy.

 

Mapa w mapshaper

 

 

Następnie klikamy na ikonkę Export, w prawym górnym rogu i wybieramy opcję SVG.

 

Eksport do pliku SVG

 

Z tak przygotowanym plikiem SVG postępujemy analogicznie jak we wpisie Mapa Polski w Excelu (kartogram) tzn. otwieramy w Inkscape --> zapisujemy jako plik EMF --> plik EMF otwieramy w Excelu jako obraz --> usuwamy grupowanie poszczególnych kształtów.

 

Wykorzystanie plików DBF

Kolejny krok, do stworzenia mapy-kartogramu, zakładał przypisanie właściwych nazw do poszczególnych kształtów mapy. W tym celu korzystaliśmy z pliku SVG, który otwieraliśmy w Excelu jako tabelę XML, aby wydobyć z niej nazwy poszczególnych województw. Następnie za pomocą makra, nadawaliśmy poszczególnym kształtom mapy, nazwy wyszczególnione z tabeli XML. Tym razem za źródło naszych danych posłuży plik o innym rozszerzeniu a mianowicie DBF. Plik ten, dla wybranego przez nas obszaru, otwieramy na stronie DBF Converter, za pomocą której przekonwertujemy format pliku do rozszerzenia CSV.  Gotowy plik CSV, otwieramy w Excelu za pomocą importu danych z pliku tekstowego.

 

Rozpakowanie pliku CSV do Excela

 

Na tym etapie może się zdarzyć, że nasz plik będzie miał problemy z obsługą polskich znaków ze względu na wybrany język kodowania.

 

Język kodowania pliku

 

Jeżeli zaimportowany plik nie czyta polskich liter warto wybrać jeden z przedstawionych wariantów kodowania:

65001 : Unicode (UTF-8)
28592 : Środkowoeuropejski (ISO)
1250 : Środkowoeuropejski (Windows)

 

Import pliku po wybraniu właściwego kodowania

 

W kolejny kroku wybieramy sposób rozdzielania poszczególnych pól np. przecinek i importujemy tekst do Excela.

 

Potencjalne problemy

Niestety podane rozwiązanie nie jest wolne od wad. Problem dotyczy kształtów, które po rozgrupowaniu tworzą daną mapę. Otóż zdarza się, że pojedynczych kształtów na mapie jest więcej aniżeli faktycznie przypisanych im nazw np. w Polsce mamy 380 powiatów a kształty ponumerowane w Excelu tworzą ciąg od Freeform4 - Freeform412. Skąd ta różnica ? Wynika ona z budowy mapy, na której zostały użyte dodatkowe, zazwyczaj bardzo małe, kształty celem np. dopełnienia kształtu danego obszaru.

 

Kształty powiatów w Excelu

 

Aby możliwie jak najbardziej wyczyścić mapę z dodatkowych kształtów można posłużyć się jednym z dwóch rozwiązań:

1. Uprościć kształt mapy korzystając z przycisku Simplify na Mapsharper. Po naciśnięciu przycisku zaznaczyć opcję Prevent shape removal, wybrać metodę uproszczenia Weighted area i nacisnąć Apply. Następnie suwakiem zmniejszyć wartość szczegółowości zarysów mapy. Przykładowo po zmniejszeniu wartości do 0% nasza mapa będzie wyglądała jak poniżej.

 

Uproszczona mapa

 

2. Identyfikacja dodatkowych kształtów za pomocą makra. Używając makra możemy nie tylko stworzyć listę nazw wszystkich dostępnych kształtów ale też podać ich parametry. Na tej podstawie sortując elementy np. po ich rozmiarze możemy łatwo znaleźć te, które zostały dodane wyłącznie w celach kosmetycznych (makro GetShapeProperties wklejamy do arkusza z mapą a następnie sortujemy po parametrze Height).

 

Kształty do usunięcia wyszczególnione przez makro

 

Aby upewnić się czy zaznaczone kształty faktycznie nie są częścią mapy, podglądamy je w Okienku zaznaczenia, które aktywujemy z karty Narzędzia główne --> Edytowanie -->Znajdź i zaznacz.

 

Bonus Tip

Mapę w postaci kartogramu możemy również wykonać korzystając z innego programu Microsoftu - Power BI Desktop (PBD). Jest to darmowy program BI do analizy danych, o którym wspominałem we wcześniejszych wpisach. W PBD kartogram można stworzyć na 2 sposoby.

 

1. Filled Map

Po pobraniu programu instalujemy go na komputerze i za pomocą przycisku Get Data pobieramy przygotowaną wcześniej tabelę z Excela.

 

Import danych z Excela do Power BI

 

W tabeli powinny znajdować się dane charakteryzujące obszar geograficzny, który chcemy pokazać oraz przypisaną do niego wartość.

 

Excel do Power BI

 

Po załadowaniu danych wykonujemy najważniejszą operację bez, której kartogram nie zadziała. Należy przypisać kategorie danych geograficznych do wybranej kolumny tabeli. W tym wypadku kolumnę Powiat określamy jako County.

 

Kategoria dla danych w Power BI

 

 

Następnie z zakładki Visualizations wybieramy opcję Filled Map i metodą drag&drop przenosimy na nią nasze dane z tabeli. W efekcie uzyskujemy mapę w postaci kartogramu dla wybranego obszaru.

 

Filled map w Power BI

 

2. Shape Map Visual

Jest jeszcze drugi sposób na mapę-kartogram w Power BI. Tym razem skorzystamy z ukrytej opcji Shape Map Visual, dzięki której będziemy mogli osiągnąć efekt bardzo zbliżony do propozycji kartogramu w Excelu. Aby aktywować Shape Map Visual należy wejść w zakładkę Opcji Power BI.

 

Opcje Power BI

 

Następnie z grupy Preview Features wybrać Shape Map Visual, zamknąć PBD i otworzyć go ponownie.

 

Aktywacja shape map visual

 

Rezultatem wprowadzonych zamian będzie niebieska ikonka mapy kartogramu w zakładce Visualizations. Aby przedstawić dane za pomocą Shape Map Visual należy kliknąć na niebieską ikonkę mapy i przenieść na nią dowolne dane. W rezultacie tej operacji, pojawi się nam szara mapa USA. Następnie za pomocą ikonki Formatowanie wybieramy zakładkę Shape, w której mamy do wyboru opcje:

 

Formatowanie w Power BI

 

- Map (tutaj po kliknięciu na strzałkę mamy możliwość wyboru kilku map z danym krajem)

- View map keys (pozwala na wyświetlenie nazw poszczególnych kształtów). Pole to jest nam szczególnie potrzebne jeśli chcemy aby nasz kartogram zadziałał. Tabela z danymi, którą importujemy dla danej mapy musi posiadać kolumnę identyfikującą obszary po dowolnym "id" z kolumny Map keys.

 

Opcje Shape Map

 

- Projection (różne sposoby na prezentację naszej mapy)

Tak wygląda przykładowa mapa stworzona dla Kanady. Pamiętajcie jednak, aby przy tworzeniu mapy tym sposobem, również przypisać kategorię geograficzną do kolumny posiadającej nazwę danego miejsca.

 

Mapa kanady w Power BI

 

Niestety, między innymi mapa Polski, nie została udostępniona w programie, ale istnieje sposób na jej dodanie. Aby dodać dowolną mapę do Shape Map Visual należy skorzystać z przycisku + Add Map znajdującego się w zakładce Shape. Ważne jest jednak aby mapa, którą importujemy była w formacie TopoJSON. Pod tym linkiem możecie znaleźć gotowy zbiór różnych map, którymi za darmo dzielą się użytkownicy m.in. PowerBI. Zbiór ten nie posiada mapy Polski ale przy pomocy znanej już nam strony Mapshaper oraz programu QGIS możemy ją stworzyć samodzielnie (użycie programu QGIS nie zawsze jest obowiązkowe i korzystamy z niego tylko wtedy gdy nasze dane posiadają układ współrzędnych inny niż EPSG:4326 - WGS 84). Na początek otwieramy program QGIS i z zakładki Warstwa --> Dodaj warstwę wybieramy Dodaj warstwę wektorową.

 

Dodanie warstwy w QGIS

 

Następnie w pojawiającym się okienku wybieramy plik z danymi obszaru, który chcemy pokazać o rozszerzeniu SHP. W tym miejscu warto na wszelki wypadek zapamiętać informację o kodowaniu pliku.

 

Dodaj warstwę wektorową w QGIS

 

Po zaimportowaniu pliku, w prawym dolnym rogu programu, sprawdzamy jaki układ współrzędnych mają nasze dane. Jeżeli opisany jest on jako EPSG:4326 pozostałe kroki związane z praca w programie QGIS możemy pominąć i przejść od razu do punktu związanego z Mapsharper.

 

Układ współrzędnych w QGIS

 

Jeżeli jednak układ współrzędnych jest inny od podanego to klikamy na ikonkę warstwy i zaznaczmy opcję Zapisz jako.

 

Zapisywanie warstwy w QGIS

 

Następnie w pojawiającym się okienku dokonujemy następujące zmiany:

- ustalamy nową ścieżkę zapisu dla plików naszej mapy

- zmieniamy układ współrzędnych na EPSG:4326 - WGS 84

- zmieniamy opcję rozmiaru warstwy na YES

- klikamy OK i zamykamy program QGIS

 

Zapisywanie warstwy wektorowej

 

Rezultatem dokonanych przez nas zmian będzie nowy zbiór plików ESRI Shapefile, który utworzy się w podanym przez nas wcześniej miejscu.

 

Nowe pliki ESRI Shapefile

 

Następnie zaznaczamy wszystkie nowo powstałe pliki i przenosimy je na stronę Mapsharper.

 

Przeniesienie plików na mapshaper

 

W rezultacie pojawi się nam mapa Polski z podziałem na województwa. Kiedy klikniemy na stronie przycisk Export z pojawiającego się okienka wybieramy format plików TopoJSON.

 

Export plików jako topojson

 

W naszym wypadku po kliknięciu przycisku Export pojawi się błąd związany z kodowaniem pliku DBF, dla tego też w okienku Command line options wpisujemy "encoding=1250" (to kod, o którym wspominałem kilka linijek wcześniej).

 

Encoding błąd QGIS

 

Podanie kodowania pliku QGIS

 

Kiedy tym razem klikniemy Export nasza mapa wyeksportuje się w postaci pliku TopoJSON. Teraz nie pozostaje nam nic innego jak tylko dodać mapę do PBD za pomocą przycisku +Add Map oraz utworzyć tabelę z danymi w oparciu o nazewnictwo pobrane z View map keys (danych z View map keys nie można skopiować dla tego pobieramy je z nowego pliku DBF przetworzonego na CSV). Nasza mapa jest gotowa.

 

Mapa Polski w Power BI

 

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *