Notebook séance 10

Reprise des exercices Pandas en SQL On commence par se connecter à la base et à importer pandas.

%load_ext sql
%config SqlMagic.dsn_filename = "connections.ini"
%sql --section bd-pedago
%sql --connections
import pandas

Connecting to 'bd-pedago'

La table de la séance9 existe toujours, pas besoin de la recréer

Projections et filtres simples

Donner les valeurs de la colonne “RT”

%%sql
SELECT "RT"
FROM df

Running query in 'bd-pedago'

9594 rows affected.

RT
18865
13157
11628
10068
11801
12117
16347
13237
12589
10973
Truncated to displaylimit of 10.

Donner les différentes valeurs possible pour la colonne “Subject”

On utilise pour cela le mot clé DISTINCT, placé juste après le SELECT:

%%sql
SELECT DISTINCT "Subject"
FROM df

Running query in 'bd-pedago'

24 rows affected.

Subject
P_SOA_337
P_GRF_322
P_LAC_354
P_BEH_340
P_GRC_341
P_LEG_335
P_MOE_339
P_VAL_329
P_CAV_333
P_BOC_342
Truncated to displaylimit of 10.

Le distinct peut fonctionner sur plusieurs colonnes, par exemple si la table data contient:

A B C
a b e
a c f
d b g
a b h

La requête

SELECT DISTINCT A, B
FROM data

produira

A B
a b
a c
d b

Filtrer la dataframe pour ne garder que les lignes dont le “RT” est plus petit que 10 000.

%%sql
SELECT *
FROM df
WHERE "RT" < 10000

Running query in 'bd-pedago'

4528 rows affected.

index Subject Name_A Name_B Dist_A Dist_B Mode Space Side Response RT
20 P_ADI_331 1 2 3 5 Dic E D 1 8564
24 P_ADI_331 1 4 1 5 Dic E D 1 7805
27 P_ADI_331 2 0 3 1 Dic E D 2 9533
29 P_ADI_331 1 4 3 2 Dic E D 2 9887
33 P_ADI_331 0 4 5 2 Dic E D 2 8152
35 P_ADI_331 3 1 1 4 Dic E D 1 8414
36 P_ADI_331 3 1 2 4 Dic E D 1 9523
40 P_ADI_331 0 4 2 5 Dic E D 1 8459
59 P_ADI_331 1 2 1 4 Dio E G 1 9281
60 P_ADI_331 4 2 1 5 Dio E G 1 7671
Truncated to displaylimit of 10.
%sqlcmd explore --table df
df_subjects = %sql SELECT DISTINCT "Subject" FROM df

Running query in 'bd-pedago'

24 rows affected.

df_subjects.DataFrame().to_excel("subjects.xlsx")

On change le nombre de ligne affichées à 50

%config SqlMagic.displaylimit = 50
%%sql
SELECT DISTINCT "Subject"
FROM df

Running query in 'bd-pedago'

24 rows affected.

Subject
P_SOA_337
P_GRF_322
P_LAC_354
P_BEH_340
P_GRC_341
P_LEG_335
P_MOE_339
P_VAL_329
P_CAV_333
P_BOC_342
P_ALM_345
P_ADI_331
P_GAM_338
P_CON_336
P_VAR_330
P_GHM_334
P_BOA_321
P_BLR_321
P_TAI_343
P_CAR_327
P_AMY_346
P_BAM_347
P_ROS_336
P_BLC_325
# On remet le nombre de lignes affichées à 10
%config SqlMagic.displaylimit = 10

Combiner les projections et les filtres

Indiquer la valeur de “RT” pour les essais en mode “Dic”.

%%sql
SELECT "RT"
FROM df
WHERE "Mode" = 'Dic'

Running query in 'bd-pedago'

4795 rows affected.

RT
18865
13157
11628
10068
11801
12117
16347
13237
12589
10973
Truncated to displaylimit of 10.

Créer une Series indiquant pour chaque essai si la réponse est juste ou non. Une réponse est juste si la distance A est inférieure à la distance B et la réponse est 1 ou si la distance A est supérieure à la distance B et la réponse est 2.

%%sql
SELECT index,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

9594 rows affected.

index juste
0 False
1 True
2 False
3 False
4 True
5 False
6 True
7 True
8 True
9 True
Truncated to displaylimit of 10.

Pour avoir la colonne “juste” intégrée avec les autres, on peut faire une requête qui récupère tout df plus cette colonne:

%%sql
SELECT *,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

9594 rows affected.

index Subject Name_A Name_B Dist_A Dist_B Mode Space Side Response RT juste
0 P_ADI_331 0 2 2 4 Dic E D 2 18865 False
1 P_ADI_331 1 4 4 1 Dic E D 2 13157 True
2 P_ADI_331 4 3 3 2 Dic E D 1 11628 False
3 P_ADI_331 2 4 4 1 Dic E D 1 10068 False
4 P_ADI_331 1 2 2 4 Dic E D 1 11801 True
5 P_ADI_331 2 1 2 3 Dic E D 2 12117 False
6 P_ADI_331 2 1 3 4 Dic E D 1 16347 True
7 P_ADI_331 0 3 2 4 Dic E D 1 13237 True
8 P_ADI_331 2 0 4 2 Dic E D 2 12589 True
9 P_ADI_331 2 1 4 2 Dic E D 2 10973 True
Truncated to displaylimit of 10.

On peut en faire une vue en ajoutant devant CREATE OR REPLACE VIEW un_nom AS, ce qui permettra d’utiliser le résultat comme s’il avait été mis dans une table un_nom. La différence est qu’une vue est toujours recalculée et a donc toujours des données correctes.

%%sql
DROP VIEW IF EXISTS dfj;
CREATE OR REPLACE VIEW dfj AS
SELECT *,
       (("Dist_A" < "Dist_B" AND "Response" = 1)
        OR ("Dist_A" > "Dist_B" AND "Response" = 2)) 
       AS juste
FROM df

Running query in 'bd-pedago'

%%sql
SELECT *
FROM dfj

Running query in 'bd-pedago'

9594 rows affected.

index Subject Name_A Name_B Dist_A Dist_B Mode Space Side Response RT juste
0 P_ADI_331 0 2 2 4 Dic E D 2 18865 False
1 P_ADI_331 1 4 4 1 Dic E D 2 13157 True
2 P_ADI_331 4 3 3 2 Dic E D 1 11628 False
3 P_ADI_331 2 4 4 1 Dic E D 1 10068 False
4 P_ADI_331 1 2 2 4 Dic E D 1 11801 True
5 P_ADI_331 2 1 2 3 Dic E D 2 12117 False
6 P_ADI_331 2 1 3 4 Dic E D 1 16347 True
7 P_ADI_331 0 3 2 4 Dic E D 1 13237 True
8 P_ADI_331 2 0 4 2 Dic E D 2 12589 True
9 P_ADI_331 2 1 4 2 Dic E D 2 10973 True
Truncated to displaylimit of 10.

Indiquer les “Subject” qui ont répondu en moins de 4 000 (“RT” inférieur à 4 000)

%%sql
SELECT DISTINCT "Subject"
FROM df
WHERE "RT" < 4000

Running query in 'bd-pedago'

13 rows affected.

Subject
P_GRC_341
P_LEG_335
P_MOE_339
P_CAV_333
P_BOC_342
P_ALM_345
P_CON_336
P_VAR_330
P_GHM_334
P_BLR_321
Truncated to displaylimit of 10.

Agrégations globales

Donner la moyenne de “RT”.

%%sql
SELECT avg("RT") AS moyenne
FROM df

Running query in 'bd-pedago'

1 rows affected.

moyenne
12050.088597039817

Donner le plus grand “RT” pour le “Subject” “P_ROS_336”

%%sql
SELECT max("RT") AS moyenne
FROM df
WHERE "Subject" = 'P_ROS_336'

Running query in 'bd-pedago'

1 rows affected.

moyenne
54405

Donner l’écart-type de “RT” pour les essais réussis (utiliser .std())

%%sql
SELECT stddev("RT") AS ecart_type
FROM dfj
WHERE juste

Running query in 'bd-pedago'

1 rows affected.

ecart_type
6505.738105682207

Groupes simples

Donner la moyenne du temps de réaction (“RT”) pour chaque “Subject”.

%%sql
SELECT "Subject", avg("RT") as Moyenne_RT
FROM df
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.

Subject moyenne_rt
P_SOA_337 10051.175000000000
P_GRF_322 15652.556390977444
P_LAC_354 15617.710000000000
P_BEH_340 13187.195000000000
P_GRC_341 11213.115000000000
P_LEG_335 10904.325000000000
P_MOE_339 8470.4475000000000000
P_VAL_329 13661.730000000000
P_CAV_333 10551.257500000000
P_BOC_342 12779.680000000000
Truncated to displaylimit of 10.

Donner le nombre d’essais pour chaque “Subject” en utilisant COUNT(*).

%%sql
SELECT "Subject", COUNT(*) as nombre_essais
FROM df
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.

Subject nombre_essais
P_SOA_337 400
P_GRF_322 399
P_LAC_354 400
P_BEH_340 400
P_GRC_341 400
P_LEG_335 400
P_MOE_339 400
P_VAL_329 400
P_CAV_333 400
P_BOC_342 400
Truncated to displaylimit of 10.

Donner le nombre d’essais réussis pour chaque “Subject” en utilisant un filtre puis count(*).

%%sql
SELECT "Subject", COUNT(*) as essais_reussis
FROM dfj
WHERE juste
GROUP BY "Subject"

Running query in 'bd-pedago'

24 rows affected.

Subject essais_reussis
P_SOA_337 305
P_GRF_322 291
P_LAC_354 319
P_BEH_340 337
P_GRC_341 322
P_LEG_335 323
P_MOE_339 350
P_VAL_329 280
P_CAV_333 345
P_BOC_342 299
Truncated to displaylimit of 10.
Maître de conférences en Informatique