Data Mining, czyli odkrywanie kolejnych dodatków Microsoft Excel

Sławomir Drzymała | Business Intelligence | 5 czerwca 2014

Czas na analizę danych oraz na odkrywanie zgromadzonej w nich wiedzy. Pozostajemy w Microsoft Excel, ale rozpoczynamy zupełnie nowy rozdział. Czas na Data Mining, czyli odkrywanie wiedzy.

W poprzednich postach omówiony został dodatek Power Query. Za jego pomocą można pobrać dane z niemalże dowolnego miejsca oraz wstępnie je przygotować do dalszej pracy. W tym poście natomiast nadal będzie mowa o Excelu, ale tym razem zostanie 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ę, która będzie dobrym punktem wyjścia do dalszych poszukiwań wiedzy teoretycznej i zgłębiania wiedzy.

Data mining – Excel jako narzędzie w odkrywaniu 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. Techniki i koncepcje wykorzystywane w procesie odkrywania wiedzy, które zostały w tym dodatku zaimplementowane, można wykorzystać również w innych, dostępnych na rynku pakietach czy programach do analizy i eksploracji danych.

Instalacja i konfiguracja dodatku SQL Server Data Mining Add-in

Dodatek ten nie jest predefiniowany i należy go doinstalować, a następnie go skonfigurować. Można go pobrać z tej strony. Podczas konfiguracji należy uruchomić aplikację „Server Configuration Utility”, która zostanie dodana podczas instalacji w „Menu Start” do folderu „Microsoft SQL Server (wersja) Data Mining Add-Ins”.

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

Konfiguracja dodatku do odkrywania wiedzy. Welcome.

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. Do uruchomienia dodatku nie jest wymagany serwer Analysis Services (a więc nie jest potrzebna konfiguracja), ale bez dostępu do instancji jego funkcje zostaną ograniczone do przygotowania oraz czyszczenia danych. Wszystkie inne możliwości tego dodatku wymagają budowy modeli, a co za tym idzie, serwera Analysis Services. W kolejnym oknie należy wskazać wyłącznie nazwę serwera SSAS oraz zatwierdzić przyciskiem „Next”.

Konfiguracja dodatku do odkrywania wiedzy. Analysis Services database connecting.

W następnym kroku musimy potwierdzić, że dodatek Data Mining będzie mógł korzystać z w/w serwera i będzie mógł za jego pośrednictwem wykorzystywać i przetwarzać przygotowywane w Excelu modele. 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”.

Konfiguracja dodatku do odkrywania wiedzy. Database creation rules.

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 musimy dać użytkownikowi, który będzie korzystał z Excela, dostęp do serwera oraz 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.

Konfiguracja dodatku do odkrywania wiedzy. Confirmation.

Przygotowanie do pracy z dodatkiem Data Mining

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 PowerQuery.

Ze strony CIA pobierzemy dane na temat użytkowników Internetu. Dane te można znaleźć w udostępnianym przez CIA Factbooku, który jest ciekawym źródłem danych. Wybrałem do dzisiejszych przykładów dwie tabele: „Internet hosts” oraz „Internet users”, które są dostępne w osobnych tabelach na w/w stronie. Aby pobrać te dane wykorzystamy Power Query. Poniżej pokazuję jak wykorzystać napisany przeze mnie, przykładowy 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”.

Power Query i Data Mining

Następnie należy przejść do zakładki „View” oraz wybrać „Advanced Editor”. Teraz wystarczy już wkleić przygotowany kod.

Using the Data mining adds

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

Data imports to SQL Server Data Mining Add

Do naszego arkusza zostaną wówczas załadowane interesujące dane. Z w/w 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.

Imported data in Excel

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

Funkcjonalności dodatku Data Mining w Microsoft Excel

Wracając do tematu dodatku Data Mining 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.

Przygotowanie danych za pomocą funkcji Explore Data

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ę. W przypadku odkrywania wiedzy dyskretyzacja jest niezmiernie ważna. Zmienne liczbowe w ogromnych zbiorach danych 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”.

Data mining techniques in Excel - Explore Data

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

Work with Explore Data feature in Data Mining add-in for Excel.

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

Data range for the data mining.

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

Atributes of data mining.

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).

Data exploration results.

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.

Microsoft tools for data mining.

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

Data mining tasks - Add new column

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

Data exploration results.

Oczyszczanie danych za pomocą funkcji Clean Data

Druga opcja: „Clean Data” została podzielona na dwa: „Outliers” oraz „Re-Label”.

Usuwanie wartości skrajnych

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.

Data mining techniques in Excel - Outliers

Następnie, podobnie jak poprzednio, należy wybrać zakres danych i wskazać interesujący atrybut.

Work with outliers feature.

W kolejnym kroku 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ą.

Data mining - outliers features configuration

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ę.

Using the data mining tools in Excel.

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.

Outliers feature results.

Edycja nieprawidłowych wartości

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

Data mining techniques in Excel - Re-Label

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.

Choosing atributes for the data mining process.

Po zaakceptowaniu ukaże się okno prezentujące wszystkie unikalne wartości w zbiorze wraz z ich liczebnością. Taka opcja może być przydatna 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.

Efekty pracy nad danymi

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óżni się od średniej „internet hosts *”, która odzwierciedla dane bez wartości pustych oraz skrajnych. Podobnie sytuacja wygląda z drugą tabelą.

Preparing data for creation of data mining models.

Próba reprezentatywna dla modelu

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 – można prosto z tego miejsca połączyć się na przykład do relacyjnej bazy danych i stamtąd bezpośrednio przygotować próbkę danych.

Data mining models - Sample data definition

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.

Data mining

Podsumowanie

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.

Więcej na ten temat: Microsoft Power Query edytor kodu – zobacz jak pisać skrypty w języku M

Autorem jest jeden z największych pasjonatów Business Intelligence w JCommerce.

Zapraszamy do zapoznania się z prywatnym blogiem od Sławka, gdzie możesz znaleźć więcej ciekawych artykułów dot. Business Intelligence.

Autorem wpisu jest:

specjalista Business Intelligence

Konsultant Business Intelligence, który na co dzień projektuje i implementuje rozwiązania BI w Microsoft SQL Server, korzystając zarówno z klasycznych narzędzi, jak i funkcjonalności Power BI. Posiada doświadczenie w pracy z Oracle, SAP, IBM, QlikView oraz Pervasive.

Dodaj komentarz: