Excel Data-Mining Add-ins Tutorial krok po kroku

Tym razem zostanie przedstawiony Office Data Mining Add-In, czyli dodatek do Microsoft Excel, który służy do eksploracji danych. Eksploracja danych to pojęcie niezwykle szerokie, ale w ogólności oznacza odkrywanie pewnej ukrytej wiedzy z danych. Co prawda w bieżącym i następnych postach będzie trochę informacji teoretycznych, natomiast dociekliwych i zainteresowanych od razu odsyłam na wikipedię (http://en.wikipedia.org/wiki/Data_mining), która będzie dobrym punktem wyjścia do dalszych poszukiwań wiedzy teoretycznej i zgłębiania wiedzy. W tej serii postów zostanie przedstawione wykorzystanie jednego z dostępnych pakietów do analizy danych, a konkretnie dodatku do Excela Office Data Mining Add-In. Zostaną zaprezentowane jego możliwości w kontekście analizy danych oraz techniki i koncepcje wykorzystywane w procesie odkrywania wiedzy, które zostały w tym dodatku zaimplementowane, a które można wykorzystać również w innych, dostępnych na rynku pakietach i programach do analizy i eksploracji danych.

Dodatek ten nie jest predefiniowany i należy go doinstalować. Najnowsza wersja dla Excela 2013 znajduje się pod adresem:
http://www.microsoft.com/en-us/download/details.aspx?id=35578 Dodatek należy zainstalować, a następnie go skonfigurować. Instalacja jest standardowym procesem, natomiast aby skonfigurować dodatek i przygotować do pracy należy uruchomić “Server Configuration Utility”, który zostanie dodany w “Menu Start” do folderu “Microsoft SQL Server (wersja) Data Mining Add-Ins”, który zostanie utworzony podczas instalacji.

W pierwszym kroku należy się wyłącznie zapoznać z informacjami zamieszczonymi na ekranie oraz przejść dalej za pomocą przycisku “Next”.

Następnie należy podać adres serwera SSAS. Warto nadmienić, że dodatek ten to tak naprawdę interfejs graficzny, który pozwala na tworzenie struktur i modeli Data Miningowych dla SSAS w Excelu oraz który pozwala na komunikację danych pomiędzy arkuszami Excela i SSAS. Co prawda należy nadmienić, że do uruchomienia dodatku nie jest wymagany serwer Analysis Services (a więc nie jest potrzebna konfiguracja), ale bez dostępu do instancji Analysis Services dodatek będzie umożliwiał wyłącznie funkcje do przygotowania oraz czyszczenia danych. Wszystkie inne możliwości tego dodatku wymagają budowy modeli, a co za tym idzie niezbędny jest serwer Analysis Services. W tym oknie należy wskazać wyłącznie nazwę serwera SSAS oraz zatwierdzić przyciskiem “Next”.

W następnym kroku musimy zaakceptować to, że dodatek będzie mógł faktycznie korzystać z ww serwera i będzie mógł przygotowywane w Excelu modele na nim wykorzystywać i przetwarzać. Zmiany należy oczywiście zaakceptować i przejść dalej.

W kolejnym oknie musimy określić, czy modele tworzone podczas pracy z danymi mają być dodawane do istniejącej bazy danych SSAS, czy ma zostać utworzona w tym celu zupełnie nowa baza danych. Wybieramy pasujący nam scenariusz oraz zatwierdzamy przyciskiem “Next”.

Oprócz modeli tymczasowych, które zostają usuwane po zakończeniu pracy z arkuszem i danymi dodatek może również tworzyć stałe modele, które nie będą usuwane. W tym celu jednak musimy dać dostęp do serwera użytkownikowi, który będzie korzystał z Excela i tego dodatku do tworzenia takich modeli. Należy wskazać jego konto domenowe i zaakceptować zmiany.

Na tym kończy się konfiguracja dodatku, co powinno zostać również potwierdzone poniższym komunikatem.

Po zainstalowaniu oraz skonfigurowaniu dodatku jest on w pełni funkcjonalny i gotowy do użycia. Zanim jednak przejdziemy do prezentacji jego możliwości najpierw zadbamy o dane na których będziemy pracować. W celu zgromadzenia danych na potrzeby dzisiejszego postu wykorzystamy wiedzę z wcześniejszych wpisów o PQ. Ze strony CIA pobierzemy dane na temat telefonów komórkowych. Dane te można znaleźć w udostępnianym przez CIA Factbooku, który jest nieocenionym źródłem interesujących danych – zainteresowanych odsyłam na https://www.cia.gov/library/publications/the-world-factbook/ Wybrałem do dzisiejszych przykładów dwie tabele: “Internet hosts” oraz “Internet users”, które są dostępne w osobnych tabelach na ww stronie. Aby pobrać te dane wykorzystamy Power Query. Poniżej pokazuję jak wykorzystać napisany przeze mnie, gotowy skrypt, ale oczywiście zachęcam do spróbowania własnych sił i napisania własnego. Kod, który napisałem to:

let  
        Source1 = Csv.Document(Web.Contents("https://www.cia.gov/library/publications/the-world-factbook/rankorder/rawdata_2153.txt"),null,"#(tab)",null,1250),  
        ChangedType1 = Table.TransformColumnTypes(Source1,{{"Column1", type number}, {"Column2", type text}, {"Column3", type text}}),  
        RemovedColumns1 = Table.RemoveColumns(ChangedType1,{"Column1"}),  
        RenamedColumns1 = Table.RenameColumns(RemovedColumns1,{{"Column2", "Country"}, {"Column3", "InternetUsers"}}),  
        TableInternetUsers = RenamedColumns1,  
        Source2 = Csv.Document(Web.Contents("https://www.cia.gov/library/publications/the-world-factbook/rankorder/rawdata_2184.txt"),null,"#(tab)",null,1250),  
        ChangedType2 = Table.TransformColumnTypes(Source2,{{"Column1", type number}, {"Column2", type text}, {"Column3", type text}}),  
        RenamedColumns2 = Table.RenameColumns(ChangedType2,{{"Column1", "InternetHostsID"}, {"Column2", "Country"}, {"Column3", "InternetHostsCount"}}),  
        RemovedColumns2 = Table.RemoveColumns(RenamedColumns2,{"InternetHostsID"}),  
        TableInternetHosts = RemovedColumns2,  
        Source = Table.NestedJoin(TableInternetHosts,{"Country"},TableInternetUsers,{"Country"},"NewColumn"),  
        FinalData = Table.ExpandTableColumn(Source, "NewColumn", {"InternetUsers"}, {"NewColumn.InternetUsers"}),  
        ReplacedValue = Table.ReplaceValue(FinalData,",","",Replacer.ReplaceText,{"InternetHostsCount"}),  
        ReplacedValue1 = Table.ReplaceValue(ReplacedValue,",","",Replacer.ReplaceText,{"NewColumn.InternetUsers"}),  
        ChangedType = Table.TransformColumnTypes(ReplacedValue1,{{"InternetHostsCount", type number}, {"NewColumn.InternetUsers", type number}}),  
        SortedRows = Table.Sort(ChangedType ,{{"Country", Order.Ascending}}) , 
        AllData = SortedRows 
in  
        AllData

Aby go wykorzystać należy oczywiście wybrać w dodatku Power Query opcję “From Other Sources”, a następnie “Blank Query”.

Następnie należy przejść do zakładki “View” oraz wybrać “Advanced Editor”.

Teraz wystarczy już wkleić przygotowany kod.

Następnie nadać nazwę dla zapytania PQ i zaakceptować zmiany.

Do naszego arkusza zostaną wówczas załadowane interesujące dane. Z ww stron zostały pobrane dane ilościowe o operatorach sieci komórkowych oraz użytkowników. Dane te zostały połączone w jedną tabelę za pomocą nazwy państwa.

W taki oto sposób zostały pobrane dane potrzebne do dzisiejszego ćwiczenia.

Wracając do tematu dodatku Data Mining Add-In to po jego instalacji w Excelu pojawią się nowa zakładka: “DATA MINING”. Ponadto podczas pracy z danymi będzie dostępna jeszcze jedna zakładka w sekcji “Narzędzia tabel”, która nosi nazwę “Analyse”. Sam dodatek został podzielony na kilka grup funkcjonalnych, które odzwierciedlają różne zadania i techniki realizowane w procesie DM, czyli przygotowanie danych, modelowanie danych oraz walidację i weryfikacje modeli. Ponadto dodatek umożliwia przeglądanie przygotowanych już wcześniej modeli (“Browse”), tworzenie do nich dokumentacji (“Document model”) oraz bezpośrednie przygotowywanie zapytań typu DMX do tych modeli (“Query”). Istnieje również możliwość zarządzania tymi modelami (“Manage models”) – usuwanie, importowanie i eksportowanie, ale również procesowanie modeli. Przycisk “DMExcel” jest jednym z najważniejszych, ponieważ pozwala na faktyczne, fizyczne połączenie do bazy bazy SSAS, przed pracą z dodatkiem należy się upewnić czy połączenie do bazy jest aktywne. “Trace” natomiast służy do podglądu pracy dodatku oraz pozwala przechwytywać zapytania tworzące modele oraz zwracające wyniki pracy dodatku. W tym poście zostaną zaprezentowane możliwości tego dodatku względem przygotowywania danych. Pierwszą z dostępnych opcji jest “Explore Data”. Opcja ta umożliwia po pierwsze zebranie informacji na temat wybranego przez atrybutu – dystrybuanta, ale również pozwala na dyskretyzację (http://pl.wikipedia.org/wiki/Dyskretyzacja_(statystyka)). W przypadku odkrywania wiedzy dyskretyzacja jest niezmiernie ważna. W przypadku ogromnych zbiorów danych zmienne liczbowe mogą być reprezentowane przez ogromną liczbę unikalnych wartości o bardzo małej różnicy. W takim przypadku standaryzacja polega przeważnie na przekształcenie zmiennej ciągłej w dyskretną za pomocą utworzenia przedziałów dla tej wartości. Pozwala to w późniejszej analizie odkrywać wzorce dla podobnych wartości zmiennej ciągłej. Na przykład analizując wiek badanego najczęściej sama informacja o jego wieku jest mało istotna, a zdecydowanie bardziej użyteczna jest prezentacja wieku w przedziałach np.: niemowlak, dziecko, młodzież, dorosły itp. Umożliwia to właśnie “Explore Data”.

Po wybraniu tej opcji w pierwszym kroku należy zapoznać się z informacjami zamieszczonymi na ekranie monitora i przejść dalej.

W następnym kroku należy wybrać zakres danych, które zamierzamy badać. Można wybrać tabelę, lub zaznaczyć dowolny zakres danych w arkuszu.

W kolejnym kroku należy wskazać interesujący nas atrybut – można wybrać tylko jeden.

Po zaakceptowaniu wyboru zostaną zaprezentowane wyniki. W pierwszej sekcji (można ją wybrać klikając ikonę w lewym dolnym roku okna) można zobaczyć dystrybuantę, czyli rozkład zmiennej. Warto dodać, że wykres można skopiować do schowka i następnie wkleić i wykorzystać w Excelu (przycisk kopiuj w prawym dolnym rogu ekranu).

Druga zakładka (druga ikona od prawej w lewym dolnym rogu okna) pozwala na wspomnianą wyżej dyskretyzację. Jak widać na załączonej grafice można określić ilość grup na jaką dane powinny zostać podzielone, a wykres prezentuje liczbę przypadków pasujących do danego przedziału. (http://pl.wikipedia.org/wiki/Dyskretyzacja_(statystyka))

Dla celów ćwiczeń wybrano 6 przedziałów, a nastepnie za pomocą przycisku “Add new column” dodano stosowną informację do analizowanej tabeli.

Jak widać do tabeli została dodana nowa kolumna, która opisuje do którego przedziału pasuje podana w procesie dyskretyzacji wartość.

Druga opcja: “Clean Data” została podzielona na dwa: “Outliers” oraz “Re-Label”. Outliers, czyli wartości skrajne są to takie wartości, które w badanym atrybucie znacznie różnią się od pozostałych i mogą mieć niekorzystny wpływ na niektóre statystyki. Usuwanie wartości skrajnych bardzo często jest przydatne przy pozbywaniu się błędnych wartości i odchyleń. Jeżeli dane pochodzą z systemu, gdzie użytkownik sam wprowadza dane to pomyłki nie są wykluczone. Jeżeli w atrybucie wiek pojawi się wartość 170 to najprawdopodobniej jest to właśnie pomyłka. W przypadku usuwania wartości skrajnych można w szybki i automatyczny sposób określić pożądaną dziedzinę atrybutu oraz usunąć dane, które mogłyby zniekształcić rezultaty. W pierwszym roku opcji “Outliers” należy zapoznać się z opisem oraz przejść dalej.

Następnie, podobnie jak poprzednio, należy wybrać zakres danych.

Następnie należy wskazać interesujący atrybut.

W kolejnym korku można ustalić pożądaną dziedzinę danego atrybutu.

Po akceptacji przedziału prawdopodobnych wartości należy zdecydować co powinno stać się z rekordami, których wartości dla tego atrybutu mieszczą się poza wyznaczoną skalą. W tym kroku możemy wskazać konkretną wartość, którą zostanie zastąpiona dana wartość, zastąpić ją średnią arytmetyczną z wartości mieszczących się w dziedzinie, wskazać, że powinna być reprezentowana jako wartość pusta (null) lub zwyczajnie usunąć dany rekord z badanej próbki. W tym przypadku wybrano zamianę nieprawidłowej (skrajnej) wartości na średnią.

W kolejnym kroku możemy zdecydować, że oryginalny atrybut powinien zostać zastąpiony danymi poprawnymi, możemy poprawione dane dołączyć do bieżącego zestawu danych lub zapisać wszystkie dane wraz z poprawianymi wartościami jako nowy arkusz. Tutaj zdecydowano się dołączyć informację o poprawnej wartości jako nową kolumnę.

Jak widać na poniższej grafice w przypadku, kiedy dana wartość nie mieściła się w pożądanym zakresie została ona zamieniona na wartość średnią. W pozostałych przypadkach dane się nie zmieniły.

Drugą opcją czyszczenia danych jest “Re-Label”, która jak sama nazwa wskazuje pozwala na zamianę poszczególnych wartości.

Po wybraniu tej opcji standardowo należy zapoznać się z informacjami wstępnymi oraz przejść dalej.

Następnie należy wybrać interesujący nas zakres danych.

Oraz wybrać atrybut do analizy.

Po zaakceptowaniu ukaże się okno prezentujące wszystkie unikalne wartości w zbiorze wraz z ich liczebnością. Taka opcja może być rpzydant szczególnie przy zamianie wartości pustych (jak w tym przypadku) lub innych wartości, które ekspert podczas oględzin danych uznać może za nieprawidłowe. W tym przypadku zostały zamienione wartości puste (null) na wartość średnią, która została pobrana z wcześniejszego kroku analizy.

Podobnie jak we wcześniejszych krokach tutaj również należy wybrać gdzie ma zostać zapisany wynik operacji. W tym przypadku do tej samej tabeli jako nowa kolumna.

Analogiczne operacje przeprowadzono również w przypadku drugiej tabeli. Poniższa grafika prezentuje wartości podstawowej statystyki, czyli średniej arytmetycznej dla danych oryginalnych oraz danych po przygotowaniu wykorzystując techniki eksploracji wiedzy. Jak widać średnia dla “internet hosts” dość znacznie rózni się od średniej “internet hosts *”, która odzwierciedla dane bez wartości pustych oraz skrajnych.Podobnie sytuacja wygląda z drugą tabelą.

Ostatnią opcją dostępną w sekcji “Data preparation” jest “Sample Data”, która służy do przygotowywania reprezentacyjnej próbki danych do modelu. Po skorzystaniu z opcji należy się zapoznać z informacjami na temat tej opcji i przejść dalej.

Następnie należy określić zakres danych. Tutaj niespodzianka ponieważ można proso z tego miejsca połączyć się na przykład do relacyjnej bazy danych i stamtąd bezpośrednio przygotować próbkę danych.

W następnym kroku możemy określić czy dane do próbki mają zostać wybrane losowo czy chcemy wybrać konkretne dane (z konkretnego przedziału) na podstawie rozkładu zmiennej. W przypadku ćwiczenia wybrano pierwszą opcję.

W kolejnym kroku należy wskazać jaki procent populacji ma stanowić nasza próbka – w tym przypadku 70% populacji.

Na samym końcu należy wskazać gdzie dane, które zostały wybrane do próbki mają zostać załadowane oraz należy wskazać nazwę dla tabeli zawierającej dane wybrane do próbki i dane, które do tej próbki nie zostały wybrane.

Mam nadzieję, że już na tym etapie widać, że dodatek Microsoft Data Mining Add-In jest fantastycznym narzędziem do prowadzenia eksploracji danych. Zaprezentowane wyżej techniki służyły do przygotowywania danych i ich czyszczenia, a to dopiero początek. Już teraz zapraszam do czytania kolejnych postów, które będą prezentowały konkretne użycie dostępnych algorytmów data miningowych.