Animacja przypadków koronawirusa na świecie - podejście drugie - Arkusze Google – użyteczne funkcje i sztuczki Google Sheets

W jednym z wiosennych wpisów (2020) opisywałem jak przygotować wykres w postaci animowanych słupków. Wykorzystywał on dane z wikipedii.

Niestety formaty danych często się zmieniają, dane dla niektórych krajów przestały być aktualizowane w wikipedii, a temat pozostał ciekawy. Dlatego postanowiłem pokazać alternatywny (i prostszy) sposób na osiągnięcie ciekawego efektu. Wpis warto czytać przeglądając równocześnie roboczy arkusz, w którym są wszystkie dane.

Pobieramy dane do arkusza z zewnętrznego źródła

Chwila szperania w Google pozwoliła mi znaleźć źródło surowych danych na temat dziennych zachorowań na COVID-19. John Hopkins University udostępnia je pod postacią pliku tekstowego z rozszerzeniem .csv

To uniwersalny format danych, gdzie kolejne komórki rozdziela się przecinkami (bywają odmiany z innymi separatorami, dlatego warto zawsze przyjrzeć się źródłu).

Plik kryje się tutaj.

M.in. na jego podstawie powstało takie cudo:

Dashboard z wizualizacją danych na temat Covid-19 (otwórz działajacą stronę)

My stworzymy coś skromniejszego, ale też bardzo ciekawego.

Dane z pliku csv możemy zaimportować przy pomocy menu, możemy je też ściągnąć przy pomocy funkcji IMPORTDATA

Jedna formuła generuje całą tabelę. Jednak nie wszystko działa tak jak byśmy chcieli. Potrzebujemy danych dla całych krajów, a niektóre są rozbite na regiony (jak widoczna Australia). Jest też trochę problemów z ciągiem dat.

Sumujemy dane regionów

Chodzi o to by mieć dane dla całej Australii i dla innych krajów podzielonych w tabeli na regiony. I żeby nie trzeba było tego ręcznie aktualizować za każdym razem jak przychodzą nowe dane.

Aby to zilustrować, wytnę kawałek arkusza i zacznę operować na nim:

Zaczynam od tego, że robię nową listę krajów, gdzie nie eliminuję podwojenia. Służy do tego funkcja UNIQUE.

Następnie kopiuję listę z datami – one się nie zmienią.

Kolejne zadanie to przeniesienie danych. Używam do tego funkcji FILTER (opisanej w osobnym wpisie).

Dla Algerii wygląda to tak:

*Mówiąc “po polsku” – formuła =filter(C$2:C;$B$2:$B=$B4) wyświetla wszystkie wartości z kolumny C, dla których nagłówki z kolumny B są identyczne z nagłówkiem w kolumnie M.* Pozorny bałagan ze znacznikami $ pozwala na szybkie skopiowanie formuły w całą tabelę.

W przypadku Australii, zastosowanie tej samej funkcji zwróci nam liczby przypadków ze wszystkich jej regionów:

Wklejenie funkcji filtrującej obok nagłówka “Australia” sprawiło, że wyświetlają się wartości wszystkich regionów. Formuła jest tylko w jednym miejscu.

Pozostaje je zsumować, dodając do funkcji FILTER, funkcję SUM:.

=sum( filter(C$2:C;$B$2:$B=$M10)) – składa cały wynik w jedną komórkę.

Pozostaje teraz skopiować odpowiednio formułę do wszystkich komórek…

Widać, że sumowanie nie zaszkodziło w przypadkach, gdy kraj nie był podzielony na regiony, a tam gdzie są regiony, mamy gotowe sumy.

Importujemy dane do Flourish Studio

Następny krok to import do Fluorish.Studio. 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”

Flourish daje dostęp do dziesiątek efektownych szablonów – dużo bardziej widowiskowych niż te znane z Excela czy standardowych Google Sheets

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

Przykładowe dane wprowadzone do wizualizacji. Bardzo pomagają zrozumieć jak działa to narzędzie i co trzeba zrobić.

Po wejściu w zakładkę “Data” możemy zobaczyć strukturę danych stojącą za wykresem. To zwykła tabela, w której poszczególnym kolumnom przypisano specjalne funkcje. Możemy śmiało wkleić do niej swoje dane.

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…

Klasyfikujemy kraje po kontynentach, żeby móc filtrować wizualizacje.

Żeby móc przydzielić krajom ich kontynenty, najlepiej jest ściągnąć tabelę. Taką możemy znaleźć np. na stronie statistictimes.com

Tablicę możemy skopiować do naszego arkusza i przy pomocy funkcji VLOOKUP (mojej ulubionej i opisanej we wcześniejszym wpisie) przypisać kontynenty do krajów

Filtr pozwala włączać lub wyłączać kategorie

Opracowane dane po skopiowaniu do Flourish.Studio wyglądają tak:

Po wklejeniu danych, należy “wytłumaczyć” aplikacji, w których kolumnach są nazwy krajów, gdzie są flagi, a gdzie dane z kolejnych dni.

Dodajemy komentarze do wizualizacji

Dla większego efektu wprowadzam komentarze pojawiające się i znikające w odpowiednich momentach. Ich wprowadzanie jest bardzo proste. Wystarczy stworzyć 3 kolumny: klatka kiedy komentarz ma się pojawić, klatka kiedy ma zniknąć i treść komentarza.

Publikujemy efekty pracy

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

Kiedy wykres jest gotowy, należy wygenerować publiczny link do niego, albo skopiować kod na własną stronę.

Poniżej jest osadzona animacja wyeksportowana na bazie danych z końca października.

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.

Animacja przypadków koronawirusa na świecie – podejście drugie