Jak przygotować animowany wykres typu “pogoń słupków” przy pomocy Google Sheets

Niestety nie jesteśmy w stanie tego zrobić samodzielnie bez pisania skryptów. Ale możemy skorzystać z dodatkowych aplikacji online (bez instalacji dodatkowego oprogramowania – wszystko w przeglądarce). Jako przykładowe dane posłużą mi przypadki zachorowań na koronawirusa. Temat, który zdominował media wiosną 2020.

Plik z danymi i kompletnym rozwiązaniem znajdziecie tutaj. Ostrzegam jednak, że dane zmieniają się codziennie i część danych pobieranych na żywo może się “wysypać”. Ale idea się nie zmieni 🙂

Pobieramy dane z wikipedii do arkusza

Ten temat został już szeroko opisany w tekście o tworzeniu własnego aktualizującego się wykresu.

Arkusze mają kilka funkcji którymi można pobrać dane zewnętrzne. Ponieważ strony Wikipedii są dość prosto skonstruowane, możemy używać IMPORTHTML . Jej składnia jest następująca:

=IMPORTHTML(url; zapytanie; indeks)

• url to oczywiście adres strony z której będziemy chcieli pobrać dane. Adres musimy umieścić w cudzysłowie, ewentualnie wskazać w tym miejscu, w której komórce funkcja ma szukać adresu.
• zapytanie – oznacza rodzaj obiektu jaki ma być znaleziony. Tabela lub lista. A więc w to miejsce wpisujemy „table” lub „list”.
• indeks – określamy którą tabelę lub listę ma dla nas pociągnąć funkcja. Pierwszą, drugą czy inną napotkaną licząc od góry.

Na stronie anglojęzycznej wikipedii znalazłem wykres z tabelą danych, które okazały się możliwe do pociągnięcia przez IMPORTHTML

Żeby wydobyć dane z tej tabeli posłużyliśmy się funkcją IMPORTHTML i uzyskaliśmy następujący rezultat:

Zdarza się, że strona internetowa w której grzebiemy zawiera bardzo wiele tabel. Czasem tabele w tabelach i wykresy zbudowane z okienek tabelkowych. Generalnie bałagan, który sprawia, że nie sposób policzyć czy to jest tabela nr 3 czy 5. (a raczej 79). W takich sytuacjach trzeba się ratować konsolą deweloperską. W przeglądarce Chrome wciskamy F12, następnie w zakładce “Console” wklejamy poniższy kod: var i = 1; [].forEach.call(document.getElementsByTagName("table"), function(x) { console.log(i++, x); }); . Wciskamy Enter i widzimy opisy wszystkich tabel w dokumencie. Po kliknięciu w opis, podświetla się interesująca nas tabela i już łatwo odczytać jej numer indeksu.

Porządkujemy dane

Ponieważ za chwilę będziemy pobierać dane dotyczące innych krajów, musimy nasze formuły dostosować. Liczba przypadków powinna mieć format numeryczny – bez ogona w nawiasie. Formuła też musi być gotowa na przyjęcie tabeli danych o długości.

Pierwotne rozwiązanie wygląda tak:

Mówiąc “po ludzku”, a nie po “arkuszowemu: Bierzemy dane z komórek D4 do końca kolumny D, znajdujemy w każdym wierszu pozycję znaku “(” i chwytamy wszystko na lewo od niego. Nastęnie konwertujemy z formatu tekstowego na liczbowy. Na koniec ograniczamy działania do wierszy dla których istnieją dane w kolumnie D.

Tabel ściągnąłem w sumie 30 i przekonałem się, że gdzieniegdzie były one ciut inaczej sformatowane, dlatego uważam, że lepiej zadziałać inaczej – pobrać pierwszy napotkany ciąg cyfr w komórkach D4:D. Służy do tego funkcja REGEXEXTRACT.

Jej składnia to:

=REGEXEXTRACT(tekst; wyrażenie_regularne)

tekst – prosta sprawa. Chodzi o zawartość komórki w kolumnie D

wyrażenie regularne – tu jest skomplikowana sprawa (ale potężna). Chodzi o specjalny sposób opisywania ciągów znaków i ich specyfiki. Np. kod pocztowy ma charakterystyczną formę 00-000 . “Po ludzku” powiemy: “2 cyfry, myślnik i 3 cyfry”. Przy pomocy wyrażenia regularnego: [0-9]{2}-[0-9]{3}. Więcej o budowaniu wyrażeń możecie przeczytać na tym blogu.

Nowa funkcja wygląda następująco:

Pozbywamy się przecinka – niestety import z anglojęzycznych stron jest kłopotliwy. Dlatego ja często korzystam z Arkuszy ustawionych na język angielski. Jednak dla celów szkoleniowych pozbędziemy się tego przecinka przy pomocy funkcji SUBSTITUTE (PODSTAW)

=SUBSTITUTE(tekst;czego szukać;czym zastąpić; [ile razy] - opcjonalne)

Tutaj wywalamy przecinek, więc funkcja do wklejenia do samego środka naszej formuły to:

=SUBSTITUTE(D4:D;",";"")

Zbieramy dane dla kolejnych krajów

To część pracochłonna. Ale ponieważ mamy już gotową formułę, wystarczy sobie zduplikować zakładkę i podmienić link do importu tabeli z wikipedii na inny kraj:

Od tej pory mamy już gotowy wzorzec i pobieramy już tylko dane ze stron innych krajów. Na potrzeby animacji, zrobiłem 30 arkuszy – po jednym dla każdego kraju. Podmiana linku do funkcji IMPORTHTML nie zawsze działa za pierwszym razem, bo strony wykresem rozwoju wirusa mają różną formę. Zauważyłem, że wykres o stałym szablonie jest tam zawsze, ale czasem jest to tabela nr 4, czasem 5, czasem 6. Trzeba pomacać. Ale jak mamy pozostać przy tym trybie zbierania danych i nie wplatać do zabawy innych form importu, musimy poświęcić jakieś 30-50 minut na stworzenie tych arkuszy.

Każda z zakładek ma nazwę od nazwy kraju: Polska, Włochy, USA, Chiny, etc.

Składamy dane w jedną zgrabną tabelę, którą będziemy eksportować.

Na tym etapie mogę już powiedzieć, że będziemy eksportować dane do aplikacji Fluorish i musimy je trochę uporządkować, żeby je łyknęła.

Robię nowy arkusz i nazywam go ‘Tabela zbiorcza’. W kolejnych wierszach będą daty, w kolumnach kraje, a wartościami oczywiście przypadki korona wirusa.

Potrzebuję zrobić listę dat – od pierwszego interesującego nas dnia (tu 31 grudnia 2019) do dziś. Ponieważ chcę by lista się wydłużała codziennie o jeden dzień, używam funkcji SEQUENCE. =SEQUENCE(ile wierszy; ile kolumn; od czego zacząć; co ile skakać)

• ile wierszy – today()-B3 , czyli od dzisiejszej daty odejmuję 31 grudnia. Tyle dni ma wierszy ma liczyć spis
• 1 – jedna kolumna
• od czego zacząć – od 01 stycznia
• co ile skakać – co jeden dzień

Listę krajów wpisuję “z palca”. Google Sheets nie potrafi wygenerować listy zakładek bez użycia skryptów.

Najważniejsza funkcja kryje się wierszu 3 i jest skopiowana pod każdym krajem, żeby pobrać dane o zachorowaniach. To funkcja vlookup, którą szerzej omawiam w tym wpisie. Dodatkowo jest ona obudowana funkcją arrayformula i array_constrain, które rozszerzają i ograniczają jej działanie.

=array_constrain( ArrayFormula( ifna( vlookup($B3:B;indirect(C$2&"!A4:I");8;TRUE);0) ); counta($B$3:$B);1)

=VLOOKUP(kryteria_wyszukiwania; zakres; indeks; posortowany)

• kryteria wyszukiwania – $B3:B . Ma wyszukiwać wartośći odpowiadających tym z kolumny B (ten dolarek blokuje kolumnę, żeby łatwiej było kopiować kolumnę w prawo).
• zakres – indirect(C$2&”!A4:I”) . Tu używam funkcji indirect, która pozwala mi użyć zawartości komórki C2 jako nazwy arkusza (Polska), a następnie z tego arkusza wziąć zakres A4:I. W całości ten zapis Arkusze odczytają jako Polska!A4:I . Jak będę kopiował funkcję w prawo, to będzie ona pobierać nazwy kolejnych krajów i zakładek.
• indeks – 8 Za każdym razem dane o ilości przypadków były w kolumnie H, czyli kolumnie 8.
• posortowany – true. Dane są posortowane, i warto uwzględnić tą wartość. W niektórych krajach nie ma danych w tabelach z dni gdzieś w środku zakresu, bo liczba przypadków nie zmieniała się. Dzięki użyciu opcji true, arkusz nie będzie wypluwał błędu a poda ostatnią wartość z ostatniego wiersza w którym go znalazł. Dodatkowo jeśli dla początkowych wierszy nie było wyników (np. w lutym w Polsce nie było przypadków), to automatycznie wpisuje 0.

Wartości w wierszu 1 to tylko mój tester, wywołują liczbę przypadków na dzisiaj – dzięki temu wiem czy gdzieś na dole nie wyskoczyły błędy (zwykle w razie błędu pojawia się 0).

Importujemy dane do Flourish

Następny krok to import do Fluorish. To aplikacja działająca w przeglądarce. Przy jej pomocy możemy robić masę pięknych wizualizacji. Podstawowa wersja jest za free i wystarczy nam do dzisiejszego zastosowania.

Zaczynamy od założenia sobie darmowego konta i wybieramy New Visualisation:

Ten rodzaj wykresu/animacji został nazwany “Bar chart race”

Po wybraniu widzimy, że mamy do czynienia z przykładową strukturą danych i wystarczy dopasować nasz arkusz do gotowej struktury.

Widzę, że tabela danych jest zbudowana odwrotnie niż moja – tzn. oś czasu stanowią kolumny, natomiast w wierszach są kraje. Dlatego muszę swoją tabelę “przewrócić”. Tworzę nowy arkusz i wrzucam do niego zawartość tabeli zbiorczej przy pomocy funkcji TRANSPOSE (ona zamienia miejscami wiersze z kolumnami).

Jak widać poniżej struktura danych we Flourish jest bardzo podobna do tej ze zwykłego arkusza kalkulacyjnego i wystarczy wkleić dane. Widzę, że w wybranym szablonie jest też możliwość wpisania kontynentu. Robię to “z palca”.

Dodajemy graficzne etykiety

Jest też możliwość wrzucenia flagi kraju. Widać, że autorzy szablonu wygrzebują flagi ze strony: www.countryflags.io (linki w kolumnie C)

Wizyta na tej stronie i przyjrzenie się adresowi, szybko owocuje pomysłem o co chodzi: https://www.countryflags.io/br/flat/64.png . to BR to kod kraju. Lista dwuliterowych kodów znajduje się na countryflags.io . Wystarczy podmienić te dwie literki i mieć flagę Polski (PL), Portugalii (PT), itd…

Publikujemy efekty pracy

Majstrujemy z danymi i podglądamy jak wyglądają. Zmieniamy formatowanie, kolory, czcionki w razie potrzeby. Na koniec klikamy Export&Publish:

Poniżej jest osadzona animacja wyeksportowana na bazie danych z 19 kwietnia.

Moim zdaniem “pogoń słupków” to jeden z najciekawszych i najbardziej hipnotyzujących wykresów. Nadaje się nie tylko do statystyk państw. Z powodzeniem można go używać do ilustrowania zmian sprzedaży w czasie, udziałów w rynku poszczególnych przedsiębiorstw, czy zmian w tabelach ligowych rozgrywek sportowych.