5.6 Bases de datos con dplyr
Para poder usar tablas en bases de datos relacionales con dplyr hay que emplear el paquete dbplyr (convierte automáticamente el código de dplyr en consultas SQL).
Algunos enlaces:
5.6.1 Ejemplos
Como ejemplo emplearemos la base de datos de SQLite Sample Database Tutorial, almacenada en el archivo chinook.db.
# install.packages('dbplyr')
library(dplyr)
library(dbplyr)En primer lugar hay que conectar la base de datos:
chinook <- DBI::dbConnect(RSQLite::SQLite(), "datos/chinook.db")Podemos listar las tablas:
src_dbi(chinook)## src: sqlite 3.36.0 [D:\OneDrive - Universidade da Coruña\__Actual\__IGE\_book_notasr\datos\chinook.db]
## tbls: albums, artists, customers, employees, genres, invoice_items, invoices,
## media_types, playlist_track, playlists, sqlite_sequence, sqlite_stat1, tracksPara enlazar una tabla:
invoices <- tbl(chinook, "invoices")
invoices## # Source: table<invoices> [?? x 9]
## # Database: sqlite 3.36.0 [D:\OneDrive - Universidade da
## # Coruña\__Actual\__IGE\_book_notasr\datos\chinook.db]
## InvoiceId CustomerId InvoiceD~1 Billi~2 Billi~3 Billi~4 Billi~5 Billi~6 Total
## <int> <int> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
## 1 1 2 2009-01-0~ Theodo~ Stuttg~ <NA> Germany 70174 1.98
## 2 2 4 2009-01-0~ Ullevå~ Oslo <NA> Norway 0171 3.96
## 3 3 8 2009-01-0~ Grétry~ Brusse~ <NA> Belgium 1000 5.94
## 4 4 14 2009-01-0~ 8210 1~ Edmont~ AB Canada T6G 2C7 8.91
## 5 5 23 2009-01-1~ 69 Sal~ Boston MA USA 2113 13.9
## 6 6 37 2009-01-1~ Berger~ Frankf~ <NA> Germany 60316 0.99
## 7 7 38 2009-02-0~ Barbar~ Berlin <NA> Germany 10779 1.98
## 8 8 40 2009-02-0~ 8, Rue~ Paris <NA> France 75002 1.98
## 9 9 42 2009-02-0~ 9, Pla~ Bordea~ <NA> France 33000 3.96
## 10 10 46 2009-02-0~ 3 Chat~ Dublin Dublin Ireland <NA> 5.94
## # ... with more rows, and abbreviated variable names 1: InvoiceDate,
## # 2: BillingAddress, 3: BillingCity, 4: BillingState, 5: BillingCountry,
## # 6: BillingPostalCodeOjo [?? x 9]: de momento no conoce el número de filas.
nrow(invoices)## [1] NAPodemos mostrar la consulta SQL correspondiente a una operación:
show_query(head(invoices))## <SQL>
## SELECT *
## FROM `invoices`
## LIMIT 6# str(head(invoices))Al trabajar con bases de datos, dplyr intenta ser lo más vago posible:
No exporta datos a R a menos que se pida explícitamente (
colect()).Retrasa cualquier operación lo máximo posible: agrupa todo lo que se desea hacer y luego hace una única petición a la base de datos.
invoices %>% head %>% collect## # A tibble: 6 x 9
## InvoiceId CustomerId InvoiceDate Billi~1 Billi~2 Billi~3 Billi~4 Billi~5 Total
## <int> <int> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
## 1 1 2 2009-01-01~ Theodo~ Stuttg~ <NA> Germany 70174 1.98
## 2 2 4 2009-01-02~ Ullevå~ Oslo <NA> Norway 0171 3.96
## 3 3 8 2009-01-03~ Grétry~ Brusse~ <NA> Belgium 1000 5.94
## 4 4 14 2009-01-06~ 8210 1~ Edmont~ AB Canada T6G 2C7 8.91
## 5 5 23 2009-01-11~ 69 Sal~ Boston MA USA 2113 13.9
## 6 6 37 2009-01-19~ Berger~ Frankf~ <NA> Germany 60316 0.99
## # ... with abbreviated variable names 1: BillingAddress, 2: BillingCity,
## # 3: BillingState, 4: BillingCountry, 5: BillingPostalCodeinvoices %>% count # número de filas## # Source: lazy query [?? x 1]
## # Database: sqlite 3.36.0 [D:\OneDrive - Universidade da
## # Coruña\__Actual\__IGE\_book_notasr\datos\chinook.db]
## n
## <int>
## 1 412Por ejemplo, para obtener el importe mínimo, máximo y la media de las facturas:
res <- invoices %>% summarise(min = min(Total, na.rm = TRUE),
max = max(Total, na.rm = TRUE), med = mean(Total, na.rm = TRUE))
# show_query(res)
res %>% collect## # A tibble: 1 x 3
## min max med
## <dbl> <dbl> <dbl>
## 1 0.99 25.9 5.65Para obtener el total de las facturas de cada uno de los países:
res <- invoices %>% group_by(BillingCountry) %>%
summarise(n = n(), total = sum(Total, na.rm = TRUE))
# show_query(res)
res %>% collect## # A tibble: 24 x 3
## BillingCountry n total
## <chr> <int> <dbl>
## 1 Argentina 7 37.6
## 2 Australia 7 37.6
## 3 Austria 7 42.6
## 4 Belgium 7 37.6
## 5 Brazil 35 190.
## 6 Canada 56 304.
## 7 Chile 7 46.6
## 8 Czech Republic 14 90.2
## 9 Denmark 7 37.6
## 10 Finland 7 41.6
## # ... with 14 more rowsPara obtener un listado con Nombre y Apellidos de cliente y el importe de cada una de sus facturas (Hint: WHERE customer.CustomerID=invoices.CustomerID):
customers <- tbl(chinook, "customers")
tbl_vars(customers) ## <dplyr:::vars>
## [1] "CustomerId" "FirstName" "LastName" "Company" "Address"
## [6] "City" "State" "Country" "PostalCode" "Phone"
## [11] "Fax" "Email" "SupportRepId"res <- customers %>% inner_join(invoices, by = "CustomerId") %>% select(FirstName, LastName, Country, Total)
show_query(res)## <SQL>
## SELECT `FirstName`, `LastName`, `Country`, `Total`
## FROM (SELECT `LHS`.`CustomerId` AS `CustomerId`, `FirstName`, `LastName`, `Company`, `Address`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `Fax`, `Email`, `SupportRepId`, `InvoiceId`, `InvoiceDate`, `BillingAddress`, `BillingCity`, `BillingState`, `BillingCountry`, `BillingPostalCode`, `Total`
## FROM `customers` AS `LHS`
## INNER JOIN `invoices` AS `RHS`
## ON (`LHS`.`CustomerId` = `RHS`.`CustomerId`)
## )res %>% collect## # A tibble: 412 x 4
## FirstName LastName Country Total
## <chr> <chr> <chr> <dbl>
## 1 Luís Gonçalves Brazil 3.98
## 2 Luís Gonçalves Brazil 3.96
## 3 Luís Gonçalves Brazil 5.94
## 4 Luís Gonçalves Brazil 0.99
## 5 Luís Gonçalves Brazil 1.98
## 6 Luís Gonçalves Brazil 13.9
## 7 Luís Gonçalves Brazil 8.91
## 8 Leonie Köhler Germany 1.98
## 9 Leonie Köhler Germany 13.9
## 10 Leonie Köhler Germany 8.91
## # ... with 402 more rowsPara listar los 10 mejores clientes (aquellos a los que se les ha facturado más cantidad) indicando Nombre, Apellidos, Pais y el importe total de su facturación:
customers %>% inner_join(invoices, by = "CustomerId") %>% group_by(CustomerId) %>%
summarise(FirstName, LastName, country, total = sum(Total, na.rm = TRUE)) %>%
arrange(desc(total)) %>% head(10) %>% collect## # A tibble: 10 x 5
## CustomerId FirstName LastName Country total
## <int> <chr> <chr> <chr> <dbl>
## 1 6 Helena Holý Czech Republic 49.6
## 2 26 Richard Cunningham USA 47.6
## 3 57 Luis Rojas Chile 46.6
## 4 45 Ladislav Kovács Hungary 45.6
## 5 46 Hugh O'Reilly Ireland 45.6
## 6 28 Julia Barnett USA 43.6
## 7 24 Frank Ralston USA 43.6
## 8 37 Fynn Zimmermann Germany 43.6
## 9 7 Astrid Gruber Austria 42.6
## 10 25 Victor Stevens USA 42.6Al finalizar hay que desconectar la base de datos:
DBI::dbDisconnect(chinook)