Uwielbiam pisać zapytania SQL-owe. Są one logiczne, dokładne, a przy odpowiednim formatowaniu – przejrzyste. Ale trzeba zdawać sobie sprawę, że SQL nie jest idealny do wszystkich zastosowań. W tym wpisie postanowiłem pokazać jak łatwo przestawić się z pisania zapytań SQL na Pythona, a dokładniej Pandas.
Wróćmy jeszcze na chwilę do wstępu dzisiejszego wpisu. Dlaczego właściwie SQL nie jest dobry na wszystko? W jakich sytuacjach nie ma wystarczających funkcjonalności? – zapytacie. SQL jest idealny do pozyskiwania danych z różnych tabel hurtowni. Zgrabnie napisane zapytania oszczędza analitykowi masę pracy i pozwala wydobyć głębię oraz wartość biznesową drzemiącą w danych firmowych. Ok – mamy już dane i teraz chcemy trochę z nimi podziałać. Dołączyć inną tabelkę, zmienić formaty czy kolejność kolumn możemy oczywiście również z poziomu SQL Developera. Czasem brakuje nam przestrzeni roboczej do odkładania tymczasowych tabel, a musicie przyznać, że zabawa z WITH w SQL nieco zaciemnia obraz kodu. Czasem musimy także “wpuścić bokiem” jakiego Excela lub csv z danymi do analizy.
Jest kilka rozwiązań produktowych, które tworzą dla użytkownika przestrzeń do zabawy z danymi i umożliwiają łatwą obsługę importu plików. Należą do nich m.in.:
- Microsoft SQL Server
- SAS Enterprise Guide
- Python (a w szczególności jego biblioteka Pandas).
Ostatnio trochę było na blogu o SASie – jest to oprogramowanie, którego używam na co dzień i bardzo to sobie chwalę. Jednak w związku z niesłabnącą popularnością Pythona tym razem postanowiłem “skręcić” na blogu nieco w kierunku tego open source’owego oprogramowania.
Artykuł będzie krótkim wprowadzeniem do Pythona dla SQLowców. Najpierw napiszę kilka zdań o przygotowaniu warsztatu do pracy z danymi w Pandas. Później pokażę jak napisać w Pythonie kilkanaście najpopularniejszych komend SQLowych, tak aby wyjadacze SQLa mogli odnaleźć się w nowym środowisku.
Chcę, żebyś ćwiczył razem ze mną. Dlatego fajnie będzie, jeśli pobierzesz pliki z danymi, które wykorzystuję w dzisiejszym artykule. Dane, na których będziemy pracować dotyczą Formuły 1. Znajdziesz tu informację m.in. o kierowcach, którzy startowali w wyścigach. Pliki z danymi pobrałem ze zbiorów Kaggle. Dokładnie to wszystkie pliki znajdziesz TUTAJ
Przygotowanie środowiska Jupyter do pracy
Aby zacząć programowanie w Pythonie nie potrzebujemy instalować żadnego programu na naszym komputerze. Istnieje wiele on-linowych edytorów pozwalających na pisanie i kompilowanie kodu z poziomu przeglądarki internetowej. Jedynym z popularnych środowisk, w którym wykonasz kod Python jest Jupyter Notebook. W pełni funkcjonalną wersję demonstracyjną edytora znajdziesz tutaj.
Po przejściu na stronę Project Jupyter wybieramy opcję Try JupyterLab. Po kilku chwilach zostaje dla nas wygenerowane środowisko, w którym możemy łatwo pisać kolejne linijki kodu. Uruchamiamy notatnik w formacie Python 3.
Teraz mamy już gdzie wykonywać nasze kody, pora zaimportować pliki. W Jupyterze wrzucamy pliki z danymi o kierowcach, torach i wyścigach Formuły 1 do przestrzeni roboczej (strzałka do góry jak na poniższym obrazku).
Piszemy pierwsze polecenia, żeby zaimportować bibliotekę Pandas oraz pliki z przestrzeni roboczej.
import pandas as pd
drivers = pd.read_csv("drivers.csv", encoding='iso-8859-1')
Jak to napisać w Pythonie?
1.Wyświetlanie wszystkich danych z tabeli
Klasyczna komenda SQL w Pythonie wygląda jeszcze lepiej. Wystarczy po prostu napisać nazwę ramki danych, do której załadowaliśmy naszą tabelę:
SQL:
SELECT * FROM drivers;
Pandas:
drivers
2. Wyświetlanie pierwszych wierszy z tabeli
Załóżmy, że chcemy zobaczyć próbkę danych w tabeli – na przykład pierwszych 10 wierszy. Najpierw SQL (używamy ROWNUM), a później Python z użyciem funkcji head(n). Liczba w nawiasie określa liczbę wierszy do wyświetlenia. Jeśli pozostawisz ją pustą, to z automatu zobaczysz pierwszych 5 wierszy.
SQL:
SELECT * FROM drivers WHERE ROWNUM <= 10;
Pandas:
drivers.head(10)
3. Wybieranie określonych wierszy – jeden warunek
Jeśli chcesz wybrać jedynie wiersze, które spełniają określony warunek to w SQL użyjesz oczywiście słowa WHERE. W Pythonie sprawa wygląda również dość prosto – pamiętaj, żeby używać podwójnego symbolu “=”:
SQL:
SELECT * FROM drivers WHERE Nationality = 'Spanish';
Pandas:
drivers[drivers.nationality == 'Spanish']
4. Wybieranie określonych wierszy i wyświetlanie odpowiednich kolumn
W SQL po słowie SELECT podajemy listę kolumn do wyświetlania (w tym przypadku jedna kolumna). Z kolei w Pythonie użyjemy poniższej konstrukcji.
SQL:
SELECT driverRef FROM drivers WHERE Nationality = 'Spanish';
Pandas:
drivers[drivers.nationality == 'Spanish']['driverRef']
5. Wybieranie wierszy w oparciu o kilka warunków
Czasem jeden warunek nie wystarczy, aby wybrać pożądane obserwacje. W SQL możemy łączyć warunki korzystając ze słów AND oraz OR. W Pythonie mamy znaki “&” oraz “|“:
SELECT driverRef FROM drivers WHERE (Nationality = 'Spanish' AND forename = 'Emilio');
Pandas:
drivers[(drivers.nationality == 'Spanish') & (drivers.forename == 'Emilio')]
Jesteś już na mojej liście mailowej? Hmmm…. jeszcze nie? W takim razie zapisz się poniżej !
6. Wybieranie wierszy w oparciu o kilka warunków – tylko niektóre kolumny
Możemy dodatkowo ograniczać zakres wyświetlanych kolumn. W SQL wystarczy, że wymienimy te pożądane po słowie SELECT, w Python posługujemy się listą kolumn
SQL:
SELECT driverId, forename, surname, dob FROM drivers WHERE (nationality = 'Spanish' AND forename = 'Emilio');
Pandas:
drivers[(drivers.nationality == 'Spanish') & (drivers.forename == 'Emilio')][['driverId', 'forename', 'surname', 'dob']]
7. Podstawowe funkcje agregujące
SQL:
Widok wszystkich danych spełniających określony przez nas warunek jest super. Najczęściej jednak, gdy mówimy o danych liczbowych to nie interesują nas wszystkie dane, ale pewne związane z nimi statystyki. Dobrze jest znać minimalną, maksymalną i średnią wartość w zbiorze obserwacji. Funkcje SQL takie jak min, max, avg mają swoje odpowiedniki w Pythonie.
SELECT min(number), max(number), avg(number) FROM drivers;
Pandas:
drivers.agg({'number' : ['mean', 'min', 'max']})
8. Wybieranie niepowtarzalnych wartości w ramach danej kolumny
Niepowtarzalne (unikatowe) wartości przydają się wszędzie tam, gdzie pracujemy na danych sprzedażowych. Rodzajów produktów w sklepie czy w banku być może jest i dużo. Jednak najczęściej transakcji na poszczególnych produktach jest dużo dużo więcej. W jaki sposób sprawdzić jakie produkty zostały sprzedane? W SQL mamy słowo DISTINCT, które w Pythonie ma swój odpowiednik w funkcji unique().
SQL:
SELECT DISTINCT surname FROM drivers;
Pandas:
drivers.surname.unique()
9. Szeregowanie wartości w zadanej kolejności
Układanie wartości w kolejności malejącej lub rosnącej zwiększa przejrzystość danych, niejednokrotnie pozwala na znajdowanie błędów, a poprzez zakładanie indeksów zwiększa szybkość wykonywania zapytań w SQL. Oczywiście w bibliotece Pandas mamy odpowiednika ORDER BY.
SQL:
SELECT * FROM drivers WHERE nationality = 'German' ORDER BY surname;
Pandas:
drivers[drivers.nationality == 'German'].sort_values('surname')
… i jeszcze kolejność malejąca…
SQL:
SELECT * FROM drivers WHERE nationality = 'German' ORDER BY surname DESC;
Pandas:
drivers[drivers.nationality == 'German'].sort_values('surname', ascending = False)
10. Grupowanie elementów
Tak jak wcześniej wspominałem, bardzo dobrze jest mieć wszystkie dane pod ręką i na widoku, ale zwykle najwięcej wartości przynosi nam analiza danych zagregowanych (inaczej zgrupowanych). Prosta komenda GROUP BY używana w SQL, niestety w Pythonie wygląda nieco gorzej:
SQL:
SELECT nationality, surname, count(*) FROM drivers GROUP BY nationality, surname;
Pandas:
drivers.groupby(['nationality', 'surname']).size().to_frame('liczba_kierowcow').reset_index()
Czemu po instrukcji groupby mamy jeszcze kilka innych?
1) size() – zastępuje w tym przypadku znane z SQL COUNT(*)
2) opcja to_frame() zamienia typ wyświetlanych danych z listy na ramkę danych, w nawiasie wpisujemy jak będzie się nazywać kolumna ze zagregowanymi wartościami
3) reset_index() – pozwala na przepisanie wartości, po której grupujemy, w każdym wierszu wynikowym.
Żeby sprawdzić działanie każdej z powyższych opcji dobrze jest dopisywać je po kolei i weryfikować co się zmieniło.
11. Grupowanie elementów z uszeregowaniem wyników
Aby jeszcze zwiększyć czytelność naszego raportu, często dodatkowo porządkujemy pogrupowane wartości. W SQL możemy to zrobić dodając po poleceniu GROUP BY, znane z wcześniejszych akapitów ORDER BY. A jak to wygląda w Pythonie?
SQL:
SELECT nationality, surname, count(*) as liczba_kierowcow FROM drivers GROUP BY nationality, surname ORDER BY count(*) DESC;
Pandas:
drivers.groupby(['nationality', 'surname']).size().to_frame('liczba_kierowcow').reset_index().sort_values('liczba_kierowcow', ascending = False)
12. Filtrowanie zagregowanych wyników
Być może rzadziej używaną, ale niekiedy mega przydatną opcją jest możliwość sortowania w SQL już zagregowanych wyników. Komendą odpowiedzialną za taką operację w SQL jest HAVING. W Pythonie to już nie takie oczywiste…
SQL:
SELECT nationality, surname, count(*) as liczba_kierowcow FROM drivers GROUP BY nationality, surname HAVING count(*) > 1
Pandas:
drivers.groupby(['nationality', 'surname']).filter(lambda g: len(g) > 1).groupby(['nationality', 'surname']).size().to_frame('liczba_kierowcow').reset_index()
Użyta powyżej funkcja lambda w tym przypadku sprawdza liczbę wystąpień kierowców z jednego kraju o takim samym nazwisku. Więcej o tej funkcji możesz przeczytać tutaj.
13. Wybieranie wierszy w oparciu o listę wartości
Kto by pomyślał, że jedno dwuliterowe słówko pozwoli zaoszczędzić tyle linijek kodu. Chodzi oczywiście o IN – zamiast pisać kilka, kilkanaście razy ten sam warunek z nową wartością wystarczy, że podamy w SQL listę wartości, które mają być znalezione. Czy równie prosto będzie w Pandas?
SQL:
SELECT * FROM drivers WHERE Nationality IN ('Spanish', 'German', 'Polish', 'Japanese');
Pandas:
drivers[drivers.nationality.isin(['Spanish', 'German', 'Polish', 'Japanese'])]
A co w przypadku, gdy na liście łatwiej jest wypisać elementy i wartości, które NIE powinny się znaleźć w naszych danych? W SQL mamy NOT IN, w Pandas jest nawet prościej zaprzeczyć – wystarczy wstawić na początku symbol ~.
SQL:
SELECT * FROM drivers WHERE Nationality NOT IN ('Spanish', 'German', 'Polish', 'Japanese');
Pandas:
drivers[~drivers.nationality.isin(['Spanish', 'German', 'Polish', 'Japanese'])]
14. Wybieranie n-największych/najmniejszych wartości
Jednym z częstych zadań przy analizie danych jest wyszukiwanie największych/najmniejszych wartości w zbiorze. Przykład. Porównujemy wartości wyliczone z tymi, których oczekujemy – w nowej kolumnie wyznaczamy różnicę między jedną a drugą wartością (nazwijmy ją “błędem”). Chcemy przeanalizować przypadki, gdzie błąd jest największy. Ustawiamy dane w kolejności malejącej według wartości błędu – zwykle potrzeba nam kilka pierwszych wierszy, żeby znaleźć przyczynę niezgodności.
W SQL możemy podejść do tematu następująco:
SELECT * FROM drivers ORDER BY driverId DESC LIMIT 10;
Natomiast w Pandas:
drivers.nlargest(10, columns = 'driverId')
A co jeśli nie wystarczy nam jednak pierwszych 10 wartości? Będziemy chcieli zobaczyć kolejnych 10 wierszy.
W SQL możemy napisać:
SELECT * FROM drivers ORDER BY driverId DESC LIMIT 10 OFFSET 10;
A w Pandas:
drivers.nlargest(20, columns = 'driverId').tail(10)
Zwróć uwagę, że zmieniliśmy wartość pierwszego argumentu w funkcji nlargest – wcześniej mieliśmy 10 teraz już 20 wierszy. Dlaczego? Mówimy Pandas – wyświetl 20 największych wartości (nlargest z parametrem 20), a następnie pokazujemy 10 ostatnich z nich (funkcja tail). Tym samym wyrzucamy 10 największych i zostawiamy 10 kolejnych. Sprytne, nie?
15. Łączenie zbiorów – UNION
Typowa operacja SQLowa – łączenie dwóch zbiorów o takiej samej strukturze. Przydaje się, gdy na przykład do istniejącego pliku zasilającego raport chcemy dodać dane z nowego miesiąca o tym samym układzie. Wyróżniamy dwie metody łączenia zbiorów – z powtórzeniami i bez. W pierwszym przypadku podczas łączenia nie dbamy o to czy w zbiorze mamy już wiersze identyczne z tymi, które dodajemy. Łączenie bez powtórzeń wymaga dodatkowo sprawdzenia, czy nie mamy już dokładnie takich danych w naszym wyjściowym zbiorze – jeśli tak, rekord, który powodowałby powtórzenie nie jest dodawany. Tyle słowem wstępu. Teraz jak to napisać w SQL i Pandas:
SQL:
SELECT * FROM drivers WHERE nationality = 'German' UNION SELECT * FROM drivers WHERE forename = 'Michael';
Oczywiście powyższy przykład ma jedynie pokazać zastosowanie UNIONa – w realnym świecie powyższy rezultat możemy uzyskać podając warunki na narodowość i imię po słowie WHERE a oddzielając OR.
Pandas:
pd.concat([drivers[drivers.nationality == 'German'], drivers[drivers.forename == 'Michael']])
Zastosowaliśmy opcję UNION, więc w szczególności mamy powtórzenia dla kilku rekordów – przykładowo Michael Schumacher jest zarówno Niemcem, jak i ma na imię Michael – wystąpił w obydwu zbiorach. Podczas łączenia nie sprawdzaliśmy powtórzeń – zatem w połączonej tabeli występuje dwa razy.
Jak sobie z tym poradzić? W SQL – UNION ALL, w Pandas – metodą drop_duplicates().
SQL:
SELECT * FROM drivers WHERE nationality = 'German' UNION ALL SELECT * FROM drivers WHERE forename = 'Michael';
Pandas:
pd.concat([drivers[drivers.nationality == 'German'], drivers[drivers.forename == 'Michael']]).drop_duplicates()
Jesteś już na liście mailowej Zobacz Dane?
16. Wstawianie nowych wartości do tabeli
Jeśli administrujemy jakąś bazą, to często musimy umieszczać w niej nowe dane. Można to robić na kilka sposobów. Czasem trzeba też “ręcznie” dodać jakiś rekord. W SQL mamy polecenie INSERT, a w Pandas?
SQL:
INSERT INTO drivers VALUES (9999, 'nowy_kierowca', 100.0, 'NEW', 'Nowy', 'Kierowca', '01/01/2022', 'Polish', 'https://zobaczdane.pl')
Pandas:
drivers = drivers.append({'driverId':9999, 'driverRef':'nowy_kierowca', 'number':100.0, 'code':'NEW',
'forename':'Nowy', 'surname':'Kierowca', 'dob':'01/01/2022',
'nationality':'Polish',
'url':'https://zobaczdane.pl'}, ignore_index=True)
17. Aktualizacja wartości w tabeli
Gdy patrzymy na dane w naszej tabeli, to czasem okazuje się, że chcemy lub potrzebujemy zaktualizować niektóre wartości. W zależności od tego na jakich zbiorach pracujemy, możemy zmieniać na przykład dane osobowe (nazwisko, wynagrodzenie itd.), dane o zamówieniu lub sprzedaży, czy stany magazynowe. Nieważne w sumie co zmieniamy, ważne jak to robimy. Załóżmy, że chcemy zmienić wartość driverRef dla naszego nowododanego kierowcy.
W SQLu możemy to zrobić następująco:
UPDATE drivers SET driverRef = 'zmiana_kierowcy' WHERE driverId = 9999;
W Pandas wykorzystamy metodę .loc:
drivers.loc[drivers['driverId']==9999, 'driverRef'] = 'zmiana_kierowcy'
18. Usuwanie danych z tabeli
Skoro możemy dodawać nowe wartości do naszych danych oraz modyfikować istniejące dane, to oczywiście możemy też usuwać niepotrzebne wiersze z zakresu. Usuńmy na przykład z naszego zbioru wszystkich Włochów.
W SQL mamy:
DELETE FROM drivers WHERE nationality = 'Italian';
… natomiast w Pandas użyjemy polecenia:
drivers.drop(drivers[drivers.nationality =='Italian'].index)
19.Łączenie zbiorów JOIN
JOIN – czyli podstawowe i ulubione polecenie miłośników SQLa (do których również się zaliczam ;)). Nie będę w tym miejscu pisać czym różnią się poszczególne warianty JOINa (LEFT/RIGHT/INNER FULL/OUTER JOIN) – informację o tym znajdziesz bez problemu w Internecie – na przykład TUTAJ lub TUTAJ.
Teraz zajmiemy się tym jak “wkraść” sqlowego joina do Pandas. Ale… żeby było co łączyć, to dobrze by było mieć co najmniej dwa zestawy danych do połączenia. Podobnie jak robiliśmy to z arkuszem drivers, z zestawu z Kaggle wybieramy arkusz driverStandings i ładujemy go do Jupytera.
driverStandings = pd.read_csv("driverStandings.csv", encoding='utf-8', encoding_errors='ignore')
driverStandings
Nowy plik wygląda mniej więcej tak:
W tabeli znajduje się klasyfikacja kierowców (position) w poszczególnych wyścigach Formuły 1 (raceId). Każdy kierowca jest identyfikowany za pomocą indeksu (driverId). Dodatkowo zwycięstwo w wyścigu jest odnotowane w zmiennej logicznej wins.
Dołączymy teraz do tabeli drivers wszystkie dane z driverStandings (łączenie po driverId):
W SQL:
SELECT
*
FROM drivers dri
LEFT JOIN driverStandings dris ON (dris.driverId = dri.driverId);
Pora na Pandas. Tym razem musimy zapisać:
driversJoin = drivers.merge(driverStandings, left_on = 'driverId', right_on = 'driverId', how = 'left')
Kilka słów wyjaśnienia. W powyższym poleceniu tworzymy nowy zbiór driversJoin przez dołączenie danych z tabeli driverStandings do tabeli drivers. Po słowach left_on oraz right_on podajemy nazwy kolumn stanowiących klucz połączenia. Co ważne – dobrze jest aby nazwy te były takie same (bo łatwiej jest się wtedy w tym wszystkim połapać), ale nie jest to wymóg. Możemy mieć inne nazwy w obu tabelach i dalej zrobić dobre łączenie. Typ łączenia (w tym wypadku LEFT JOIN) definiujemy po słowie how. Do wyboru mamy opcje: LEFT, RIGHT, INNER lub OUTER.
Ponieważ zastosowaliśmy LEFT JOIN to “rozmnożyliśmy” nieco liczbę wierszy w naszym zbiorze 😉
20. Zmiana nazw kolumn
Ostatnia dzisiejsze zagadnienie dotyczy zmiany nazw kolumn w tabeli / zbiorze danych. Przydaje się dość często, gdy na przykład chcemy dostosować się do konkretnej struktury na którym etapie procesu ETL. Wtedy nasze robocze nazwy zmieniamy na docelowe, których będzie dalej oczekiwał proces.
W SQL użyjemy zapisu:
ALTER TABLE drivers RENAME COLUMN driverRef TO nickname;
W Pandas zapiszemy to:
drivers.rename(columns={'driverRef': 'nickname'}, inplace=True)
Wpisana na końcu opcja inplace = True wskazuje, że nie chcemy tworzyć kopii zbioru zawierającej zmienioną kolumnę, ale że chcemy nadpisać nazwę kolumny w wyjściowym zbiorze. Jeżeli nie zapiszemy tej opcji, to Python stworzy nowy zbiór zostawiając jednocześnie kolumny drivers niezmienione.
Na zakończenie
Myślę, że dzisiejszy wpis będzie dla Ciebie przydatny. Nie musisz skakać po forach lub książkach – wszystko masz w jednym miejscu. Ten artykuł pisałem również dla siebie, żeby to co potrzebuję mieć zawsze pod ręką bez konieczności poszukiwań.
Polecam inne artykuły na blogu – np. ten o Trikach Excela. Jeśli jeszcze tego nie zrobiłeś, to możesz zapisać się na mój newsletter i zawsze w pierwszej kolejności dowiadywać się o nowościach ze świata danych i na blogu. Zapisać się możesz korzystając z poniższego formularza.
Tyle na dzisiaj i do zobaczenia !
