--- title: "Gestion de données avec `dplyr` et `(My)SQL`" date: "2016-05-31" output: html_document --- ```{r setup, include=FALSE} library(knitr) knitr::opts_chunk$set(echo = TRUE, eval=FALSE) library(dplyr) ``` Cet article fournit des exemples de gestion de données dans R avec la librairie `dplyr`, et les commandes équivalentes avec `MySQL`. ## Commandes simples Les exemples seront appliqués au tableau de données suivant : ```{r, eval=TRUE} dat1 <- data.frame( Name = c("Joe", "Joe", "Bill", "Jim", "Kate"), Value = c(10.1, 13, 9, 7.7, -3), Indic = c(0L, 1L, 1L, 2L, 2L), Status = c(TRUE, TRUE, TRUE, FALSE, FALSE), stringsAsFactors = FALSE ) ``` ```{r, eval=TRUE, echo=FALSE} kable(dat1) ``` On peut le créer ainsi dans `MySQL` : ```sql CREATE TABLE `dat1` ( `Name` varchar(15), `Value` double, `Indic` int(11), `Status` tinyint(1) ); INSERT INTO `dat1` VALUES ('Joe',10.1,0,1),('Joe',13,1,1),('Bill',9,1,1),('Jim',7.7,2,0),('Kate',-3,2,0); ``` ### Sélectionner des colonnes
R:MySQL:
```r select(dat1, Name, Value) ``` ```sql SELECT `Name`, `Value` FROM dat1; ```
```{r, eval=TRUE, echo=FALSE} kable(select(dat1, Name, Value)) ``` ### Supprimer des colonnes
R:MySQL:
```r select(dat1, -Name, -Value) ``` ```sql ALTER TABLE `dat1` DROP COLUMN `Name`, DROP COLUMN `Value`; ```
```{r, eval=TRUE, echo=FALSE} kable(select(dat1, -Name, -Value)) ``` Plus d'infos pour `SQL` : * [drop columns](http://stackoverflow.com/questions/6346120/how-do-i-drop-multiple-columns-with-a-single-alter-table-statement) ### Supprimer des lignes
R:MySQL:
```r dat1 <- dat1[-which(dat1$Name=="Joe"),] ``` ```sql DELETE FROM `dat1` WHERE `Name`='Joe'; ```
```{r, eval=TRUE, echo=FALSE} kable(dat1[-which(dat1$Name=="Joe"),]) ``` ### Renommer une colonne
R:MySQL:
```r rename(dat1, Score = Value) ``` ```sql SELECT `Name`, `Value` AS `Score`, `Indic`, `Status` FROM `dat1`; ```
```{r, eval=TRUE, echo=FALSE} kable(rename(dat1, Score = Value)) ``` Dans `MySQL`, on peut aussi utiliser la méthode suivante qui est plus directe, avec elle on n'a pas besoin de taper les noms des autres colonnes : ```sql ALTER TABLE `dat1` CHANGE `Value` `Score` double NOT NULL; ``` Notez qu'on dû spécifier le type de la colonne (`double`). Plus d'infos : * [How do I rename a column in a database table using SQL?](http://stackoverflow.com/questions/174582/how-do-i-rename-a-column-in-a-database-table-using-sql) * [ALTER TABLE - Rename a column](http://dba.stackexchange.com/questions/62051/alter-table-rename-a-column) ### Filtrer
R:MySQL:
```r filter(dat1, Value > 8) ``` ```sql SELECT * FROM dat1 WHERE `Value` > 8; ```
```{r, eval=TRUE, echo=FALSE} kable(filter(dat1, Value > 8)) ```
R:MySQL:
```r filter(dat1, Value > 8, Indic==1) ``` ```sql SELECT * FROM dat1 WHERE `Value` > 8 AND Indic = 1; ```
```{r, eval=TRUE, echo=FALSE} kable(filter(dat1, Value>8, Indic==1)) ``` Voici des exemples de conditions sur les chaînes de caractères :
R:MySQL:
```{r, eval=FALSE} library(stringr) # noms contenant "J" filter(dat1, str_detect(Name, "J")) # noms commençant par "J" filter(dat1, str_detect(Name, "^J")) # noms terminant par "e" filter(dat1, str_detect(Name, "e$")) # noms contenant "o" ou "K" filter(dat1, str_detect(Name, "[oK]")) ``` ```sql /* noms contenant "J" */ SELECT * FROM `dat1` WHERE `Name` LIKE '%J%'; /* noms commençant par "J" */ SELECT * FROM `dat1` WHERE `Name` LIKE 'J%'; /* noms terminant par "e" */ SELECT * FROM `dat1` WHERE `Name` LIKE '%e'; /* noms contenant "o" ou "K" */ SELECT * FROM `dat1` WHERE `Name` LIKE '%o%' OR Name LIKE '%K%'; ```
### Transformer une colonne
R:MySQL:
```r mutate(dat1, Value=Value*2) ``` ```sql UPDATE `dat1` SET `Value` = `Value`*2; ```
```{r, eval=TRUE, echo=FALSE} kable(mutate(dat1, Value=Value*2)) ``` La fonction `mutate` permet aussi de mettre le résultat dans une nouvelle colonne :
R:MySQL:
```r mutate(dat1, x=Value*2) ``` ```sql SELECT `Name`, `Value`, `Indic`, `Value` * 2 AS `x` FROM `dat1`; ```
```{r, eval=TRUE, echo=FALSE} kable(mutate(dat1, x=Value*2)) ``` Pour éviter de sélectionner toutes les colonnes dans `SQL`, on peut d'abord créer une nouvelle colonne puis utiliser `UPDATE` comme précédemment : ```sql ALTER TABLE `dat1` ADD `x` double; UPDATE `dat1` SET `x` = `Value`*2; ``` On peut facilement ajouter une colonne constante au tableau :
R:MySQL:
```r mutate(dat1, x='Hello') ``` ```sql SELECT *, 'Hello' AS x FROM `dat1`; ```
```{r, eval=TRUE, echo=FALSE} kable(mutate(dat1, x='Hello')) ``` Avec la fonction `transmute`, le résultat ne contient que les nouvelles colonnes :
R:MySQL:
```r transmute(dat1, x=Value*2, y=Indic+1) ``` ```sql SELECT `Value` * 2 AS `x`, `Indic` + 1 AS `y` FROM `dat1`; ```
```{r, eval=TRUE, echo=FALSE} kable(transmute(dat1, x=Value*2, y=Indic+1)) ``` ### Modifier la valeur dans une cellule
R:MySQL:
```r dat1$Value[which(dat1$Name=="Bill")] <- 8.5 ``` ```sql UPDATE `dat1` SET `Value` = 8.5 WHERE `Name` = 'Bill'; ```
```{r, eval=TRUE, echo=FALSE} local({ dat1$Value[which(dat1$Name=="Bill")] <- 8.5 kable(dat1)}) ``` ### Trier selon une colonne * dans l'ordre croissant :
R:MySQL:
```r arrange(dat1, Value) ``` ```sql SELECT * FROM `dat1` ORDER BY `Value`; ```
```{r, eval=TRUE, echo=FALSE} kable(arrange(dat1, Value)) ``` * dans l'ordre décroissant :
R:MySQL:
```r arrange(dat1, desc(Value)) ``` ```sql SELECT * FROM `dat1` ORDER BY `Value` DESC; ```
```{r, eval=TRUE, echo=FALSE} kable(arrange(dat1, desc(Value))) ``` ## Aggrégation ### Moyennes On obtient aussi la moyenne de `Values` pour chaque valeur de `Name` :
R:MySQL:
```r dat1 %>% group_by(Name) %>% summarise(m=mean(Value)) ``` ```sql SELECT `Name`, AVG(`Value`) AS `m` FROM `dat1` GROUP BY `Name`; ```
```{r, eval=TRUE, echo=FALSE} kable(dat1 %>% group_by(Name) %>% summarise(m=mean(Value))) ``` Si on veut obtenir les moyennes puis appliquer une autre requête, il suffit d'enchaîner les commandes avec `dplyr` ; par contre, avec `SQL`, il faut encapsuler le premier tableau avant d'appliquer la seconde requête :
R:MySQL:
```r dat1 %>% group_by(Name) %>% summarise(m=mean(Value)) %>% filter(m>8) ``` ```sql SELECT `Name`, `m` FROM (SELECT `Name`, AVG(`Value`) AS `m` FROM `dat1` GROUP BY `Name`) AS `temp` WHERE `m`>8; ```
```{r, eval=TRUE, echo=FALSE} kable(dat1 %>% group_by(Name) %>% summarise(m=mean(Value)) %>% filter(m>8)) ``` ### Comptages
R:MySQL:
```r dat1 %>% group_by(Name) %>% summarise(Count=n()) ``` ```sql SELECT `Name`, count(*) AS `Count` FROM `dat1` GROUP BY `Name`; ```
```{r, eval=TRUE, echo=FALSE} kable(dat1 %>% group_by(Name) %>% summarise(Count=n())) ``` ## Jonction de deux tableaux Dans les exemples qui suivent, nous utilisons ce deuxième tableau de données : ```{r, eval=TRUE} dat2 <- data.frame( Name = c("Bill", "Brian"), Country = c("USA", "Scotland"), stringsAsFactors = FALSE ) ``` ```{r, eval=TRUE, echo=FALSE} kable(dat2) ``` ### Jonction interne La commande R classique (qui n'utilise pas `dplyr`) est `merge(dat1, dat2, by="Name")`.
R:MySQL:
```r inner_join(dat1, dat2, by="Name") ``` ```sql SELECT * FROM `dat1` INNER JOIN `dat2` USING (`Name`); ```
```{r, eval=TRUE, echo=FALSE} kable(inner_join(dat1, dat2, by="Name")) ``` ### Jonction à gauche commande R classique : `merge(dat1, dat2, by="Name", all.x=TRUE)`
R:MySQL:
```r left_join(dat1, dat2, by="Name") ``` ```sql SELECT * FROM `dat1` LEFT JOIN `dat2` USING (`Name`); ```
```{r, eval=TRUE, echo=FALSE} kable(left_join(dat1, dat2, by="Name")) ``` ### Jonction à droite commande R classique : `merge(dat1, dat2, by="Name", all.y=TRUE)`
R:MySQL:
```r right_join(dat1, dat2, by="Name") ``` ```sql SELECT * FROM `dat1` RIGHT JOIN `dat2` USING (`Name`); ```
```{r, eval=TRUE, echo=FALSE} kable(right_join(dat1, dat2, by="Name") ) ``` ### Jonction complète commande R classique : `merge(dat1, dat2, all=TRUE)`
R:MySQL:
```r full_join(dat1, dat2) ``` ```sql (SELECT * FROM `dat1` LEFT JOIN `dat2` USING (`Name`)) UNION (SELECT * FROM `dat1` RIGHT JOIN `dat2` USING (`Name`) WHERE `dat1`.`Name` IS NULL); ```
```{r, eval=TRUE, echo=FALSE} kable(full_join(dat1, dat2, by="Name")) ``` Autres types de jonction (`semi_join`, `anti_join`): * [Two-table verbs](https://cran.r-project.org/web/packages/dplyr/vignettes/two-table.html) ## Du format large au format long ("unpivot") Nous utilisons ici le tableau de données suivant : ```{r, eval=TRUE} longdata <- data.frame( Name = c("Joe", "Bill"), Age = c(19L, 21L), Score1 = c(5, 9), Score2 = c(4, 8) ) ``` ```{r, eval=TRUE, echo=FALSE} kable(longdata) ``` Pour transformer ce tableau en un tableau au format long, avec `Name` et `Age` comme variables identifiantes, on peut utiliser la fonction `melt` de la librairie `reshape2` : ```r reshape2::melt(longdata, id=c("Name", "Age"), value.name="Value") ``` La librairie `tidyr` est le successeur de la librairie `reshape2`.
R:MySQL:
```r library(tidyr) gather(longdata, Score, Value, -c(Name,Age)) ``` ```sql SELECT `Name`, `Age`, 'Score1' AS `Variable`, `Score1` AS `Value` FROM `longdata` UNION ALL SELECT `Name`, `Age`, 'Score2' AS `Variable`, `Score2` AS `Value` FROM `longdata`; ```
```{r, eval=TRUE, echo=FALSE} library(tidyr) kable(gather(longdata, Score, Value, -c(Name,Age))) ``` Avec `MySQL`, nous avons simplement créé un tableau pour chaque valeur de la variable `Score` puis nous avons empilé les deux tableaux avec `UNION ALL`. ``` mysql> SELECT `Name`, `Age`, 'Score1' AS `Score`, `Score1` AS `Value` -> FROM `longdata`; +------+------+--------+-------+ | Name | Age | Score | Value | +------+------+--------+-------+ | Joe | 19 | Score1 | 5 | | Bill | 21 | Score1 | 9 | +------+------+--------+-------+ 2 rows in set (0.00 sec) ``` ## Du format large au format long ("pivot") ```{r, eval=TRUE, include=FALSE} widedata <- gather(longdata, Score, Value, -c(Name,Age)) ``` Nous utilisons ici le tableau créé précedemment à partir du tableau `longdata`, et nous allons reconstruire `longdata` à partir de celui-ci.
R:MySQL:
```r widedata <- gather(longdata, Score, Value, -c(Name,Age)) ``` ```sql CREATE TABLE `widedata` AS SELECT `Name`, `Age`, 'Score1' AS `Score`, `Score1` AS `Value` FROM `longdata` UNION ALL SELECT `Name`, `Age`, 'Score2' AS `Score`, `Score2` AS `Value` FROM `longdata`; ```
Dans R, on peut utiliser la commande `dcast` de la librairie `reshape2` : ```r reshape2::dcast(widedata, Name + Age ~ Score, value.var="Value") ```
R:MySQL:
```r library(tidyr) spread(widedata, Score, Value) ``` ```sql SELECT Name, Age, MAX( IF( Score='Score1', `Value`, NULL ) ) AS `Score1`, MAX( IF( Score='Score2', `Value`, NULL ) ) AS `Score2` FROM widedata GROUP BY `Name`, `Age`; ```
```{r, eval=TRUE, echo=FALSE} kable(spread(widedata, Score, Value)) ``` Détaillons la procédure `MySQL`. Si on retire l'opérateur `MAX` et la commande `GROUP BY`, on obtient : ``` mysql> SELECT Name, Age, -> IF( Score='Score1', `Value`, NULL ) AS `Score1`, -> IF( Score='Score2', `Value`, NULL ) AS `Score2` -> FROM widedata; +------+------+--------+--------+ | Name | Age | Score1 | Score2 | +------+------+--------+--------+ | Joe | 19 | 5 | NULL | | Bill | 21 | 9 | NULL | | Joe | 19 | NULL | 4 | | Bill | 21 | NULL | 8 | +------+------+--------+--------+ 4 rows in set (0.00 sec) ``` (on aurait le même résultat si on utilisait ``````CASE WHEN `Score`='Score1' THEN `Value` END`````` à la place de `IF(...)`.) L'idée consiste alors à manipuler le tableau en regroupant par `Name` et `Age`, et à utiliser `MAX` pour garder la valeur numérique (par exemple, `MAX` appliqué à `5` et `NULL` retourne `5`).