Dernière mise à jour le 2024-09-26

Notebook seance 11

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

Connecting to 'bd-pedago'

Donner le nombre d’essais pour chaque combinaison de “Name_A” et “Name_B”.

%%sql
SELECT "Name_A", "Name_B", count(*) as nombre_essais
FROM df
GROUP BY "Name_A", "Name_B"

Running query in 'bd-pedago'

20 rows affected.

Name_A Name_B nombre_essais
4 1 480
4 3 479
2 0 479
1 3 480
0 2 479
0 3 480
3 0 480
0 4 480
3 2 480
4 2 480
Truncated to displaylimit of 10.

Donner le meilleur temps de réaction moyen par “Subject”, c’est-à-dire la meilleure moyenne de temps de réaction, chaque moyenne étant calculée pour un “Subject” donné.

%%sql
SELECT min(rt_moy) as plus_petit_rt_moy
FROM (SELECT avg("RT") as rt_moy
      FROM df
      GROUP BY "Subject") moyennes

Running query in 'bd-pedago'

1 rows affected.

plus_petit_rt_moy
8352.7425000000000000 
%%sql
WITH moyennes AS 
(SELECT avg("RT") as rt_moy
 FROM df
 GROUP BY "Subject")

SELECT min(rt_moy) as plus_petit_rt_moy
FROM moyennes

Running query in 'bd-pedago'

1 rows affected.

plus_petit_rt_moy
8352.7425000000000000

Créer une DataFrame avec les données suivantes:

Space Dist DistM
I 1 0.2
I 2 0.3
I 3 0.4
I 4 0.6
I 5 0.8
E 1 2
E 2 3
E 3 4
E 4 6
E 5 8 
list(range(1,6))*2

[1, 2, 3, 4, 5, 1, 2, 3, 4, 5]

ie = ["I"]*5 + ["E"]*5
dists = list(range(1,6))*2
df_m = pandas.DataFrame({"Space": ie, "Dist": dists, "DistM": [0.2, 0.3, 0.4, 0.6, 0.8, 2, 3, 4, 6, 8]})
df_m

Space Dist DistM
0 I 1 0.2
1 I 2 0.3
2 I 3 0.4
3 I 4 0.6
4 I 5 0.8
5 E 1 2.0
6 E 2 3.0
7 E 3 4.0
8 E 4 6.0
9 E 5 8.0 
%sql  --persist df_m

Running query in 'bd-pedago'

Success! Persisted df_m to the database.

%%sql 
select * 
from df_m

Running query in 'bd-pedago'

10 rows affected.

index Space Dist DistM
0 I 1 0.2
1 I 2 0.3
2 I 3 0.4
3 I 4 0.6
4 I 5 0.8
5 E 1 2.0
6 E 2 3.0
7 E 3 4.0
8 E 4 6.0
9 E 5 8.0
Truncated to displaylimit of 10.

Écrire une requête qui ajoute des colonnes “Dist_A_m” et “Dist_B_m” en effectuant deux jointures avec la DataFrame ci-dessus.

%%sql
SELECT df.*, dfa."DistM" AS "Dist_A_m", dfb."DistM" AS "Dist_B_m"
FROM df, df_m AS dfa, df_m AS dfb
WHERE df."Space" = dfa."Space"
  AND df."Space" = dfb."Space"
  AND df."Dist_A" = dfa."Dist"
  AND df."Dist_B" = dfb."Dist"
ORDER BY df.index

Running query in 'bd-pedago'

9594 rows affected.

index Subject Name_A Name_B Dist_A Dist_B Mode Space Side Response RT Dist_A_m Dist_B_m
0 P_ADI_331 0 2 2 4 Dic E D 2 18865 3.0 6.0
1 P_ADI_331 1 4 4 1 Dic E D 2 13157 6.0 2.0
2 P_ADI_331 4 3 3 2 Dic E D 1 11628 4.0 3.0
3 P_ADI_331 2 4 4 1 Dic E D 1 10068 6.0 2.0
4 P_ADI_331 1 2 2 4 Dic E D 1 11801 3.0 6.0
5 P_ADI_331 2 1 2 3 Dic E D 2 12117 3.0 4.0
6 P_ADI_331 2 1 3 4 Dic E D 1 16347 4.0 6.0
7 P_ADI_331 0 3 2 4 Dic E D 1 13237 3.0 6.0
8 P_ADI_331 2 0 4 2 Dic E D 2 12589 6.0 3.0
9 P_ADI_331 2 1 4 2 Dic E D 2 10973 6.0 3.0
Truncated to displaylimit of 10.
Emmanuel Coquery
Emmanuel Coquery
Maître de conférences en Informatique