Gestion de données avec `dplyr` et `(My)SQL`

2016-05-31
Source

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 :

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
)

Name	Value	Indic	Status
Joe	10.1	0	TRUE
Joe	13.0	1	TRUE
Bill	9.0	1	TRUE
Jim	7.7	2	FALSE
Kate	-3.0	2	FALSE

On peut le créer ainsi dans MySQL :

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:

select(dat1, Name, Value)

SELECT `Name`, `Value` FROM dat1;

Name	Value
Joe	10.1
Joe	13.0
Bill	9.0
Jim	7.7
Kate	-3.0

Supprimer des colonnes

R: MySQL:

select(dat1, -Name, -Value)

ALTER TABLE `dat1`
  DROP COLUMN `Name`, DROP COLUMN `Value`;

Indic	Status
0	TRUE
1	TRUE
1	TRUE
2	FALSE
2	FALSE

Plus d’infos pour SQL :

drop columns

Supprimer des lignes

R: MySQL:

dat1 <- dat1[-which(dat1$Name=="Joe"),]

DELETE FROM `dat1` 
  WHERE `Name`='Joe';

	Name	Value	Indic	Status
3	Bill	9.0	1	TRUE
4	Jim	7.7	2	FALSE
5	Kate	-3.0	2	FALSE

Renommer une colonne

R: MySQL:

rename(dat1, Score = Value)

SELECT `Name`, `Value` AS `Score`, `Indic`, `Status` 
  FROM `dat1`;

Name	Score	Indic	Status
Joe	10.1	0	TRUE
Joe	13.0	1	TRUE
Bill	9.0	1	TRUE
Jim	7.7	2	FALSE
Kate	-3.0	2	FALSE

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 :

ALTER TABLE `dat1` 
  CHANGE `Value` `Score` double NOT NULL;

Notez qu’on dû spécifier le type de la colonne (double).

Plus d’infos :

Filtrer

R: MySQL:

filter(dat1, Value > 8)

SELECT * FROM dat1 
  WHERE `Value` > 8;

Name	Value	Indic	Status
Joe	10.1	0	TRUE
Joe	13.0	1	TRUE
Bill	9.0	1	TRUE

R: MySQL:

filter(dat1, Value > 8, Indic==1)

SELECT * FROM dat1 
  WHERE `Value` > 8 AND Indic = 1;

Name	Value	Indic	Status
Joe	13	1	TRUE
Bill	9	1	TRUE

Voici des exemples de conditions sur les chaînes de caractères :

R: MySQL:

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]"))

/* 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:

mutate(dat1, Value=Value*2)

UPDATE `dat1` SET `Value` = `Value`*2;

Name	Value	Indic	Status
Joe	20.2	0	TRUE
Joe	26.0	1	TRUE
Bill	18.0	1	TRUE
Jim	15.4	2	FALSE
Kate	-6.0	2	FALSE

La fonction mutate permet aussi de mettre le résultat dans une nouvelle colonne :

R: MySQL:

mutate(dat1, x=Value*2)

SELECT `Name`, `Value`, `Indic`, `Value` * 2 AS `x`
  FROM `dat1`;

Name	Value	Indic	Status	x
Joe	10.1	0	TRUE	20.2
Joe	13.0	1	TRUE	26.0
Bill	9.0	1	TRUE	18.0
Jim	7.7	2	FALSE	15.4
Kate	-3.0	2	FALSE	-6.0

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 :

ALTER TABLE `dat1` ADD `x` double;
UPDATE `dat1` SET `x` = `Value`*2;

On peut facilement ajouter une colonne constante au tableau :

R: MySQL:

mutate(dat1, x='Hello')

SELECT *, 'Hello' AS x
  FROM `dat1`;

Name	Value	Indic	Status	x
Joe	10.1	0	TRUE	Hello
Joe	13.0	1	TRUE	Hello
Bill	9.0	1	TRUE	Hello
Jim	7.7	2	FALSE	Hello
Kate	-3.0	2	FALSE	Hello

Avec la fonction transmute, le résultat ne contient que les nouvelles colonnes :

R: MySQL:

transmute(dat1, x=Value*2, y=Indic+1)

SELECT `Value` * 2 AS `x`, `Indic` + 1 AS `y`
  FROM `dat1`;

x	y
20.2	1
26.0	2
18.0	2
15.4	3
-6.0	3

Modifier la valeur dans une cellule

R: MySQL:

dat1$Value[which(dat1$Name=="Bill")] <- 8.5

UPDATE `dat1`
  SET `Value` = 8.5 
  WHERE `Name` = 'Bill';

Name	Value	Indic	Status
Joe	10.1	0	TRUE
Joe	13.0	1	TRUE
Bill	8.5	1	TRUE
Jim	7.7	2	FALSE
Kate	-3.0	2	FALSE

Trier selon une colonne

dans l’ordre croissant :

R: MySQL:

arrange(dat1, Value)

SELECT * FROM `dat1`
  ORDER BY `Value`;

Name	Value	Indic	Status
Kate	-3.0	2	FALSE
Jim	7.7	2	FALSE
Bill	9.0	1	TRUE
Joe	10.1	0	TRUE
Joe	13.0	1	TRUE

dans l’ordre décroissant :

R: MySQL:

arrange(dat1, desc(Value))

SELECT * FROM `dat1`
  ORDER BY `Value` DESC;

Name	Value	Indic	Status
Joe	13.0	1	TRUE
Joe	10.1	0	TRUE
Bill	9.0	1	TRUE
Jim	7.7	2	FALSE
Kate	-3.0	2	FALSE

Aggrégation

Moyennes

On obtient aussi la moyenne de Values pour chaque valeur de Name :

R: MySQL:

dat1 %>% group_by(Name) %>% summarise(m=mean(Value))

SELECT `Name`, AVG(`Value`) AS `m` 
  FROM `dat1` 
  GROUP BY `Name`;

Name	m
Bill	9.00
Jim	7.70
Joe	11.55
Kate	-3.00

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:

dat1 %>% group_by(Name) %>% summarise(m=mean(Value)) %>% filter(m>8)

SELECT `Name`, `m`
  FROM (SELECT `Name`, AVG(`Value`) AS `m`
        FROM `dat1`
        GROUP BY `Name`) 
        AS `temp`
  WHERE `m`>8;

Name	m
Bill	9.00
Joe	11.55

Comptages

R: MySQL:

dat1 %>% group_by(Name) %>% summarise(Count=n())

SELECT `Name`, count(*) AS `Count`
  FROM `dat1`
  GROUP BY `Name`;

Name	Count
Bill	1
Jim	1
Joe	2
Kate	1

Jonction de deux tableaux

Dans les exemples qui suivent, nous utilisons ce deuxième tableau de données :

dat2 <- data.frame(
  Name = c("Bill", "Brian"),
  Country = c("USA", "Scotland"), 
  stringsAsFactors = FALSE
)

Name	Country
Bill	USA
Brian	Scotland

Jonction interne

La commande R classique (qui n’utilise pas dplyr) est merge(dat1, dat2, by="Name").

R: MySQL:

inner_join(dat1, dat2, by="Name")

SELECT * FROM 
  `dat1` INNER JOIN `dat2` 
  USING (`Name`);

Name	Value	Indic	Status	Country
Bill	9	1	TRUE	USA

Jonction à gauche

commande R classique : merge(dat1, dat2, by="Name", all.x=TRUE)

R: MySQL:

left_join(dat1, dat2, by="Name")

SELECT * FROM 
  `dat1` LEFT JOIN `dat2` 
  USING (`Name`);

Name	Value	Indic	Status	Country
Joe	10.1	0	TRUE	NA
Joe	13.0	1	TRUE	NA
Bill	9.0	1	TRUE	USA
Jim	7.7	2	FALSE	NA
Kate	-3.0	2	FALSE	NA

Jonction à droite

commande R classique : merge(dat1, dat2, by="Name", all.y=TRUE)

R: MySQL:

right_join(dat1, dat2, by="Name")

SELECT * FROM 
  `dat1` RIGHT JOIN `dat2` 
  USING (`Name`);

Name	Value	Indic	Status	Country
Bill	9	1	TRUE	USA
Brian	NA	NA	NA	Scotland

Jonction complète

commande R classique : merge(dat1, dat2, all=TRUE)

R: MySQL:

full_join(dat1, dat2)

(SELECT * FROM 
  `dat1` LEFT JOIN `dat2`
  USING (`Name`))
UNION 
(SELECT * FROM 
  `dat1` RIGHT JOIN `dat2`
  USING (`Name`)
  WHERE `dat1`.`Name` IS NULL);

Name	Value	Indic	Status	Country
Joe	10.1	0	TRUE	NA
Joe	13.0	1	TRUE	NA
Bill	9.0	1	TRUE	USA
Jim	7.7	2	FALSE	NA
Kate	-3.0	2	FALSE	NA
Brian	NA	NA	NA	Scotland

Autres types de jonction (semi_join, anti_join):

Two-table verbs

Du format large au format long (“unpivot”)

Nous utilisons ici le tableau de données suivant :

longdata <- data.frame(
  Name = c("Joe", "Bill"),
  Age = c(19L, 21L),
  Score1 = c(5, 9),
  Score2 = c(4, 8)
)

Name	Age	Score1	Score2
Joe	19	5	4
Bill	21	9	8

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 :

reshape2::melt(longdata, id=c("Name", "Age"), value.name="Value")

La librairie tidyr est le successeur de la librairie reshape2.

R: MySQL:

library(tidyr)
gather(longdata, Score, Value, -c(Name,Age))

SELECT `Name`, `Age`, 'Score1' AS `Variable`, `Score1` AS `Value`
  FROM `longdata`
UNION ALL
SELECT `Name`, `Age`, 'Score2' AS `Variable`, `Score2` AS `Value`
  FROM `longdata`;

Name	Age	Score	Value
Joe	19	Score1	5
Bill	21	Score1	9
Joe	19	Score2	4
Bill	21	Score2	8

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”)

Nous utilisons ici le tableau créé précedemment à partir du tableau longdata, et nous allons reconstruire longdata à partir de celui-ci.

R: MySQL:

widedata <- gather(longdata, Score, Value, -c(Name,Age))

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 :

reshape2::dcast(widedata, Name + Age ~ Score, value.var="Value")

R: MySQL:

library(tidyr)
spread(widedata, Score, Value)

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`;

Name	Age	Score1	Score2
Bill	21	9	8
Joe	19	5	4

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).

« Back Home

stlaPblog

Some links

▶ R links

▶ Blogs