Kod
library(readr)
= read_csv("data/gapminder_1950_2023.csv") gm
SQL (ang. Structured Query Language) to język służący do interakcji z danymi przechowywanymi w bazach danych. Zakres jego zastosowań jest bardzo szeroki, od prostych zapytań, poprzez zarządzanie bazami danych, aż po zaawansowane analizy danych.
W przypadku przetwarzania danych tabelarycznych, SQL oferuje szereg możliwych operacji (ang. statements) do wykonania na zbiorach danych. Wśród nich znajdują się między innymi:
Przykładowe zapytanie SQL poniżej wybiera kraje, rok oraz oczekiwaną długość życia dla roku 2022 ze zbioru danych gapminder
, sortując wyniki malejąco według oczekiwanej długości życia.
SELECT Kraj, Rok, Ocz_dl_zycia
FROM gapminder
WHERE Rok = 2022
ORDER BY Ocz_dl_zycia DESC
Kolejne, bardziej złożone zapytanie SQL, grupuje dane z tabeli gapminder
według roku, obliczając średnią oczekiwaną długość życia dla każdego roku, zaczynając od 1980 roku, sortując wyniki malejąco według średniej oczekiwanej długości życia.
SELECT Rok, AVG(Ocz_dl_zycia) AS srednia_odz
FROM gapminder
GROUP BY Rok
WHERE Rok >= 1980
ORDER BY srednia_odz DESC
Język SQL stał jest się standardem w dziedzinie baz danych, jednak w przypadku przetwarzania danych tabelarycznych o bardziej analitycznym charakterze1 często wygodniejsze jest korzystanie z dedykowanych narzędzi, takich jak pakiet dplyr w języku R czy pandas w języku Python.2 Te narzędzia były inspirowane językiem SQL, ale oferują interfejs i funkcjonalności nakierowane na przetwarzanie danych tabelarycznych.
library(readr)
= read_csv("data/gapminder_1950_2023.csv") gm
gm
# A tibble: 14,060 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austra… 1950 Azja 8180000 16400 69.4 6.7 3.07
2 Austra… 1951 Azja 8420000 16500 69.2 7.04 3.06
3 Austra… 1952 Azja 8630000 16200 69.5 7.01 3.18
# ℹ 14,057 more rows
Filtrowanie to operacja polegająca na wybraniu wierszy, które spełniają określone warunki. Warunki te definiowane jako zapytania dotyczące poszczególnych zmiennych (kolumn) w zbiorze danych. Do ich stworzenia stosowane są operatory porównania, takie jak ==
, !=
, >
, <
, >=
, <=
(Tabela 2.1) oraz operatory logiczne i pomocnicze, takie jak !
, &
, |
, all()
, any()
, is.na()
(Tabela 2.2).
Filtrowanie w pakiecie dplyr realizowane jest za pomocą funkcji filter()
. Możliwe jest także wykorzystanie funkcji subset()
z pakietu base lub operatora [
do filtrowania danych.
library(dplyr)
= filter(gm, Rok == 2022)
gm2022 gm2022
# A tibble: 190 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austra… 2022 Azja 26200000 51000 83.3 NA 1.8
2 Brunei 2022 Azja 449000 58700 74.8 NA 1.81
3 Kambod… 2022 Azja 16800000 4530 70.5 NA 2.39
# ℹ 187 more rows
Wybór wierszy z danymi dla roku 2000 i późniejszych:
= filter(gm, Rok >= 2000)
gm_m gm_m
# A tibble: 4,560 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austra… 2000 Azja 19000000 40200 79.7 18.5 1.76
2 Austra… 2001 Azja 19200000 40400 80.1 18.6 1.76
3 Austra… 2002 Azja 19500000 41400 80.3 18.7 1.78
# ℹ 4,557 more rows
Wybór wierszy z danymi dla Polski:
= filter(gm, Kraj == "Polska")
gm_pol gm_pol
# A tibble: 74 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Polska 1950 Europa 24800000 5430 59 4.52 3.6
2 Polska 1951 Europa 25200000 5550 57.9 4.83 3.62
3 Polska 1952 Europa 25700000 5560 60.1 4.97 3.64
# ℹ 71 more rows
Operator | Wyjaśnienie |
---|---|
== | Równy |
!= | Nie równy |
%in% | Zawiera się w |
>, < | Większy/Mniejszy niż |
>=, <= | Większy/Mniejszy niż lub równy |
Wybór wierszy z danymi nie dotyczącymi Polski:
= filter(gm, Kraj != "Polska")
gm_niepol gm_niepol
# A tibble: 13,986 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austra… 1950 Azja 8180000 16400 69.4 6.7 3.07
2 Austra… 1951 Azja 8420000 16500 69.2 7.04 3.06
3 Austra… 1952 Azja 8630000 16200 69.5 7.01 3.18
# ℹ 13,983 more rows
Wybór wierszy z danymi dla Polski i Niemiec:
= filter(gm, Kraj %in% c("Polska", "Niemcy"))
gm_eu gm_eu
# A tibble: 148 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Niemcy 1950 Europa 71000000 8630 66.5 7.31 2.08
2 Niemcy 1951 Europa 70800000 9350 66.9 8.21 2.09
3 Niemcy 1952 Europa 70800000 10100 67.3 8.66 2.11
# ℹ 145 more rows
Wybór wierszy z danymi dla Polski w roku 2022:3
= filter(gm, Kraj == "Polska", Rok == 2022)
gm_pol2022 gm_pol2022
# A tibble: 1 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Polska 2022 Europa 39900000 36800 78.6 NA 1.29
Operator | Wyjaśnienie |
---|---|
! | Negacja (nie) |
& | Koniunkcja (i) |
| | Alternatywa (lub) |
all | Wszystkie |
any | Którykolwiek |
is.na | Czy jest NA? |
Wybór wierszy z danymi dla Polski lub dla roku 2022:
= filter(gm, Kraj == "Polska" | Rok == 2022)
gm_pol2022b gm_pol2022b
# A tibble: 263 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austra… 2022 Azja 26200000 51000 83.3 NA 1.8
2 Brunei 2022 Azja 449000 58700 74.8 NA 1.81
3 Kambod… 2022 Azja 16800000 4530 70.5 NA 2.39
# ℹ 260 more rows
Filtrowanie może być również łączone z innymi operacjami, takimi jak sortowanie czy wydzielanie. Poniżej następuje wybór danych dla Polski, posortowanych malejąco według PKB na osobę oraz wybranie 10 pierwszych wierszy. Inaczej mówiąc, prezentowane jest 10 lat z najwyższym PKB na osobę dla Polski, posortowanych od tych z najwyższym PKB na osobę.
= filter(gm, Kraj == "Polska")|>
gm_pol2022c arrange(-PKB_na_osobe) |>
slice(1:10)
print(gm_pol2022c, n = 10)
# A tibble: 10 × 8
Kraj Rok Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Polska 2023 Europa 41000000 37100 78.7 NA 1.3
2 Polska 2022 Europa 39900000 36800 78.6 NA 1.29
3 Polska 2021 Europa 38300000 34900 78.4 NA 1.29
4 Polska 2019 Europa 38500000 33200 78.1 NA 1.29
5 Polska 2020 Europa 38400000 32500 76.7 NA 1.29
6 Polska 2018 Europa 38500000 31700 78 9.07 1.29
7 Polska 2017 Europa 38500000 30000 78 8.88 1.29
8 Polska 2016 Europa 38500000 28500 77.9 8.51 1.3
9 Polska 2015 Europa 38600000 27700 77.6 8.22 1.31
10 Polska 2014 Europa 38600000 26500 77.6 8.11 1.32
Selekcja polega na wyborze kolumn z tabeli. Zazwyczaj selekcja polega na wskazaniu nazw kolumn, które mają zostać wybrane, ale możliwe jest także wybranie kolumn na podstawie ich pozycji w tabeli czy wzorców nazw kolumn.
Selekcja w pakiecie dplyr realizowana jest za pomocą funkcji select()
. Możliwe jest także wykorzystanie operatora [
czy też argumentu select
w funkcji subset()
z pakietu base.
Wybór kolumn z danymi dla kraju, roku oraz oczekiwanej długości życia:
= select(gm, Kraj, Rok, Ocz_dl_zycia)
gm_sel gm_sel
# A tibble: 14,060 × 3
Kraj Rok Ocz_dl_zycia
<chr> <dbl> <dbl>
1 Australia 1950 69.4
2 Australia 1951 69.2
3 Australia 1952 69.5
# ℹ 14,057 more rows
Wybór kolumn z danymi dla wszystkich zmiennych, z wyjątkiem PKB na osobę:
= select(gm, -PKB_na_osobe)
gm_sel2 gm_sel2
# A tibble: 14,060 × 7
Kraj Rok Region Populacja Ocz_dl_zycia CO2_na_osobe Plodnosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 Australia 1950 Azja 8180000 69.4 6.7 3.07
2 Australia 1951 Azja 8420000 69.2 7.04 3.06
3 Australia 1952 Azja 8630000 69.5 7.01 3.18
# ℹ 14,057 more rows
Wybór kolumn od tej z nazwą kraju do kolumny z populacją:
= select(gm, Kraj:Populacja)
gm_sel3 gm_sel3
# A tibble: 14,060 × 4
Kraj Rok Region Populacja
<chr> <dbl> <chr> <dbl>
1 Australia 1950 Azja 8180000
2 Australia 1951 Azja 8420000
3 Australia 1952 Azja 8630000
# ℹ 14,057 more rows
Wybór kolumn z danymi dla roku oraz wszystkich pozostałych:
= select(gm, Rok, everything())
gm_sel4 gm_sel4
# A tibble: 14,060 × 8
Rok Kraj Region Populacja PKB_na_osobe Ocz_dl_zycia CO2_na_osobe Plodnosc
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1950 Austra… Azja 8180000 16400 69.4 6.7 3.07
2 1951 Austra… Azja 8420000 16500 69.2 7.04 3.06
3 1952 Austra… Azja 8630000 16200 69.5 7.01 3.18
# ℹ 14,057 more rows
Wybór kolumn z danymi dla roku oraz wszystkich zaczynających się od litery “P”:
= select(gm, Rok, starts_with("P"))
gm_sel5 gm_sel5
# A tibble: 14,060 × 4
Rok Populacja PKB_na_osobe Plodnosc
<dbl> <dbl> <dbl> <dbl>
1 1950 8180000 16400 3.07
2 1951 8420000 16500 3.06
3 1952 8630000 16200 3.18
# ℹ 14,057 more rows
Funckja select()
pozwala także na zmianę nazw kolumn podczas ich selekcji.4
= select(gm, Kraj, Dlugosc_zycia = Ocz_dl_zycia)
gm_sel6 gm_sel6
# A tibble: 14,060 × 2
Kraj Dlugosc_zycia
<chr> <dbl>
1 Australia 69.4
2 Australia 69.2
3 Australia 69.5
# ℹ 14,057 more rows
Grupowanie to operacja polegająca na podziale zbioru danych na podzbiory na podstawie wartości jednej lub kilku zmiennych. Ta operacja zazwyczaj jest powiązana z operacją podsumowywania, czyli obliczaniem wybranych wartości dla każdego z podzbiorów.
Grupowanie w pakiecie dplyr realizowane jest za pomocą funkcji group_by()
lub wewnątrz funkcji summarize()
przy użyciu argumentu .by
. R umożliwia także grupowanie danych za pomocą funkcji aggregate()
z pakietu base.
Określenie średniej oczekiwanej długości życia dla każdego roku:
= summarize(gm, mean_Odz = mean(Ocz_dl_zycia),
gm_sum1 .by = Rok)
gm_sum1
# A tibble: 74 × 2
Rok mean_Odz
<dbl> <dbl>
1 1950 52.3
2 1951 52.5
3 1952 53.2
# ℹ 71 more rows
Określenie średniej oczekiwanej długości życia dla każdego roku, bez uwzględnienia brakujących danych:
= summarize(gm, mean_Odz = mean(Ocz_dl_zycia, na.rm = TRUE),
gm_sum2 .by = Rok)
gm_sum2
# A tibble: 74 × 2
Rok mean_Odz
<dbl> <dbl>
1 1950 52.3
2 1951 52.5
3 1952 53.2
# ℹ 71 more rows
Obliczenia grupowania i podsumowywania mogą być również wykonywane dla kilku zmiennych oraz statystyk jednocześnie. Najprostszym sposobem jest zadeklarowanie każdego obliczenia jako kolejny argument funkcji summarize()
. Alternatywnie, możliwe jest użycie pomocniczej funkcji across()
. Oczekuje ona dwóch argumentów: wybranych zmiennych oraz listy funkcji, które mają zostać zastosowane do tych zmiennych.
Określenie średniej oczekiwanej długości życia, PKB na osobę oraz populacji dla każdego roku:
= summarize(gm,
gm_sum3 across(Populacja:Ocz_dl_zycia,
list(mean = ~mean(.x, na.rm = TRUE))),
.by = Rok)
gm_sum3
# A tibble: 74 × 4
Rok Populacja_mean PKB_na_osobe_mean Ocz_dl_zycia_mean
<dbl> <dbl> <dbl> <dbl>
1 1950 12079956. 4807. 52.3
2 1951 12294779. 4934. 52.5
3 1952 12528559. 5061. 53.2
# ℹ 71 more rows
Określenie średniej oraz ochylenia standardowego dla populacji, PKB na osobę oraz oczekiwanej długości życia dla każdego roku i regionu:
= summarize(gm,
gm_sum4 across(Populacja:Ocz_dl_zycia,
list(mean = ~mean(.x, na.rm = TRUE),
sd = ~sd(.x, na.rm = TRUE))),
.by = c(Rok, Region))
gm_sum4
# A tibble: 296 × 8
Rok Region Populacja_mean Populacja_sd PKB_na_osobe_mean PKB_na_osobe_sd
<dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 1950 Azja 23475186. 84754940. 4881. 10335.
2 1951 Azja 23934519. 86408690. 5008. 10454.
3 1952 Azja 24425897. 88171080. 5179. 10625.
# ℹ 293 more rows
# ℹ 2 more variables: Ocz_dl_zycia_mean <dbl>, Ocz_dl_zycia_sd <dbl>
Przekształcanie formy danych oznacza zmianę sposobu, w jaki dane są przechowywane w tabeli. Może odnosić się to do przekształcenia danych z szerokiego formatu (ang. wide format) na długi format (ang. long format) i odwrotnie.
W R przekształcanie formy danych może być realizowane za pomocą funkcji pivot_longer()
oraz pivot_wider()
z pakietu tidyr. Alternatywnie, możliwe jest także wykorzystanie funkcji reshape()
z pakietu base.
library(tidyr)
Przekształcanie formy danych często poprzedzone jest operacjami takimi jak np. filtrowanie czy selekcja.
Wybór danych dla kraju, roku oraz oczekiwanej długości życia, a następnie przekształcenie ich z szerokiego na długi format:
= select(gm, Kraj, Rok, Ocz_dl_zycia)
gm2 gm2
# A tibble: 14,060 × 3
Kraj Rok Ocz_dl_zycia
<chr> <dbl> <dbl>
1 Australia 1950 69.4
2 Australia 1951 69.2
3 Australia 1952 69.5
# ℹ 14,057 more rows
= pivot_wider(gm2, names_from = Rok, values_from = Ocz_dl_zycia)
gm2_w gm2_w
# A tibble: 190 × 75
Kraj `1950` `1951` `1952` `1953` `1954` `1955` `1956` `1957` `1958` `1959`
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Austral… 69.4 69.2 69.5 69.9 70.2 70.3 70.4 70.6 71 70.9
2 Brunei 51.7 52.6 52.3 52.6 53 52.9 52.6 52.5 53.6 54.5
3 Kambodża 46.2 46.4 46.6 46.8 47.3 47.5 47.8 48.2 48.5 48.9
# ℹ 187 more rows
# ℹ 64 more variables: `1960` <dbl>, `1961` <dbl>, `1962` <dbl>, `1963` <dbl>,
# `1964` <dbl>, `1965` <dbl>, `1966` <dbl>, `1967` <dbl>, `1968` <dbl>,
# `1969` <dbl>, `1970` <dbl>, `1971` <dbl>, `1972` <dbl>, `1973` <dbl>,
# `1974` <dbl>, `1975` <dbl>, `1976` <dbl>, `1977` <dbl>, `1978` <dbl>,
# `1979` <dbl>, `1980` <dbl>, `1981` <dbl>, `1982` <dbl>, `1983` <dbl>,
# `1984` <dbl>, `1985` <dbl>, `1986` <dbl>, `1987` <dbl>, `1988` <dbl>, …
W efekcie powyższej operacji, dane dla każdego roku zostały umieszczone w osobnej kolumnie.
W celu przekształcenia danych z powrotem do długiego formatu, można skorzystać z funkcji pivot_longer()
. W tym przypadku, dane dla każdego roku zostaną umieszczone w osobnym wierszu. Użycie tej funkcji wymaga podania nazw kolumn, które mają zostać przekształcone, oraz nazw kolumn, które mają zostać utworzone.
= pivot_longer(gm2_w, cols = `1950`:`2023`,
gm2_l names_to = "Rok", values_to = "Ocz_dl_zycia")
gm2_l
# A tibble: 14,060 × 3
Kraj Rok Ocz_dl_zycia
<chr> <chr> <dbl>
1 Australia 1950 69.4
2 Australia 1951 69.2
3 Australia 1952 69.5
# ℹ 14,057 more rows
Warto zwrócić uwagę, że w wyniku tej operacji wiele kolumn zostaje przekształconych w jedną kolumnę z nazwami grupującymi oraz jedną kolumnę z wartościami.
Wszystkie kolumny nie wybrane do przekształcenia zostaną automatycznie przeniesione do nowego zbioru danych. Widać to na poniższym przykładzie, gdzie kolumny Kraj
, Rok
, Region
, Populacja
, CO2_na_osobe
oraz Plodnosc
zostały przeniesione do nowego zbioru danych i zduplikowane tyle razy ile kolumn jest wybranych w argumencie cols
.
= pivot_longer(gm, cols = c(PKB_na_osobe, Ocz_dl_zycia),
gm_l2 names_to = "Zmienna", values_to = "Wartosc")
gm_l2
# A tibble: 28,120 × 8
Kraj Rok Region Populacja CO2_na_osobe Plodnosc Zmienna Wartosc
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <chr> <dbl>
1 Australia 1950 Azja 8180000 6.7 3.07 PKB_na_osobe 16400
2 Australia 1950 Azja 8180000 6.7 3.07 Ocz_dl_zycia 69.4
3 Australia 1951 Azja 8420000 7.04 3.06 PKB_na_osobe 16500
# ℹ 28,117 more rows
Zazwyczaj mówimy tutaj o danych, które mieszczą się w RAMie oraz nie muszą być przechowywane długotrwale w bazie danych.↩︎
Alternatywą dla tych dwóch narzędzi jest także pakiet polars dostępny zarówno w języku R, jak i Python.↩︎
Przecinek w poniższym zapytaniu jest równoznaczny operatorowi &
.↩︎
Zmianę nazw kolumn można również przeprowadzić po ich selekcji za pomocą funkcji rename()
.↩︎