Podstawy baz danych i operacji bazodanowych w R
Wstęp
Język R posiada zestaw funkcji i narzędzi pozwalających na realizację podstawowych operacji bazodanowych takich jak selekcja według wartości (WHERE), porządkowanie wg kolumn (ORDER BY), łączenie tabel (JOIN), grupowanie wg kolumn (GROUP BY) bezpośrednio na obiektach języka R ( data.frames ) jak również liczne narzędzia pozwalające na bezpośrednią pracę z bazami. W pierwszej części kursu zostaną omówione funkcje z podstawowej części R, które realizują funkcjonalność właściwą dla baz danych.
Druga część będzie dotyczyła łączenia się z bazami danych i pracy w R jako aplikacji klienckiej. Stosowanie baz danych zostanie omówione przy użyciu modelu bazodanowego SQLite. Zaletą SQLite jest plikowy charakter bazy danych, tzn wszystko przechowywane jest w pojedynczym pliku (podobnie jak w MS Access). Możliwości bazy danych w porównaniu z PostgreSQL są ograniczone, niemniej jednak w zupełności wystarczy do zrozumienia zasad współpracy pomiędzy bazami SQL a językiem R. SQLite jest obsługiwany przez bibliotekę dostępu do bazy danych SQLITE – większość systemów posiada własne sterowniki – , większość operacji wykonywana jest poprzez interface biblioteki DBI. Ta część zawiera przykład jak wykorzystać R do iteracyjnego przetwarzania danych geo-przestrzennych
Trzecia część to pakiet dplyr, który realizuje zaawansowane zadania zarządzania danymi, właściwe dla różnych źródeł danych.
Wymagane biblioteki: RSQLite, DBI, dplyr, raster, sp
Wymagana wiedza: systemy baz danych
Operacje bazowe w podstawowym R
Obiekt klasy data.frame pod wieloma względami przypomina tabele w systemach bazodanowych. Operacje zostaną przeprowadzone na dwóch wygenerowanych tabelach.
tab1 <- data.frame(uid=1:30,id=sample(seq(1:5),30,replace = TRUE),values=runif(30),randoms=rnorm(30))
str(tab1) #uid unikalny identyfikator, id: index łączenia, values, przypadkowe dane## 'data.frame': 30 obs. of 4 variables:
## $ uid : int 1 2 3 4 5 6 7 8 9 10 ...
## $ id : int 4 1 3 2 2 1 2 2 3 2 ...
## $ values : num 0.545 0.964 0.641 0.141 0.414 ...
## $ randoms: num -0.906 -0.186 0.292 -1.054 2.358 ...tab2 <- data.frame(id=2:6,name=LETTERS[6:10]) #id index łączenia, letters, przypadkowe dane
str(tab2)## 'data.frame': 5 obs. of 2 variables:
## $ id : int 2 3 4 5 6
## $ name: Factor w/ 5 levels "F","G","H","I",..: 1 2 3 4 5Selekcja wybranych wierszy (i kolumn)
Funkcja subset pozwala wybierać wiersze z bazy danych przy użyciu operacji logicznych, pozwala również ograniczyć liczbę zwracanych kolumn. Należy jednak pamiętać, że nie pozwala przekazać nazwy kolumny jako zmiennej. W takich przpypadkach należy stosować operator “[”, czyli selekcję na postawie wierszy i kolumn
subset(tab1,values>0.7,select=c(uid,values)) #wartości powyżej 7, zwracane dwie kolumny## uid values
## 2 2 0.9641837
## 6 6 0.8182826
## 12 12 0.9003016
## 14 14 0.8376349
## 16 16 0.7330914
## 20 20 0.8166352
## 22 22 0.7440120
## 23 23 0.7829315
## 24 24 0.9172113
## 27 27 0.8003697
## 28 28 0.7795700
## 29 29 0.7468058subset(tab1,id %in% c(2,5,6)) # odpowiednik IN## uid id values randoms
## 4 4 2 0.1410868 -1.0541172
## 5 5 2 0.4139021 2.3583319
## 7 7 2 0.1933112 0.3401115
## 8 8 2 0.2905198 1.4757478
## 10 10 2 0.3958425 0.4860427
## 11 11 5 0.6620248 -0.1175504
## 12 12 5 0.9003016 -1.4947893
## 19 19 2 0.3063339 0.9455850
## 22 22 2 0.7440120 1.9625366
## 24 24 5 0.9172113 -0.7481025
## 29 29 5 0.7468058 0.5968569
## 30 30 5 0.1969287 -1.1124436subset(tab1,id %in% c(2,5,6) & values<0.4) # wybór na podstawie kilku kryteriów## uid id values randoms
## 4 4 2 0.1410868 -1.0541172
## 7 7 2 0.1933112 0.3401115
## 8 8 2 0.2905198 1.4757478
## 10 10 2 0.3958425 0.4860427
## 19 19 2 0.3063339 0.9455850
## 30 30 5 0.1969287 -1.1124436# przkazanie nazwy kolumny jako zmiennej
column <- "values"
tab1[tab1[,column]>0.7,c("uid","values")]## uid values
## 2 2 0.9641837
## 6 6 0.8182826
## 12 12 0.9003016
## 14 14 0.8376349
## 16 16 0.7330914
## 20 20 0.8166352
## 22 22 0.7440120
## 23 23 0.7829315
## 24 24 0.9172113
## 27 27 0.8003697
## 28 28 0.7795700
## 29 29 0.7468058#lub w bardziej czytelnej formie
wiersze <- tab1[,column]>0.7 #wektor logiczny
kolumny <- c("uid","values") #wektor nazw
tab1[wiersze,kolumny]## uid values
## 2 2 0.9641837
## 6 6 0.8182826
## 12 12 0.9003016
## 14 14 0.8376349
## 16 16 0.7330914
## 20 20 0.8166352
## 22 22 0.7440120
## 23 23 0.7829315
## 24 24 0.9172113
## 27 27 0.8003697
## 28 28 0.7795700
## 29 29 0.7468058# lub to samo w funcji subset
subset(tab1, tab1[,column]<0.4,select=c("uid",column))## uid values
## 4 4 0.14108684
## 7 7 0.19331122
## 8 8 0.29051983
## 10 10 0.39584247
## 17 17 0.09844231
## 19 19 0.30633393
## 21 21 0.05148823
## 26 26 0.19958672
## 30 30 0.19692873Porządkowanie wyników
Do porządkowania tabel służy funkcja order zwraca ona indeksy tabeli ułożone w kolejności odpowiadającej kryterium porządkowania. Następnie wynik funkcji order można użyć do posortowania tabeli. Zaletą funkcji order jest to że pozwala użyć do porządkowania nie tylko danych z tabeli, chociaż taka operacja wydaje się najbardziej logiczna.
order(tab1$values)## [1] 21 17 4 7 30 26 8 19 10 5 13 9 1 25 3 18 11 15 16 22 29 28 23
## [24] 27 20 6 14 12 24 2head(tab1[order(tab1$values),])## uid id values randoms
## 21 21 3 0.05148823 -0.9034424
## 17 17 3 0.09844231 0.3548332
## 4 4 2 0.14108684 -1.0541172
## 7 7 2 0.19331122 0.3401115
## 30 30 5 0.19692873 -1.1124436
## 26 26 1 0.19958672 0.1132552order(tab1$id)## [1] 2 6 18 26 27 28 4 5 7 8 10 19 22 3 9 13 17 21 1 14 15 16 20
## [24] 23 25 11 12 24 29 30head(tab1[order(tab1$id),])## uid id values randoms
## 2 2 1 0.9641837 -0.185580957
## 6 6 1 0.8182826 -0.028977866
## 18 18 1 0.6592755 0.187086024
## 26 26 1 0.1995867 0.113255174
## 27 27 1 0.8003697 0.144457766
## 28 28 1 0.7795700 -0.004249032order(tab1$id,tab1$values) #pozwala na sortowanie na podstawie więcej niż jednej kolumny## [1] 26 18 28 27 6 2 4 7 8 19 10 5 22 21 17 13 9 3 1 25 15 16 23
## [24] 20 14 30 11 29 12 24head(tab1[order(tab1$id,tab1$values),])## uid id values randoms
## 26 26 1 0.1995867 0.113255174
## 18 18 1 0.6592755 0.187086024
## 28 28 1 0.7795700 -0.004249032
## 27 27 1 0.8003697 0.144457766
## 6 6 1 0.8182826 -0.028977866
## 2 2 1 0.9641837 -0.185580957order(tab1$id,-tab1$values) # prosta metoda sortowania wartości numerycznych rosnąco## [1] 2 6 27 28 18 26 22 5 10 19 8 7 4 3 9 13 17 21 14 20 23 16 15
## [24] 25 1 24 12 29 11 30order(tab1$id,tab1$values, decreasing=c(FALSE,TRUE),method="radix") #metoda "radix" pozwala wskazać osobne kryteria sortowania dla każdej z metod## [1] 2 6 27 28 18 26 22 5 10 19 8 7 4 3 9 13 17 21 14 20 23 16 15
## [24] 25 1 24 12 29 11 30head(tab1[order(tab1$id,-tab1$values),],7)## uid id values randoms
## 2 2 1 0.9641837 -0.185580957
## 6 6 1 0.8182826 -0.028977866
## 27 27 1 0.8003697 0.144457766
## 28 28 1 0.7795700 -0.004249032
## 18 18 1 0.6592755 0.187086024
## 26 26 1 0.1995867 0.113255174
## 22 22 2 0.7440120 1.962536615Złączenia między tabelami
Do łączenia tabel na podstawie wskazanych kolumn lub kolumn o wspólnej nazwie służy funkcja merge. Funkcja również pozwala wskazać kryteria łączenia, będące odpowiednikiem INNER, OUTER, LEFT, RIGHT
res <- merge(tab1,tab2,by.x="id",by.y="id") #proste połączenie INNER all.x i all.y FALSE
res## id uid values randoms name
## 1 2 4 0.14108684 -1.0541172 F
## 2 2 7 0.19331122 0.3401115 F
## 3 2 8 0.29051983 1.4757478 F
## 4 2 10 0.39584247 0.4860427 F
## 5 2 19 0.30633393 0.9455850 F
## 6 2 5 0.41390210 2.3583319 F
## 7 2 22 0.74401197 1.9625366 F
## 8 3 9 0.54403888 -1.2969220 G
## 9 3 13 0.41645699 -0.5127185 G
## 10 3 3 0.64085106 0.2920021 G
## 11 3 17 0.09844231 0.3548332 G
## 12 3 21 0.05148823 -0.9034424 G
## 13 4 1 0.54526057 -0.9061263 H
## 14 4 25 0.57777241 -0.1026882 H
## 15 4 15 0.67180739 1.7560196 H
## 16 4 16 0.73309136 1.6082184 H
## 17 4 23 0.78293150 -0.5997953 H
## 18 4 20 0.81663518 1.8061828 H
## 19 4 14 0.83763493 1.2974693 H
## 20 5 12 0.90030161 -1.4947893 I
## 21 5 11 0.66202480 -0.1175504 I
## 22 5 24 0.91721127 -0.7481025 I
## 23 5 29 0.74680581 0.5968569 I
## 24 5 30 0.19692873 -1.1124436 Inrow(res) # wynik nie zawiera elementów z indexem 6 (brak w tab1 i 1 (brak w tab2)## [1] 24res <- merge(tab1,tab2,by.x="id",by.y="id", all=TRUE) #połączenie OUTERall.x i all.y TRUE
res## id uid values randoms name
## 1 1 2 0.96418368 -0.185580957 <NA>
## 2 1 27 0.80036971 0.144457766 <NA>
## 3 1 28 0.77956998 -0.004249032 <NA>
## 4 1 6 0.81828256 -0.028977866 <NA>
## 5 1 18 0.65927549 0.187086024 <NA>
## 6 1 26 0.19958672 0.113255174 <NA>
## 7 2 4 0.14108684 -1.054117247 F
## 8 2 7 0.19331122 0.340111524 F
## 9 2 8 0.29051983 1.475747797 F
## 10 2 10 0.39584247 0.486042653 F
## 11 2 19 0.30633393 0.945585001 F
## 12 2 5 0.41390210 2.358331901 F
## 13 2 22 0.74401197 1.962536615 F
## 14 3 9 0.54403888 -1.296921971 G
## 15 3 13 0.41645699 -0.512718454 G
## 16 3 3 0.64085106 0.292002125 G
## 17 3 17 0.09844231 0.354833153 G
## 18 3 21 0.05148823 -0.903442350 G
## 19 4 1 0.54526057 -0.906126292 H
## 20 4 25 0.57777241 -0.102688172 H
## 21 4 15 0.67180739 1.756019587 H
## 22 4 16 0.73309136 1.608218373 H
## 23 4 23 0.78293150 -0.599795291 H
## 24 4 20 0.81663518 1.806182823 H
## 25 4 14 0.83763493 1.297469285 H
## 26 5 12 0.90030161 -1.494789290 I
## 27 5 11 0.66202480 -0.117550409 I
## 28 5 24 0.91721127 -0.748102464 I
## 29 5 29 0.74680581 0.596856924 I
## 30 5 30 0.19692873 -1.112443590 I
## 31 6 NA NA NA Jnrow(res) # wynik 30 rekordów z indexem 6 i 1 uzupełnaiją brakujące polaw wartościami NA## [1] 31head(merge(tab1,tab2,by.x="id",by.y="id", all.x=TRUE)) #LEFT JOIN## id uid values randoms name
## 1 1 2 0.9641837 -0.185580957 <NA>
## 2 1 27 0.8003697 0.144457766 <NA>
## 3 1 28 0.7795700 -0.004249032 <NA>
## 4 1 6 0.8182826 -0.028977866 <NA>
## 5 1 18 0.6592755 0.187086024 <NA>
## 6 1 26 0.1995867 0.113255174 <NA>tail(merge(tab1,tab2,by.x="id",by.y="id", all.y=TRUE)) #RIGHT JOIN## id uid values randoms name
## 20 5 12 0.9003016 -1.4947893 I
## 21 5 11 0.6620248 -0.1175504 I
## 22 5 24 0.9172113 -0.7481025 I
## 23 5 29 0.7468058 0.5968569 I
## 24 5 30 0.1969287 -1.1124436 I
## 25 6 NA NA NA JAgregacja tabel
Duże zbiory danych często znacznie łatwiej przedstawić w postaci zagregowanej. R pozwala na agregację danych przy pomocy standardowych funkcji grupujących, takich jak sum, mean, sd, max, pozwala również na stosowanie własnych funkcji. Do agregacji danych służą funkcje aggregate i by
aggregate(tab1$values,list(tab1$id),FUN=length) #odpowiednik count## Group.1 x
## 1 1 6
## 2 2 7
## 3 3 5
## 4 4 7
## 5 5 5aggregate(tab1$values,list(tab1$id),FUN=sd) #funkcja grupująca standard deviation ## Group.1 x
## 1 1 0.2654000
## 2 2 0.1978619
## 3 3 0.2641270
## 4 4 0.1151756
## 5 5 0.2927617aggregate(tab1$values,list(tab1$id),FUN=summary)## Group.1 x.Min. x.1st Qu. x.Median x.Mean x.3rd Qu. x.Max.
## 1 1 0.19960 0.68930 0.79000 0.70350 0.81380 0.96420
## 2 2 0.14110 0.24190 0.30630 0.35500 0.40490 0.74400
## 3 3 0.05149 0.09844 0.41650 0.35030 0.54400 0.64090
## 4 4 0.54530 0.62480 0.73310 0.70930 0.79980 0.83760
## 5 5 0.19690 0.66200 0.74680 0.68470 0.90030 0.91720by(tab1$values,factor(tab1$id),FUN=summary)## factor(tab1$id): 1
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.1996 0.6893 0.7900 0.7035 0.8138 0.9642
## --------------------------------------------------------
## factor(tab1$id): 2
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.1411 0.2419 0.3063 0.3550 0.4049 0.7440
## --------------------------------------------------------
## factor(tab1$id): 3
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.05149 0.09844 0.41650 0.35030 0.54400 0.64090
## --------------------------------------------------------
## factor(tab1$id): 4
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.5453 0.6248 0.7331 0.7093 0.7998 0.8376
## --------------------------------------------------------
## factor(tab1$id): 5
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.1969 0.6620 0.7468 0.6847 0.9003 0.9172res <- by(tab1,factor(tab1$id),FUN=function(x) coef(lm(values~randoms, data=x))[1],simplify = TRUE)
str(res) # wlasna fukcja zwraca współczynnik kierunkowy prostej## by [1:5(1d)] 0.744 0.243 0.346 0.672 0.703
## - attr(*, "dimnames")=List of 1
## ..$ factor(tab1$id): chr [1:5] "1" "2" "3" "4" ...
## - attr(*, "call")= language by.data.frame(data = tab1, INDICES = factor(tab1$id), FUN = function(x) coef(lm(values ~ randoms, data = x))[1], simplify = TRUE)Połączenia pomiędzy bazami danych i R
Tworzenie połączeń
Celem współpracy z bazami danych jest przede wszystkim swobodne korzystanie z różnych zbiorów danych bezpośrednio w środowisku R. R obsługuje kilkanaście typów baz danych (w tym wszystkie otwarto-źródłowe) oraz obsługuje mechanizm ODBC, co w praktyce oznacza możliwość ustanowienia dostępu do wszystkich typów baz. R nie pozwala na interaktywne zarządzanie bazą, tworzy jedynie obiekt połączenie, który trzeba wywoływać za każdym razem gdy uruchamiamy wyrażenie SQL. Po zakończeniu działań należy rozłączyć się z bazą.
Po utworzeniu połączenia możemy wywoływać dowolne funkcje db*. Należy jednak pamiętać, że niektóre funkcje nie są obsługiwane przez wszystkie systemy bazodanowe, dotyczy to zwłaszcza transakcji. Należy również pamiętać, że w języku SQL mamy dwa rodzaje wyrażeń (statements): takie które modyfikują stan bazy danych i nie zwracają wyników, oraz takie, które nie modyfikują bazy, ale zwracają wyniki. Dla uproszczenia, pierwszy typ będziemy określać jako wyrażenia drugi jako zapytania. Biblioteka DBI udostępnia wiele funkcji, które w wygodny sposób realizują podstawowe czynności związane z zarządzaniem bazami danych. Na przykład dbListTables(), zwraca listę tabel w bazie danych na którą wskazuje połączenie.
library(RSQLite)## Loading required package: DBIpath <- "DATA/poznan/" #ścieżka dostępu do danych
conn <- dbConnect(SQLite(),paste0(path,"poznan_shape.sqlite")) #obiekt połączenie
conn## <SQLiteConnection>dbListTables(conn) # funkcja wyświetla listę tabel## [1] "SpatialIndex"
## [2] "a"
## [3] "gauges"
## [4] "geom_cols_ref_sys"
## [5] "geometry_columns"
## [6] "geometry_columns_auth"
## [7] "geometry_columns_field_infos"
## [8] "geometry_columns_statistics"
## [9] "geometry_columns_time"
## [10] "idx_gauges_geometry"
## [11] "idx_gauges_geometry_node"
## [12] "idx_gauges_geometry_parent"
## [13] "idx_gauges_geometry_rowid"
## [14] "pomiary"
## [15] "poznan_shape1"
## [16] "spatial_ref_sys"
## [17] "spatialite_history"
## [18] "sql_statements_log"
## [19] "sqlite_sequence"
## [20] "tab"
## [21] "vector_layers"
## [22] "vector_layers_auth"
## [23] "vector_layers_field_infos"
## [24] "vector_layers_statistics"
## [25] "views_geometry_columns"
## [26] "views_geometry_columns_auth"
## [27] "views_geometry_columns_field_infos"
## [28] "views_geometry_columns_statistics"
## [29] "virts_geometry_columns"
## [30] "virts_geometry_columns_auth"
## [31] "virts_geometry_columns_field_infos"
## [32] "virts_geometry_columns_statistics"dbDisconnect(conn)## [1] TRUERealizacja zapytań
Po ustanowieniu połączenia możemy wykonywać dowolne polecenia zarządzające bazami: tworzyć, przeglądać usuwać, itp. Możemy też zapisywać ramki danych bezpośrednio do bazy danych oraz wczytywać zawartość bazy danych do programu realizując zapytania SQL, zgodne z danym systemem bazodanowym.
#create data.frame first:
Stacja <- 1:10
Data <- seq(as.Date("2015-10-19"),as.Date("2015-10-28"),by="day") # Data nie jest podstawowym typem danych, jest przechowywana jako tekst lub jako timestamp
CO <- runif(10)
SO <- runif(10,3,5)
df <- data.frame(Stacja,Data,CO,SO)
conn <- dbConnect(SQLite(),paste0(path,"poznan_shape.sqlite"))
if(dbExistsTable(conn,"tab")) dbRemoveTable(conn,"tab") #usninięcie tabeli jeżeli istnieje## [1] TRUEquery <- ("CREATE TABLE tab (Stacja integer, Data DATE, CO double precision, SO double precision)")
dbSendStatement(conn,query) #pozwala to kontrolować stukturę bazy## <SQLiteResult>dbWriteTable(conn,"tab",df,append=TRUE) #dodajemy dane do istniejącej tabeli## [1] TRUEdbListFields(conn,"tab")## [1] "Stacja" "Data" "CO" "SO"dbWriteTable(conn,"tab2",df,overwrite=TRUE) #prostrza forma, ale nie mamy kontroli nad tworzoną tabelą## [1] TRUEdbListFields(conn,"tab2")## [1] "Stacja" "Data" "CO" "SO"if(dbExistsTable(conn,"tab2")) dbRemoveTable(conn,"tab2") # usninięcie tabeli## [1] TRUECzytanie całych tabel i całych zapytań
Oczywiście najważniejsze funkcje, to takie, które pozwalają importować dane z bazy danych. Należy jednak pamiętać że bazy danych mogą przechowywać ogromne zbiory danych, które nie zawsze potrzebujemy w całości. Realizacja zapytań bezpośrednio w języku SQL pozwoli znacząco ograniczyć ilość przesyłanych danych i jest znacząco szybsza w porównaniu z narzędziami języka R. W przypadku, gdy jedynie chcemy wczytać zawartość tabeli do R można skorzystać z polecenia dbReadTable(), można je stosować jedynie w sytuacji, gdy wiemy że rozmiar tabeli nie jest duży. W przypadku, gdy potrzebujemy jedynie część danych, możemy skorzystać z plecenia, dbGetQuery(), które zrealizuje dowolne zapytanie zgodne z danym dialektem języka SQL, zwróci wyniki i wyczyści bufor. Podobnie jak w poprzednim przypadku, należy się upewnić co do oczekiwanego rozmiaru wyników.
df_1 <- dbReadTable(conn,"tab")
str(df_1) #należy zwrócić uwagę na typ daty, jest to liczba względem UUT (universal unix time):## 'data.frame': 10 obs. of 4 variables:
## $ Stacja: int 1 2 3 4 5 6 7 8 9 10
## $ Data : int 16727 16728 16729 16730 16731 16732 16733 16734 16735 16736
## $ CO : num 0.0108 0.6136 0.6342 0.8369 0.7902 ...
## $ SO : num 4.92 3.68 3.4 4.78 3.86 ...as.Date(df_1$Data,origin="1970-01-01")## [1] "2015-10-19" "2015-10-20" "2015-10-21" "2015-10-22" "2015-10-23"
## [6] "2015-10-24" "2015-10-25" "2015-10-26" "2015-10-27" "2015-10-28"#zapytanie
query <- "SELECT * FROM tab WHERE Stacja>1" # czyli wszystkie
df2 <- dbGetQuery(conn,query)
str(df2)## 'data.frame': 9 obs. of 4 variables:
## $ Stacja: int 2 3 4 5 6 7 8 9 10
## $ Data : int 16728 16729 16730 16731 16732 16733 16734 16735 16736
## $ CO : num 0.614 0.634 0.837 0.79 0.839 ...
## $ SO : num 3.68 3.4 4.78 3.86 4.39 ...Czytanie danych porcjami
Najbezpieczniejszą metodą jest wczytywanie danych porcjami, która wymaga jednak kilku funkcji i pętli.
results <- dbSendQuery(conn,query)
results## <SQLiteResult>chunk <- dbFetch(results,n=2) #tylko dwa wiersze, możemy sterować ilością danych
chunk## Stacja Data CO SO
## 1 2 16728 0.6135939 3.681608
## 2 3 16729 0.6341568 3.399369dbClearResult(results)## [1] TRUEdf_4 <- data.frame(Stacja=integer(0),CO=numeric(0))
query <- "SELECT Stacja,CO FROM tab"
results <- dbSendQuery(conn,query)
k <- 0
while(!dbHasCompleted(results)) {
df_4 <- rbind(df_4,dbFetch(results,4)) # po cztery wiersze
k <- k+1
}
dbClearResult(results)## [1] TRUEstr(df_4) ## 'data.frame': 10 obs. of 2 variables:
## $ Stacja: int 1 2 3 4 5 6 7 8 9 10
## $ CO : num 0.0108 0.6136 0.6342 0.8369 0.7902 ...k # trzy iteracje## [1] 3dbDisconnect(conn) # zakończenie sesji. ## [1] TRUEPołączenia pomiędzy tabelami a geoprzestrzennymi obiektami wektorowymi w R
Łączenie z bazami danych można wykorzystać do iteracyjnego przetwarzania tabelarycznych danych i łączenia ich z danymi geo-przestrzennymi. Przykład pozwoli nam zilustrować jak wykorzystać zapytania SQL do pobierania zagregowanych poleceniem SQL danych i łączeniem ich ze zbiorem danych punktowych.
W pierwszym kroku wczytamy pliki wektorowe: granice miasta i lokalizację punktów. Pliki wektorowe są przechowywane w formacie SpatialLite - jako część bazy danych SQLite. Jest to format zbliżony do PostGIS, ale o mniejszych możliwościach.
library(rgdal)## Loading required package: sp## rgdal: version: 1.1-10, (SVN revision 622)
## Geospatial Data Abstraction Library extensions to R successfully loaded
## Loaded GDAL runtime: GDAL 2.1.0, released 2016/04/25
## Path to GDAL shared files: /usr/share/gdal/2.1
## Loaded PROJ.4 runtime: Rel. 4.9.2, 08 September 2015, [PJ_VERSION: 492]
## Path to PROJ.4 shared files: (autodetected)
## Linking to sp version: 1.2-3dsn <- "DATA/poznan/poznan_shape.sqlite"
miasto <- readOGR(dsn,layer="poznan_shape1")## OGR data source with driver: SQLite
## Source: "DATA/poznan/poznan_shape.sqlite", layer: "poznan_shape1"
## with 1 features
## It has 9 fieldspunkty <- readOGR(dsn,layer="gauges")## OGR data source with driver: SQLite
## Source: "DATA/poznan/poznan_shape.sqlite", layer: "gauges"
## with 16 features
## It has 2 fieldsplot(miasto)
points(punkty)
W kolejnym punkcie pobierzemy dane z tabeli pomiary, grupujące jest po stacjach. Pobierzemy dane tylko dla 1 miesiąca.
conn <- dbConnect(SQLite(),paste0(path,"poznan_shape.sqlite"))
dbListFields(conn,"pomiary") #opis tabeli## [1] "Stacja" "Data" "CO" "SO"query_months <- "SELECT DISTINCT strftime('%Y-%m', Data) FROM pomiary" #ROK-MIESIĄC gwarantuje unikalność
months <- dbGetQuery(conn,query_months) #wybieramy unikalną listę miesięcy
month <- months[[1]][1] # wybieramy 1 miesiąc
month## [1] "2008-01"query <- paste0("SELECT Stacja , avg(CO), avg(SO) FROM pomiary WHERE strftime('%Y-%m', Data)='",month,"' GROUP BY Stacja")
dff <- dbGetQuery(conn,query) #zapytanie zawiera zmienną miesiąc
names(dff) <- c("Stacja","CO","SO")
punkty$CO <- dff$CO[match(dff$Stacja,punkty$id)] #używamy funckji match aby przypisać właściwe elementy do siebie
plot(miasto)
points(punkty,pch=20,cex=punkty$CO*2,col=1)
dbDisconnect(conn) # zakończenie sesji. ## [1] TRUEPrzykład: zastosowanie połączeń w pętli
w następnym kroku, po upewnieniu się że pojedyncza iteracja działa, użyjemy pętli, aby wykonać mapy dla wszystkich miesięcy w naszym zbiorze. W tym celu przekierujemy wynik operacji graficznych do pliku pdf. Przykład ten bardzo dobrze zilustruje zalety programistycznego podejścia do analizy danych, pozwoli raz utworzoną i przetestowaną procedurę zamienić w pętlę i wykonać dowolną ilość razy. Zamieszczony przykład jest bardzo mały, ale możliwości takiego przetwarzania danych możemy docenić przy znacznie większych przykładach.
conn <- dbConnect(SQLite(),paste0(path,"poznan_shape.sqlite"))
query_months <- "SELECT DISTINCT strftime('%Y-%m', Data) FROM pomiary"
months <- dbGetQuery(conn,query_months) #wybieramy unikalną listę miesięcy
pdf("bymonth.pdf") #Włączenie urządzenia PDF
for(month in months[[1]]) {
query <- paste0("SELECT Stacja , avg(CO), avg(SO) FROM pomiary WHERE strftime('%Y-%m', Data)='",month,"' GROUP BY Stacja")
dff <- dbGetQuery(conn,query) #zapytanie zawiera zmienną miesiąc
names(dff) <- c("Stacja","CO","SO")
punkty$CO <- dff$CO[match(dff$Stacja,punkty$id)]
punkty$SO <- dff$SO[match(dff$Stacja,punkty$id)]
par(mfcol=c(1,2))
plot(miasto)
points(punkty,pch=20,cex=punkty$CO*2,col=1)
plot(miasto)
points(punkty,pch=20,cex=punkty$SO*3,col=2)
}
dev.off() # wyłączenie urządzenia## png
## 2dbDisconnect(conn) # zakończenie sesji.## [1] TRUEZarządzanie danymi przy pomocy biblioteki dplyr.
Biblioteka dplyr wprowadza wygodny i przejrzysty sposób zarządzania dużymi zbiorami danych, wprowadzając jednocześnie własną gramatykę (gramar of data manipulation). Podstawowym obiektem dla biblioteki dplyr jest obiekt tbl (tabela), który może zostać utworzony na postawie istniejącej data.frame lub zewnętrznego zbioru danych. Wykorzystamy wcześniej utworzone obiekty tbl1 i tbl2, oraz połączenie z bazą danych SQLite. Zaletą wykorzystania baz danych jest to że dopóki nie zostanie wykonana operacja na danych, żadne dane nie zostaną wczytane do R.
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, uniontb_tab1 <- as.tbl(tab1)
str(tb_tab1) # Jak widać struktura jest dokładne taka sama jak data.frame ## Classes 'tbl_df', 'tbl' and 'data.frame': 30 obs. of 4 variables:
## $ uid : int 1 2 3 4 5 6 7 8 9 10 ...
## $ id : int 4 1 3 2 2 1 2 2 3 2 ...
## $ values : num 0.545 0.964 0.641 0.141 0.414 ...
## $ randoms: num -0.906 -0.186 0.292 -1.054 2.358 ...conn <- src_sqlite(paste0(path,"poznan_shape.sqlite"))
tb_sqlite <- tbl(conn,"pomiary")
str(tb_sqlite) # na tym etapie obiekt nie zawiera danych.## List of 2
## $ src:List of 2
## ..$ con :Formal class 'SQLiteConnection' [package "RSQLite"] with 5 slots
## .. .. ..@ Id :<externalptr>
## .. .. ..@ dbname : chr "DATA/poznan/poznan_shape.sqlite"
## .. .. ..@ loadable.extensions: logi TRUE
## .. .. ..@ flags : int 6
## .. .. ..@ vfs : chr ""
## ..$ path: chr "DATA/poznan/poznan_shape.sqlite"
## ..- attr(*, "class")= chr [1:3] "src_sqlite" "src_sql" "src"
## $ ops:List of 3
## ..$ src :List of 2
## .. ..$ con :Formal class 'SQLiteConnection' [package "RSQLite"] with 5 slots
## .. .. .. ..@ Id :<externalptr>
## .. .. .. ..@ dbname : chr "DATA/poznan/poznan_shape.sqlite"
## .. .. .. ..@ loadable.extensions: logi TRUE
## .. .. .. ..@ flags : int 6
## .. .. .. ..@ vfs : chr ""
## .. ..$ path: chr "DATA/poznan/poznan_shape.sqlite"
## .. ..- attr(*, "class")= chr [1:3] "src_sqlite" "src_sql" "src"
## ..$ x :Classes 'ident', 'sql', 'character' chr "pomiary"
## ..$ vars: chr [1:4] "Stacja" "Data" "CO" "SO"
## ..- attr(*, "class")= chr [1:3] "op_base_remote" "op_base" "op"
## - attr(*, "class")= chr [1:4] "tbl_sqlite" "tbl_sql" "tbl_lazy" "tbl"tb_sqlite # ta opercja zwraca tylko 10 pierwszych wierszy## Source: query [?? x 4]
## Database: sqlite 3.8.6 [DATA/poznan/poznan_shape.sqlite]
##
## Stacja Data CO SO
## <int> <chr> <dbl> <dbl>
## 1 11 2008-01-04 1.1510821 0.5248770
## 2 2 2008-01-04 1.0627946 0.3751408
## 3 3 2008-01-04 1.3878149 0.7046057
## 4 4 2008-01-04 0.7307420 0.5325238
## 5 1 2008-01-04 0.8809241 0.4165683
## 6 10 2008-01-04 1.0355468 0.4567587
## 7 13 2008-01-04 0.8962694 0.3267317
## 8 14 2008-01-04 0.7627089 0.4783503
## 9 15 2008-01-04 0.7893310 0.5492118
## 10 16 2008-01-04 0.7334241 0.4191454
## # ... with more rowsBiblioteka dplyr dostarcza własny zestaw funkcji, który realizuje wszystkie zadania bazodanowe. Zgodnie z filozofią, która leży u jej podstaw biblioteka zawiera liczne funkcje, realizujące pojedyncze zadania.
Selekcja i operator strumieniowania
W celu selekcji danych stosujemy funkcje filter selekcja na podstawie reguł logicznych slice aby wybierać na podstawie indeksów oraz select aby wybierać kolumny.
tbl_vars(tb_tab1) ## [1] "uid" "id" "values" "randoms"filter(tb_tab1,randoms>0) #zwraca wszystkie kolumny## # A tibble: 16 × 4
## uid id values randoms
## <int> <int> <dbl> <dbl>
## 1 3 3 0.64085106 0.2920021
## 2 5 2 0.41390210 2.3583319
## 3 7 2 0.19331122 0.3401115
## 4 8 2 0.29051983 1.4757478
## 5 10 2 0.39584247 0.4860427
## 6 14 4 0.83763493 1.2974693
## 7 15 4 0.67180739 1.7560196
## 8 16 4 0.73309136 1.6082184
## 9 17 3 0.09844231 0.3548332
## 10 18 1 0.65927549 0.1870860
## 11 19 2 0.30633393 0.9455850
## 12 20 4 0.81663518 1.8061828
## 13 22 2 0.74401197 1.9625366
## 14 26 1 0.19958672 0.1132552
## 15 27 1 0.80036971 0.1444578
## 16 29 5 0.74680581 0.5968569select(filter(tb_tab1,randoms>0),id,values) #zwraca dwie nazwane kolumny## # A tibble: 16 × 2
## id values
## <int> <dbl>
## 1 3 0.64085106
## 2 2 0.41390210
## 3 2 0.19331122
## 4 2 0.29051983
## 5 2 0.39584247
## 6 4 0.83763493
## 7 4 0.67180739
## 8 4 0.73309136
## 9 3 0.09844231
## 10 1 0.65927549
## 11 2 0.30633393
## 12 4 0.81663518
## 13 2 0.74401197
## 14 1 0.19958672
## 15 1 0.80036971
## 16 5 0.74680581select(filter(tb_tab1,randoms>0),c(1,3)) #zwraca kolumny na podstawie indeksu## # A tibble: 16 × 2
## uid values
## <int> <dbl>
## 1 3 0.64085106
## 2 5 0.41390210
## 3 7 0.19331122
## 4 8 0.29051983
## 5 10 0.39584247
## 6 14 0.83763493
## 7 15 0.67180739
## 8 16 0.73309136
## 9 17 0.09844231
## 10 18 0.65927549
## 11 19 0.30633393
## 12 20 0.81663518
## 13 22 0.74401197
## 14 26 0.19958672
## 15 27 0.80036971
## 16 29 0.74680581select(slice(tb_tab1,c(2,3,7:21)),c(1,3,4)) #zwraca wiersze i kolumny na podstawie indeksu## # A tibble: 17 × 3
## uid values randoms
## <int> <dbl> <dbl>
## 1 2 0.96418368 -0.1855810
## 2 3 0.64085106 0.2920021
## 3 7 0.19331122 0.3401115
## 4 8 0.29051983 1.4757478
## 5 9 0.54403888 -1.2969220
## 6 10 0.39584247 0.4860427
## 7 11 0.66202480 -0.1175504
## 8 12 0.90030161 -1.4947893
## 9 13 0.41645699 -0.5127185
## 10 14 0.83763493 1.2974693
## 11 15 0.67180739 1.7560196
## 12 16 0.73309136 1.6082184
## 13 17 0.09844231 0.3548332
## 14 18 0.65927549 0.1870860
## 15 19 0.30633393 0.9455850
## 16 20 0.81663518 1.8061828
## 17 21 0.05148823 -0.9034424Pakiet dplyr wprowadza własny operator strumieniowania (pipe) %>%, który pozwala na przekierowanie wyników działania jednej funkcji do drugiej. Pozwala to uprościć niedogodności programowania funkcyjnego związanego z licznymi zagnieżdżeniami funkcji. W dalszej części lekcji będziemy się posługiwać strumieniami danych, gdy będzie to możliwe. Operacje te możemy stosować zarówno na danych wewnątrz R jak i podłączonych zewnętrznych źródłach bazodanowych. W przypadku zewnętrznych baz danych do konwersji i przetwarzania danych należy używać funkcji właściwych dla bazy danych a nie R.
filter(tb_tab1,randoms>0) %>% select(id,values) # wyniki filtrowania przesyłany jest do funkcji select, select ma bardzo rozbudowane opcje, zob. dokumentację## # A tibble: 16 × 2
## id values
## <int> <dbl>
## 1 3 0.64085106
## 2 2 0.41390210
## 3 2 0.19331122
## 4 2 0.29051983
## 5 2 0.39584247
## 6 4 0.83763493
## 7 4 0.67180739
## 8 4 0.73309136
## 9 3 0.09844231
## 10 1 0.65927549
## 11 2 0.30633393
## 12 4 0.81663518
## 13 2 0.74401197
## 14 1 0.19958672
## 15 1 0.80036971
## 16 5 0.74680581filter(tb_sqlite,date(Data)>date("2011-10-11")) #date() (SQLite) a nie as.Date() (R)## Source: query [?? x 4]
## Database: sqlite 3.8.6 [DATA/poznan/poznan_shape.sqlite]
##
## Stacja Data CO SO
## <int> <chr> <dbl> <dbl>
## 1 11 2011-10-12 0.5907032 0.5015111
## 2 2 2011-10-12 0.7637246 0.3304164
## 3 3 2011-10-12 0.6349038 0.4681271
## 4 4 2011-10-12 0.6479466 0.3719888
## 5 1 2011-10-12 0.7945937 0.3131832
## 6 10 2011-10-12 0.6399107 0.3901217
## 7 13 2011-10-12 0.6109653 0.4451788
## 8 14 2011-10-12 0.6615238 0.5282662
## 9 15 2011-10-12 0.5556472 0.3448129
## 10 16 2011-10-12 0.5827186 0.2423007
## # ... with more rowsW przypadku, gdy polecenia biblioteki dplyr nie wystarczają, lub chcemy mieć pełną kontrolę nad realizowanym zapytaniem, możemy wykonać pełne zapytanie SQL, przy pomocy funkcji sql():
require(dplyr)
path <- "DATA/poznan/"
conn <- src_sqlite(paste0(path,"poznan_shape.sqlite"))
tb_sqlite_raw <- tbl(conn,sql("SELECT DISTINCT Stacja FROM pomiary")) #raczej proste
tb_sqlite_raw ### Source: query [?? x 1]
## Database: sqlite 3.8.6 [DATA/poznan/poznan_shape.sqlite]
##
## Stacja
## <int>
## 1 11
## 2 2
## 3 3
## 4 4
## 5 1
## 6 10
## 7 13
## 8 14
## 9 15
## 10 16
## # ... with more rowsPorządkowanie danych
W pakiecie dplyr porządkowanie danych wykonywane jest przy pomocy funkcji arrange().
head(arrange(tb_tab1,id,desc(randoms)),10)## # A tibble: 10 × 4
## uid id values randoms
## <int> <int> <dbl> <dbl>
## 1 18 1 0.6592755 0.187086024
## 2 27 1 0.8003697 0.144457766
## 3 26 1 0.1995867 0.113255174
## 4 28 1 0.7795700 -0.004249032
## 5 6 1 0.8182826 -0.028977866
## 6 2 1 0.9641837 -0.185580957
## 7 5 2 0.4139021 2.358331901
## 8 22 2 0.7440120 1.962536615
## 9 8 2 0.2905198 1.475747797
## 10 19 2 0.3063339 0.945585001conn <- src_sqlite(paste0(path,"poznan_shape.sqlite"))
tb_sqlite <- tbl(conn,"pomiary")
filter(tb_sqlite,date(Data)>date("2011-10-11")) %>% arrange(Stacja,CO) #dane nie zostały jeszcze pobrane## Source: query [?? x 4]
## Database: sqlite 3.8.6 [DATA/poznan/poznan_shape.sqlite]
##
## Stacja Data CO SO
## <int> <chr> <dbl> <dbl>
## 1 1 2013-03-13 0.4506863 0.3186449
## 2 1 2013-01-24 0.4528688 0.2097058
## 3 1 2013-03-15 0.4539936 0.2276587
## 4 1 2013-01-25 0.4542535 0.1870386
## 5 1 2013-02-07 0.4559055 0.2621787
## 6 1 2013-03-12 0.4573856 0.4095434
## 7 1 2013-03-14 0.4631074 0.2451028
## 8 1 2013-03-10 0.4665158 0.4342294
## 9 1 2013-03-02 0.4666764 0.3060861
## 10 1 2013-03-16 0.4674210 0.3177501
## # ... with more rowsŁączenie tabel
Łączenie tabel realizowane jest przy pomocy poleceń wprost zaczerpniętych z baz danych. Powtórzymy przykłady zrealizowane uprzednio przy pomocy funkcji merge() funkcjami z pakietu dplyr. Rola poszczególnych typów złączeń wyjaśniona jest w dokumentacji i w dowolnym podręczniku języka SQL.
tb_tab2 <- as.tbl(tab2) #dodajemy tab2
inner_join(tb_tab1,tb_tab2,by=c("id"="id"))## # A tibble: 24 × 5
## uid id values randoms name
## <int> <int> <dbl> <dbl> <fctr>
## 1 1 4 0.5452606 -0.9061263 H
## 2 3 3 0.6408511 0.2920021 G
## 3 4 2 0.1410868 -1.0541172 F
## 4 5 2 0.4139021 2.3583319 F
## 5 7 2 0.1933112 0.3401115 F
## 6 8 2 0.2905198 1.4757478 F
## 7 9 3 0.5440389 -1.2969220 G
## 8 10 2 0.3958425 0.4860427 F
## 9 11 5 0.6620248 -0.1175504 I
## 10 12 5 0.9003016 -1.4947893 I
## # ... with 14 more rowsfull_join(tb_tab1,tb_tab2,by=c("id"="id")) #outer join## # A tibble: 31 × 5
## uid id values randoms name
## <int> <int> <dbl> <dbl> <fctr>
## 1 1 4 0.5452606 -0.90612629 H
## 2 2 1 0.9641837 -0.18558096 NA
## 3 3 3 0.6408511 0.29200212 G
## 4 4 2 0.1410868 -1.05411725 F
## 5 5 2 0.4139021 2.35833190 F
## 6 6 1 0.8182826 -0.02897787 NA
## 7 7 2 0.1933112 0.34011152 F
## 8 8 2 0.2905198 1.47574780 F
## 9 9 3 0.5440389 -1.29692197 G
## 10 10 2 0.3958425 0.48604265 F
## # ... with 21 more rowsleft_join(tb_tab1,tb_tab2,by=c("id"="id"))## # A tibble: 30 × 5
## uid id values randoms name
## <int> <int> <dbl> <dbl> <fctr>
## 1 1 4 0.5452606 -0.90612629 H
## 2 2 1 0.9641837 -0.18558096 NA
## 3 3 3 0.6408511 0.29200212 G
## 4 4 2 0.1410868 -1.05411725 F
## 5 5 2 0.4139021 2.35833190 F
## 6 6 1 0.8182826 -0.02897787 NA
## 7 7 2 0.1933112 0.34011152 F
## 8 8 2 0.2905198 1.47574780 F
## 9 9 3 0.5440389 -1.29692197 G
## 10 10 2 0.3958425 0.48604265 F
## # ... with 20 more rowstail(right_join(tb_tab1,tb_tab2,by=c("id"="id")))## # A tibble: 6 × 5
## uid id values randoms name
## <int> <int> <dbl> <dbl> <fctr>
## 1 11 5 0.6620248 -0.1175504 I
## 2 12 5 0.9003016 -1.4947893 I
## 3 24 5 0.9172113 -0.7481025 I
## 4 29 5 0.7468058 0.5968569 I
## 5 30 5 0.1969287 -1.1124436 I
## 6 NA 6 NA NA JAgregacja danych
Agregacja danych wymaga w pierwszej kolejności opracowanie obiektu agregującego, a następnie wykonanie do funkcji summarize() połączonej z dowolną funkcją przetwarzania danych z pakietu dplyr. Do zbudowania obiektu grupującego służy funkcja group_by().
group_by(tb_tab1,id) %>% summarise(mean(values),mean(randoms))## # A tibble: 5 × 3
## id `mean(values)` `mean(randoms)`
## <int> <dbl> <dbl>
## 1 1 0.7035447 0.03766518
## 2 2 0.3550012 0.93060546
## 3 3 0.3502555 -0.41324950
## 4 4 0.7093048 0.69418290
## 5 5 0.6846544 -0.57520577Ewaluacja wyrażeń i pobranie wyników
W przypadku zewnętrznego źródła danych biblioteka dplyr nie pobiera danych do momentu, w którym jest to absolutnie konieczne. Pobranie danych następuje w momencie, gdy zostaną one poddane przeliczeniom, lub jawnie poprzez wywołanie funkcji collect(), compute() lub collapse(). Ponadto funkcja explain() pozwala przeanalizować zapytanie SQL przygotowane przez polecenia biblioteki dplyr.
conn <- src_sqlite(paste0(path,"poznan_shape.sqlite"))
tb_sqlite <- tbl(conn,"pomiary") %>% #SELECT
filter(date(Data)>date("2011-10-11")) %>% #WHERE
arrange(Stacja,CO) #ORDER BY
explain(tb_sqlite) #proszę zwrócić uwagę że dplyr buduje zapytania zagnieżdzone## <SQL>
## SELECT *
## FROM (SELECT *
## FROM `pomiary`
## WHERE (DATE(`Data`) > DATE('2011-10-11')))
## ORDER BY `Stacja`, `CO`## ## <PLAN>
## addr opcode p1 p2 p3 p4 p5 comment
## 1 0 Init 0 35 0 00 <NA>
## 2 1 SorterOpen 2 4 0 k(2,B,B) 00 <NA>
## 3 2 OpenRead 1 4323 0 4 00 <NA>
## 4 3 Rewind 1 22 0 00 <NA>
## 5 4 Column 1 1 2 00 <NA>
## 6 5 Function 0 2 1 date(-1) 01 <NA>
## 7 6 Le 3 21 1 6a <NA>
## 8 7 Column 1 0 4 00 <NA>
## 9 8 Column 1 1 5 00 <NA>
## 10 9 Column 1 2 6 00 <NA>
## 11 10 RealAffinity 6 0 0 00 <NA>
## 12 11 Column 1 3 7 00 <NA>
## 13 12 RealAffinity 7 0 0 00 <NA>
## 14 13 MakeRecord 4 4 1 00 <NA>
## 15 14 Column 1 0 9 00 <NA>
## 16 15 Column 1 2 10 00 <NA>
## 17 16 RealAffinity 10 0 0 00 <NA>
## 18 17 Sequence 2 11 0 00 <NA>
## 19 18 Move 1 12 1 00 <NA>
## 20 19 MakeRecord 9 4 8 00 <NA>
## 21 20 SorterInsert 2 8 0 00 <NA>
## 22 21 Next 1 4 0 01 <NA>
## 23 22 Close 1 0 0 00 <NA>
## 24 23 OpenPseudo 3 1 4 00 <NA>
## 25 24 OpenPseudo 4 13 4 00 <NA>
## 26 25 SorterSort 2 34 0 00 <NA>
## 27 26 SorterData 2 13 0 00 <NA>
## 28 27 Column 4 3 1 20 <NA>
## 29 28 Column 3 0 4 20 <NA>
## 30 29 Column 3 1 5 00 <NA>
## 31 30 Column 3 2 6 00 <NA>
## 32 31 Column 3 3 7 00 <NA>
## 33 32 ResultRow 4 4 0 00 <NA>
## 34 33 SorterNext 2 26 0 00 <NA>
## 35 34 Halt 0 0 0 00 <NA>
## 36 35 Transaction 0 0 197 0 01 <NA>
## 37 36 TableLock 0 4323 0 pomiary 00 <NA>
## 38 37 String8 0 14 0 2011-10-11 00 <NA>
## 39 38 Function 1 14 3 date(-1) 01 <NA>
## 40 39 Goto 0 1 0 00 <NA>result <- collect(tb_sqlite) #collect po prostu pobiera dane i zapisuje je do R
str(result)## Classes 'tbl_df', 'tbl' and 'data.frame': 8656 obs. of 4 variables:
## $ Stacja: int 1 1 1 1 1 1 1 1 1 1 ...
## $ Data : chr "2013-03-13" "2013-01-24" "2013-03-15" "2013-01-25" ...
## $ CO : num 0.451 0.453 0.454 0.454 0.456 ...
## $ SO : num 0.319 0.21 0.228 0.187 0.262 ...result <- compute(tb_sqlite) #tworzy tymczasową tabelę w bazie danych, nie transferuje danych do R
result <- result %>% filter(CO<0.45)
collect(result) #tylko 6 wierszy, duża oszczędność ## # A tibble: 6 × 4
## Stacja Data CO SO
## <int> <chr> <dbl> <dbl>
## 1 6 2013-01-23 0.4447429 0.2645908
## 2 13 2013-01-23 0.4466056 0.1651483
## 3 13 2013-03-10 0.4473265 0.2945373
## 4 13 2013-01-22 0.4488970 0.4231075
## 5 15 2013-01-22 0.4483700 0.4101678
## 6 16 2013-01-27 0.4490881 0.1729668result <- collapse(tb_sqlite) #tworzy zapytanie SQL, nie realizuje żadnych obliczeń
result[[2]]$x$x## <SQL> SELECT *
## FROM (SELECT *
## FROM `pomiary`
## WHERE (DATE(`Data`) > DATE('2011-10-11')))
## ORDER BY `Stacja`, `CO`