In [1]:
import pandas as pd
import numpy as np
In [2]:
mano = pd.read_csv('./navires-2022-a-la-mano-privateleaderboard.csv')
libre = pd.read_csv('./navire-2022-libre-privateleaderboard.csv')
groupes = pd.read_csv('./Auto-sélectiondegroupe_IREN_2022-07-31.csv', skiprows=[0])
In [3]:
groupes = groupes[['Group Name', 'Member 1 Username', 'Member 2 Username']]
groupes['Group Name'] = groupes['Group Name'].str.lower()
groupes
Out[3]:
Group Name Member 1 Username Member 2 Username
0 chevalvert paviel.schertzer paul.viallet
1 epibrakos morgan.vaterkowski benjamin.decreusefond
2 petite chocolatine marc.demoustier jake.penney
3 bzh marine.charra adrien.duot
4 polystiren sydney.tap amine.mrad
... ... ... ...
156 matthieu schlienger matthieu.schlienger NaN
157 miyaou melanie.tcheou NaN
158 antoine vergnaud and félix wirth felix.wirth antoine.vergnaud
159 alexandre castello & alain salanie alain.salanie alexandre.castello
160 only 77% :'( romain1.brand NaN

161 rows × 3 columns

In [4]:
mano
Out[4]:
TeamId TeamName SubmissionDate Score
0 8598499 mer-veille 2022-07-10 13:36:36 0.86788
1 8570045 Chipeur 2022-07-09 14:40:18 0.86382
2 8584448 TheRealZodinX 2022-07-10 20:01:46 0.84247
3 8869608 Erwan Goudard -Adrien Merat 2022-07-10 12:16:32 0.84146
4 8570005 Hugo 1v9 2022-07-10 16:16:38 0.83739
... ... ... ... ...
88 8858003 Sewy 2022-07-07 17:58:50 0.58231
89 8877196 alexandre castello & alain salanie 2022-07-10 19:33:37 0.37703
90 8569846 Cédric TARBES 2022-05-06 17:11:36 0.28760
91 8584368 ambrosino 2022-05-06 09:06:23 0.13211
92 8873390 RaphT1 2022-07-10 21:30:34 0.13211

93 rows × 4 columns

In [5]:
libre
Out[5]:
TeamId TeamName SubmissionDate Score
0 8873900 Mer-veille 2022-07-10 21:48:00 0.86686
1 8584837 Lombard & Zimmermann 2022-07-07 20:03:40 0.86178
2 8641900 pop 2022-07-10 23:54:58 0.85162
3 8857214 Les rejetés d'IMAGE 2022-07-10 09:52:16 0.84756
4 8591084 Hugo 1v9 2022-07-10 23:17:16 0.84552
... ... ... ... ...
83 8572884 a flot 2022-07-10 21:46:25 0.46646
84 8873211 AAAH-fatigue 2022-07-10 21:55:51 0.46544
85 8876699 Flow 2022-07-10 21:59:28 0.44004
86 8584429 Antoine Vergnaud and Felix Wirth 2022-07-10 22:47:40 0.42886
87 8861635 Boat Griffin 2022-07-10 23:13:36 0.15955

88 rows × 4 columns

In [6]:
mano.TeamName = mano.TeamName.str.lower()
libre.TeamName = libre.TeamName.str.lower()
In [7]:
deux = pd.merge(mano[['TeamName', 'Score']], libre[['TeamName', 'Score']], on="TeamName", how='outer', indicator='merge')
deux['Score_x'] = deux['Score_x'].fillna(0)
deux['Score_y'] = deux['Score_y'].fillna(0)
In [8]:
tout = pd.merge(groupes, deux, left_on="Group Name", right_on="TeamName", indicator = 'merge2')
In [9]:
tout['Note'] = round(200 * ( np.square(tout['Score_x']) / 7.5 + np.square(tout['Score_y']) / 7.5)) / 2
In [10]:
tout['Note'].max(), tout['Note'].mean()
Out[10]:
(20.0, 14.258620689655173)
In [11]:
tout
Out[11]:
Group Name Member 1 Username Member 2 Username TeamName Score_x Score_y merge merge2 Note
0 zodinx jules.dorbeau noe.jenn-treyer zodinx 0.81504 0.81097 both both 17.5
1 so6 pierre.de-la-ruffie mathieu.guerin so6 0.63414 0.69613 both both 12.0
2 rush b philippe.bernet baptiste.bourdet rush b 0.81300 0.79065 both both 17.0
3 navigateur2k22 mathieu.rivier moustapha.diop navigateur2k22 0.81808 0.83333 both both 18.0
4 dropshiping theo.perinet marc.monteil dropshiping 0.76626 0.71341 both both 14.5
5 power steven.tien jacky.wu power 0.72459 0.70223 both both 13.5
6 bearth vision timothee.ribes axel.ribon bearth vision 0.81402 0.77235 both both 17.0
7 gangplank nathan.habib vincent.courty gangplank 0.78658 0.78455 both both 16.5
8 oro jackson arthur.fan hao.ye oro jackson 0.78252 0.77947 both both 16.5
9 chipeur lucas.pinot william.guillet chipeur 0.86382 0.82113 both both 19.0
10 ships-me-harder gautier.picard declan.chlasta ships-me-harder 0.73780 0.68800 both both 13.5
11 les rejetés d'image antoine.zellmeyer nelson.vicel-farah les rejetés d'image 0.79776 0.84756 both both 18.0
12 lavs alexandre.lemonnier victor.simonin lavs 0.81504 0.00000 left_only both 9.0
13 kraken massil.ferhani NaN kraken 0.82012 0.81097 both both 17.5
14 sussy boats axel.barbier arthur.le-bourg sussy boats 0.82418 0.83028 both both 18.0
15 lukat de boat oscar.bourgue thomas.bouygues lukat de boat 0.80691 0.00000 left_only both 8.5
16 irensponsables elodine.coquelet aristide.cuny irensponsables 0.80182 0.83943 both both 18.0
17 battle ducks nicolas.lorrain nicolas.indjein battle ducks 0.77947 0.81504 both both 17.0
18 ia sup a jb_hugo jean-baptiste.deloges hugo.canton-bacara ia sup a jb_hugo 0.00000 0.77845 right_only both 8.0
19 nicolas romano1 nicolas.romano NaN nicolas romano1 0.79268 0.72662 both both 15.5
20 baguette magique classifier henri.jamet corentin.duchene baguette magique classifier 0.79166 0.83434 both both 17.5
21 mirabelle abdulmassih mirabelle.abdulmassih NaN mirabelle abdulmassih 0.75609 0.74593 both both 15.0
22 paperwork haters stephane.mabille michail.chatzizacharias paperwork haters 0.81808 0.75508 both both 16.5
23 objectif battre tom corentin.pion eliot.leclair objectif battre tom 0.78760 0.75813 both both 16.0
24 a flot thibaut.benefice NaN a flot 0.63109 0.46646 both both 8.0
25 snooty dogs nathan.cabasso ferdinand.mom snooty dogs 0.78252 0.79369 both both 16.5
26 hugo & erwan hugo.bois NaN hugo & erwan 0.72865 0.74288 both both 14.5
27 :rocket_crash: william.grolleau jeremy.croiset :rocket_crash: 0.80894 0.00000 left_only both 8.5
28 les hippocampes enguerrand.de-gentile-duquesne antoine.aubin les hippocampes 0.78963 0.61788 both both 13.5
29 maman les ptits bateaux paul.galand temano.frogier maman les ptits bateaux 0.73069 0.72357 both both 14.0
30 mer-veille tanguy.desgouttes marius.dubosc mer-veille 0.86788 0.86686 both both 20.0
31 🌱🌿🎋 elisey.balakhnichev elvin.foulon 🌱🌿🎋 0.72459 0.68292 both both 13.0
32 iren d'angleterre samuel.compagnon philippe.aymard iren d'angleterre 0.82113 0.82215 both both 18.0
33 gaming house pierre.seguin alexandre.poignant gaming house 0.81300 0.82012 both both 18.0
34 kled l'impétueux yorick.madelain NaN kled l'impétueux 0.69004 0.00000 left_only both 6.5
35 sewy phu-hien.le yassin.bouhassoun sewy 0.58231 0.70833 both both 11.0
36 test691 nicolas.trabet NaN test691 0.81707 0.81402 both both 17.5
37 angèle & dua lipa cloe.escudier paul.grolier angèle & dua lipa 0.80386 0.82418 both both 17.5
38 neurone hugo.boux clement.bieber neurone 0.00000 0.60060 right_only both 5.0
39 noot noot tao.blancheton sarah.gutierez noot noot 0.79268 0.76524 both both 16.0
40 kiloren clement.languerre mohamed-jordan.soumano kiloren 0.79674 0.55386 both both 12.5
41 sombrebunny paul.messeant alexis.julien sombrebunny 0.77134 0.84451 both both 17.5
42 flow caroline.devaux mehdi.oueslati flow 0.75609 0.44004 both both 10.0
43 aaah-fatigue marius.hurbin emmanuel.mollard aaah-fatigue 0.70630 0.46544 both both 9.5
44 rsa cnn liliam.jean-baptiste antoine.delattre rsa cnn 0.83434 0.80182 both both 18.0
45 hugo 1v9 hugo.levy NaN hugo 1v9 0.83739 0.84552 both both 19.0
46 therealzodinx ihor.husak benoist.andre therealzodinx 0.84247 0.83130 both both 18.5
47 vg romain.gregoire aurelien.visentin vg 0.76422 0.77134 both both 15.5
48 yellow submarine paul.renoux nikoloz.chaduneli yellow submarine 0.76829 0.00000 left_only both 8.0
49 les 34 petits chevaux adrien.anton-ludwig adele.pluquet les 34 petits chevaux 0.61890 0.64939 both both 10.5
50 team rocket theau.degroote julien.cros team rocket 0.74796 0.00000 left_only both 7.5
51 ship society charli.de-luca yacine.anane ship society 0.78760 0.77947 both both 16.5
52 radeau premium pejman.samieyan jules.coquel-doucet radeau premium 0.76422 0.75304 both both 15.5
53 bloop bloop adrien.barens maxime.brouillard bloop bloop 0.77134 0.76930 both both 16.0
54 jackobinks jacques.ren luca.moorghen jackobinks 0.81910 0.78150 both both 17.0
55 matthieu schlienger matthieu.schlienger NaN matthieu schlienger 0.75203 0.00000 left_only both 7.5
56 miyaou melanie.tcheou NaN miyaou 0.76016 0.75304 both both 15.5
57 alexandre castello & alain salanie alain.salanie alexandre.castello alexandre castello & alain salanie 0.37703 0.51016 both both 5.5
In [12]:
notes = pd.concat([tout[['Member 1 Username','Note']].rename(columns={'Member 1 Username':'Eleve'}), 
                   tout[['Member 2 Username', 'Note']].rename(columns={'Member 2 Username':'Eleve'})])
notes = notes.dropna()
notes.sort_values(by='Eleve', inplace=True)
notes
Out[12]:
Eleve Note
49 adele.pluquet 10.5
49 adrien.anton-ludwig 10.5
53 adrien.barens 16.0
57 alain.salanie 5.5
57 alexandre.castello 5.5
... ... ...
27 william.grolleau 8.5
9 william.guillet 19.0
51 yacine.anane 16.5
35 yassin.bouhassoun 11.0
34 yorick.madelain 6.5

106 rows × 2 columns

In [13]:
notes.to_csv('notes_iren2022.csv')
In [ ]: