Zmiana formatu tabeli z szerokiej na długą (Power Query)

Są takie momenty, że mamy tabelę z danymi ale…coś nam w niej nie pasuje. Jeśli chcemy stworzyć tabelę przestawną albo wykres, to dane (wymiary) nie dają się nam łatwo pogrupować lub przefiltrować. Zamiast w kolumnach, pasowałyby w wierszach, ewentualnie na odwrót. W takich przypadkach nieocenioną pomocą będzie zmiana formatu tabeli z szerokiej na długą. Na pierwszy rzut oka wygląda jak zwykła transpozycja…ale jest czymś znacznie więcej. Użyjemy do tego Power Query, ale dla tych, co nie mają z nim doświadczenia, obiecuję, że będzie bezboleśnie 😉 Zapraszam do lektury!

Zmiana formatu i różnice w stosunku do transpozycji

Po pierwsze, sprecyzujmy z czym mamy do czynienia i dlaczego mamy tego używać. W języku polskim nie ma chyba do końca prostego tłumaczenia z angielskiego. Osobiście lubię określenie, jak zmiana formatu tabeli z szerokiej na długą (ewentualnie na odwrót) i tego będę się trzymać. W języku angielskim w Excelu, ale także szerzej, w tzw. data science czyli danologii, spotkamy określenia jak pivot, unpivot (ps. – nie ma nic wspólnego z pivot tables czyli tabelami przestawnymi), melting itd. Co ciekawe, czasem ta sama nazwa np. unpivot w Power Query odpowiada analogicznej operacji określanej jako pivot w innym toolu (przykład to Tableau). Warto być zatem czujnym 😉

Przykład

Dlaczego zmiana formatu z szerokiego na długi jest fajna? Bo w dużym stopniu tłumaczy, co „wizualnie” robimy z tabelą danych. Zobaczmy na bardzo prosty przykład poniżej. To nasza tabela wyjściowa. Każdy wiersz to informacje na temat innej osoby. Cechy jak wiek, wzrost itd. są umieszczone w oddzielnych kolumnach.

A teraz tabela po zmianie.

Wyjaśnijmy sobie, co się stało. Zasadniczo, dalej mamy te same informacje. Dlaczego zatem jest więcej wierszy i mniej kolumn? Dlaczego imię z pierwszej kolumny jest teraz powtórzone w każdym wierszu?

Dzieje się tak dlatego, że „skompresowaliśmy” informacje z kilku do dwóch kolumn. Jedna to nazwy kolumn wzięte z poprzedniej tabelki (Wiek, Wzrost itd). A druga to odpowiadająca im wartość, jak liczba czy nazwa dla danej cechy (np kolor oczu). Innymi słowy, każdy wiersz dla danej osoby został „rozbity” do kilku, aby „upakować” te same informacje. Stąd też wartość w kolumnie Imię została powtórzona, gdyż to akurat się nie zmienia. Dodajemy wiersze, ale jedna wartośc, czyli Imię pozostaje to samo dla danej grupy.

W ten sposób cała tabela stała się węższa (mniej kolumn), ale dłuższa (więcej wierszy). Stąd mamy nazwę 😊 Można oczywiście taką operację wykonać w drugą stronę, ale raczej częściej będzie potrzebny właśnie ten kierunek. O tym, dlaczego, piszę poniżej.

Jaka różnica w stosunku do transpozycji?

Myślę, że po powyższym przykładzie już trochę rozumiecie o co chodzi 😀 W przypadku transpozycji, robimy znacznie prostszą rzecz. Obracamy tylko naszego klocka…hmm…pamiętacie Tetris? 😊 i nie zmieniamy nic więcej. Finalnie liczba zapełnionych komórek także nie zmienia się. Czasem też jest to przydatne, ale w tym artykule chciałem tylko zaznaczyć, jaka jest różnica w stosunku do zmiany układu tabeli. Poniżej zamieszczam przykład, jak wyglądałaby transpozycja poprzedniej tabelki.

Po co zmiana formatu tabeli z szerokiej na długą?

Teraz nieco bardziej trudne pytanie, po co to robimy? Ktoś mógłby powiedzieć, ze taki format jest mniej przejrzysty, są powtórzenia itd. Z jednej strony tak, ale tak jest zasadniczo dla nas…dla ludzi. Dla komputerów czyli maszyn (wiem…brzmi jak z Terminatora 🙂 ) sytuacja jest odwrotna. Zazwyczaj zmiana formatu z szerszego na długi sprawia, że możemy na przykład znacznie łatwiej grupować dane (i tworzyć tabele przestawne), wykresy, filtrować informacje po wymiarach itd.

Z tego też powodu nieraz napotykamy tabele, które są bardziej przejrzyste na pierwszy rzut oka, ale dla analizy danych ten format już tak się nie sprawdza. Typowy przykład to dane statystyczne, które zbiera się po wywiadach i np. każda kolumna to odpowiedź na jakieś inne pytanie.

Mój przykład

Tak też było w jednym z przypadków, w którym tworzyłem dane pod dashboard w dash corner. Przy okazji zapraszam…link 🙂  Tabela wyglądała jak poniżej. Zasadniczo miała jeszcze dodatkowe rzeczy, które szybko ręcznie usunąłem, ale pozostawał taki format. W skrócie są to emisje Co2 dla Europy. Mamy zatem nazwy krajów w kolumnie A i później w każdej kolejnej inny typ emisji. Na zrzucie ekranu nie widać ich wszystkich, ale są one w kolumnach od B do L. Każdy wiersz to jeden kraj. Poniżej zamieszczam też plik do pobrania, jeśli chcecie przećwiczyć u siebie.

Niby fajnie? Już tu jednak powstają problemy. Co zrobię, jeśli będę chciał użyć typu emisji jako filtra na dashboardzie? Tu każdy jest w oddzielnej kolumnie. Taki format odpada. Po drugie, może się pojawić problem z sumowaniem emisji per kraj. Co prawda mogę szybko wstawić kolumnę z sumą. Ale to trochę mniej elastyczne rozwiązanie, Szczególnie, jeśli chciałbym później grupować emisje po ogólniejszej kategorii, jak produkcja energii lub tym podobne.

W takich przypadkach z pomocą przychodzi zmiana formatu tabeli z szerokiej na długą 😊

Odpalamy Power Query

Aby to zrobić, potrzebujemy wykorzystać Power Query. Jeśli nie korzystaliście nigdy z tego dodatku, to nie martwcie się. Będzie bezobsługowo. Nie będę wchodził w szczegóły. Pokażę krok po kroku, jak sobie pomóc 😊

Opcji startu jest kilka, ale najpierw musimy wczytać dane. Ja zrobię to, jak poniżej. Z poziomu Excela otwieramy zakładkę Dane i Pobierz Dane, póżniej Z Pliku – Ze Skoroszytu i szukamy naszego pliku na dysku.

Otwieramy:

Pojawia się następujące okno. Po lewej wybieramy zakładkę z pliku, z której chcemy wczytać dane. U mnie są dwie. Wybieram CO2_emissions.

Po czym na dole klikamy na Przekształć dane

Otwiera się edytor Power Query z podglądem naszego pliku. Teraz nie będzięmy przechodzić przez całą nawigację, tylko skupimy się na naszym zadaniu. W dużym skrócie poruszanie się przypomina to ze zwykłego Excela. U góry mamy wstążkę z głównym menu. Uwaga…nie działa CTRL + Z ! 😊 Do cofnięcia jakiejś akcji używamy menu po prawej stronie w Zastosowane Kroki.

Robimy zmianę formatu tabeli

Dane mają dość prostą strukturę. Każdy wiersz to jeden kraj (nazwa w pierwszej kolumnie) i paręnaście kolumn. W każdej jest inna liczba odpowiadająca innemu typowi emisji Co2. W tym konkretnym przykładzie potrzebuję, aby informacje (liczby z kolumn) zostały przeniesione do wierszy. Zmiana nastąpi dla wszystkich poza pierwszą kolumną, gdzie mam nazwy krajów. Chodzi o to, że nazwa kraju nadal ma zostać w pierwszej kolumnie. Tu nic nie transformujemy. Klikam zatem na kolumnę z nazwami krajów. I dopiero wtedy wybieram z Menu górnego Przekształć, a póżniej Anuluj przestawienie kolumn – Anuluj przestawienie innych kolumn. Z uwagi na wybór opcji ważna jest powyższa kolejność…czyli zaznaczam kolumnę, której nie przekształcam.

PS. W angielskiej wersji ta opcja ma nazwę Unpivot columns. Szczerze, polskie tłumaczenie nie przypadło mi do gustu 😉 Nic nie anulujemy…no ale tak mają 😉

I oto rezultat. Ostatecznie, liczba kolumn została zredukowana do trzech. Natomiast otrzymaliśmy tabelę ze znacznie większa liczbą wierszy. Stąd też nazwa, którą używam, czyli zmiana formatu tabeli z szerokiej na długą.

A teraz kilka słów wyjaśnienia, co się wydarzyło😉 Poprzednio mieliśmy sytuację, gdzie jeden wiersz to informacje tylko dla jednego kraju. Teraz każdy kraj „mieści” się na parunastu wierszach ale tylko w trzech kolumnach.

Pierwsza to nazwa kraju. Zasadniczo jest ta sama ale pewna „zmiana” to fakt, że nazwa została powielona w każdym nowym wierszu. W kolumnie drugiej (nazwanej systemowo „Attribute”, co można zmienić) mamy nazwy kolumn sprzed operacji. Każdy wiersz to inna nazwa dla typu emisji Co2. Natomiast w kolumnie trzeciej („Value”) mamy odpowiadające liczby. W tym konkretnym przykładzie informacje nt. emisji Co2 dla każdego typu i ich wartość zostały przeniesione tylko do tych dwóch kolumn. Mamy zatem wymiar (typ emisji) i sumę (ilość emisji Co2).

Inne opcje

Tu tylko nadmienię, że mamy również możliwość zrobienia powyższej operacji dla wybranych kolumn, a nie dla wszystkich (poza zaznaczoną). Wybieramy wówczas (np. przytrzymując CTRL) wybrane kolumny i klikamy na Anuluj przestawienie tylko zaznaczonych kolumn:

W takim scenariuszu tylko powyższe dwie kolumny (Fuel combustion in energy industries i Fuel combustion in manufacturing...) zostaną rozbite na dwa wiersze. Pozostałe wartości z innych kolumn zostaną natomiast powtórzone. Poprzednio, powtórzona była tylko nazwa kraju.

Wychodzenie z Power Query

Tu jeszcze jedna wskazówka i krok dla osób, które chcą wyjść z trybu Powery Query. Zasadniczo używając tego dodatku nawiązujemy połączenie ze źródłem (powyżej to plik). Ma to znaczenie jeśli chcemy np. później odświeżać dane, aby nie powtarzać znów tych samych kroków w Excelu. Z drugiej strony może to mieć jednak pewne ograniczenia. Jeśli chcemy zatem powrócić do zwykłej tabeli i niejako „zapomnieć”, że używaliśmy Power Query, musimy zrobić dodatkowe kroki.

Klikamy na Plik oraz Zamknij i Załaduj w lewym górnym rogu:

Dane są wczytane do standardowego arkusza w Excelu:

U góry pojawia się zakładka Zapytanie, która służy do zarządzania połączeniem.

Klikamy na Usuń w tejże zakładce i potwierdzamy.

Samo połączenie jest już nieaktwyne. Pozostaje jeszcze kwestia tabeli w Excelu. Domyślnie dane są w formacie tabeli excelowej.

Jeśli chcecie się pozbyć także tego formatu, to już standardowo można to zrobić poprzez zakładkę Projekt Tabeli – Konwertuj na zakres:

A może jednak Kolumna Przestawna czyli vice versa?

Tu nadmienię, że mamy również możliwość odwrotnej operacji do zmiany formatu tabeli z szerokiej na długą. Być może potrzebujemy zmienić format z długiego na szeroki, bo np. przygotowujemy raport dla ludzi 😉 Wówczas możemy skorzystać z opcji  Kolumna Przestawna (w angielskiej wersji to Pivot, znów nie mylcie z tabelą przestawną). Ale to pokażę na innym przykładzie w przyszłości.

Możecie jednak już teraz obejrzeć film na You Tube, gdzie kiedyś już nieco o tym mówiłem przy okazji omawiania innego przykładu zmiany formatu z szerokiego na długi. 😉

Pozdrawiam,

Michał