Journal csvspoon et csvformatmail: l'industrialisation de la manipulation de fichiers csv.

Sommaire

• csvspoon: manipulation de csv
  • Installation
  • Concaténation de fichiers
  • Application de formules
  • Filtrer les lignes
  • Faire un tri
  • Jointures
  • Agrégation
  • Conclusion
• csvformatmail: envoyer des mails à la chaine avec formatage
  • Installation
  • Utilisation basique
  • Utilisation plus avancée
• Conclusion

Bien que les journaux sur le cyclisme me manquent, tout se perd ma bonne dame, y compris le cyclisme sur dlfp, dans ce journal, csv ne désigne pas cycliste sur vélo mais comma separated values, un format primitif de stockage de données tabulées.

Dans mes activités je me retrouve régulièrement à gérer des données de notes d'étudiants dans des modules d'enseignements à gros effectifs, ainsi qu'à envoyer des mails au dits étudiants. La manipulation se fait très souvent au moyen de fichiers csv (que cela soit les listes d'étudiants envoyés par l'administration de l'université ou que cela soit le moodle ou les fichiers de notes provenant d'une pipeline utilisant AMC. Bref, les csv, c'est le nerf de la guerre dans la gestion quotidienne d'un enseignement.

N'ayant pas trouvé ce que je cherchais dans les différents outils, j'en suis arrivé progressivement à écrire mes propres outils, et c'est ce que je vais vous présenter dans ce journal.

csvspoon: manipulation de csv

Tout d'abord, j'ai besoin de réaliser plein de choses sur les csv, les opérations les plus classiques sont la sélection de colonnes, le filtrage, les jointure, les tris, les agrégations et l'application de formules. En fait ce que j'ai besoin c'est de la manipulation de csv comme de table d'une base de donnée relationnelle. Et ayant joué avec quelques outils, aucun ne m'a satisfait, j'ai donc écrit le mien, et cela fait deux ans que je l'utilise et j'en suis très content.

Il a fallu le nommer, un outils pour csv… voyons voir, en étant original. C'est quoi une bonne arme pour traiter des csv ? Une cuillère bien sûr. Et c'est ainsi que j'ai décidé de le nommer csvspoon.

Le dépôt de dev est sur github, sous licence MIT.

Tous les csv manipulés sont des csv avec header, la première ligne indiquant le nom des colonnes.

Installation

C'est packagé sur pypi, et cela fourni un utilitaire en ligne de commande. Roulez jeunesse.

pip3 install csvspoon

Concaténation de fichiers

C'est l'opération la plus simple. Si les fichiers n'ont pas les mêmes colonnes, le fichier de sortie contient toutes les colonnes de tous les fichiers, et du vide est ajouté pour les colonnes manquantes.

Par exemple, si on a les étudiants de 3 groupes de TD, on peut très bien faire un:

csvspoon cat listeD1.csv listeD2.csv listeD3.csv > listeD123.csv

Mais pour chaque fichier, on peut lui dire de ne prendre qu'une partie des colonnes:

csvspoon cat listeD1.csv:nom,prénom,mail,note listeD2.csv:nom,prénom,mail,note > listeD12.csv

On peut aussi renommer des colonnes lors du chargement. Imaginons que dans listeD1.csv le nom se nomme NOM et dans listeD2.csv le nom se nomme Nom et qu'on veuille qu'il se nomme nom dans le fichier de sortie:

csvspoon cat listeD1.csv:nom=NOM,prénom,mail,note listeD2.csv:nom=Nom,prénom,mail,note > listeD12.csv

On peut se servir de cela uniquement pour procéder au filtrage des colonnes. Pour récupérer seulement le mail et la note dans un fichier, on peut faire:

csvspoon cat liste.csv:mail,note > liste_mailnote.csv

Et il est capable de lire sur l'entrée standard, donc on peut utiliser - en tant que nom de fichier (mais si on veut rajouter des attributs de filtrage/renomage sur les colonnes avec :, il faut utiliser -- pour lui dire que ce n'est pas une option:

… | csvspoon cat -- -:mail,note | …

Application de formules

Un usage très important, c'est d'appliquer une formule pour déclarer une nouvelle colonne. Ce qui est important de noter, c'est que la formule est du code python qui est exécuté avec les locales qui valent les valeurs de la ligne. Ainsi, comme par défaut toutes les variables sont des str, pour ajouter une colonne NomPrénom, en ayant une colonne Nom et une colonne Prénom, on peut exécuter:

csvspoon apply liste.csv -a NomPrénom "Nom.capitalize()+' '+Prénom.capitalize()" > liste2.csv

Toutefois, pour certaines opération, il peut-être utile de changer le type de certaines colonne, cela se fait avec -t, ainsi si dans un fichier on a deux notes note1 et note2, on peut vouloir calculer la moyenne:

csvspoon apply liste.csv -t note1:float -t note2:float -a moyenne "(note1+note2)/2" > liste2.csv

Je peux vouloir charger des éléments à l'avance dans l'exécuteur, et pour cela -b est utile, (--np est un alias pour -b "import numpy as np"), ainsi pour calculer la moyenne géométrique:

csvspoon apply \
    liste.csv \
    --np -b "geom_mean = lambda x,y: np.sqrt(x*y)" \
    -t note1:float -t note2:float \
    -a moyenne "geom_mean(note1,note2)" \
  > liste2.csv

Je peux également appliquer un format sur la colonne de sortie, par exemple pour avoir un nombre à 2 décimales, je spécifie le format .2f:

csvspoon apply \
    liste.csv \
    --np -b "geom_mean = lambda x,y: np.sqrt(x*y)" \
    -t note1:float -t note2:float \
    -a moyenne:.2f "geom_mean(note1,note2)" \
  > liste2.csv

Et tout ce qu'on a vu dans cat pour la sélection des colonnes et le renomage des colonnes fonctionne avec : après le nom de fichier fonctionne. Et comme cat, apply peut fonctionner à partir de l'entrée standard.

Bien entendu, -a peut être spécifié de multiple fois, pour ajouter plusieurs colonnes.

Filtrer les lignes

Alors là c'est simple, on ajoute des filtres. Un filtres est une expression qui est évaluée en python (et on peut précharger avec -b, --np, et typer avec -t comme précédemment). Ainsi, pour ne conserver que les étudiants n'ayant pas la moyenne plus grande que 10 et ayant un nom commençant par "A", on peut faire:

csvspoon filter \
    list.csv \
    -t moyenne:float \
    -a "moyenne<10" \
    -a "nom.startswith('A')" \
  > liste2.csv

Faire un tri

On fait un tri, par rapport à une colonne, si on spécifie plusieurs colonnes (avec plusieurs -k) cela permet de définir une clef secondaire (puis d'ordre 3…).

Le tri peut être numérique ou inversé. Par exemple, si on a le groupe de TD de l'étudiant dans une colonne grp, et la moyenne dans une colonne moy, et que je veux trier par groupe, puis au sein de chaque groupe par moyenne, je fais:

csvspoon sort liste.csv -k grp -k moy -n > liste2.csv

Jointures

Là, on attaque ce qui est vraiment utile, indispensable, et la clef de voute de tous mes processus de gestion de résultats étudiants.

Les jointures se font sur toutes les colonnes portant le même nom (ce sont des jointures naturelles en terme de base de données). Par exemple si j'ai deux fichiers fichierA.csv et fichierB.csv ayant une colonne commune mail:

$ cat fichierA.csv
mail,a
toto@example.com,12
tutu@example.com,13
titi@example.com,14

$ cat fichierB.csv
mail,b
titi@example.com,1
toto@example.com,2

$ csvspoon join fichierA.csv fichierB.csv > fichier.csv

$ cat fichier.csv
mail,a,b
toto@example.com,12,2
titi@example.com,14,1

On remarquera que seule les lignes étant présentes dans tous les fichiers sont conservées, pour faire une jointure gauche ou droite, on utilisera -l ou -r:

$ csvspoon join -l fichierA.csv fichierB.csv > fichier.csv

$ cat fichier.csv
mail,a,b
toto@example.com,12,2
tutu@example.com,13,
titi@example.com,14,1

Exemple plus complexe et proche de ce que j'utilise, j'ai:

• un fichier de listing des étudiants, qui contient tous les étudiants, avec leur mail dans une colonne mail et plein d'autres colonnes que je veux conserver.
• un fichier de note obtenues à l'examen final, final.csv, qui contient entre autre une colonne mail, une colonne note, et plein de colonnes inutiles (détails internes de notation).
• un fichier provenant du moodle, moodle.csv, la colonne de mail se nomme Courriel, et la note se nomme note, il y a plein d'autres colonnes inutiles.

Je veux faire une jointure, il faut que:

• je filtre les colonnes de final.csv, et que je renomme note en Final.
• je filtre les colonnes de moodle.csv et que je renomme les colonnes Courriel en mail pour pouvoir faire la jointure et note en ControleContinu.
• je fasse une jointure gauche -l, je veux que les étudiants présents dans listing.csv soient présents dans le fichier global, même si ils ont une note vide.

csvspoon join -l \
    listing.csv \
    final.csv:mail,Final=note \
    moodle.csv:mail=Courriel,ControleContinu=note \
  > global.csv

Et en cumulant avec apply, je peux calculer la moyenne (en imaginant même coef):

csvspoon join -l \
    listing.csv \
    final.csv:mail,Final=note \
    moodle.csv:mail=Courriel,ControleContinu=note \
  | csvspoon apply \
    -a moyenne "((float(ControleContinu) if ControleContinu else 0)+(float(Final) if Final else 0))/2" \
  > global.csv

Agrégation

Là c'est simple, on lui donne une (ou des) colonne par rapport à laquelle agréger (une clef d'agrégation). Toutes les colonnes qui sont déterminés par la clef d'agrégation (dont la valeur est la même si la clef d'agrégation est la même) sont conservées, les autres sont ôtés. On peut ajouter des colonnes, avec une formule python qui prend en entrée toute la liste des valeurs pour chaque agrégation.

Rappel: --np est un alias de --before "import numpy as np"

Exemple, en supposant un fichier de note global.csv qui contient une colonne grp qui correspond au groupe de TD, et une colonne moyenne de l'étudiant, si je veux avoir la moyenne par groupe de TD:

csvspoon aggregate \
    global.csv \
    -k grp \
    -t moyenne:float \
    -a moyenne_parTD "np.mean(moyenne)"

Mais, en fait je peux faire ce que je veux, et utiliser plein d'autre chose, calculer, le min, le max, les quartiles, la médiane, l'écart-type…

csvspoon aggregate \
    global.csv \
    -k grp \
    -t moyenne:float \
    -a moyenne_parTD "np.mean(moyenne)" \
    -a std_parTD "np.std(moyenne)" \
    -a min_parTD "np.min(moyenne)" \
    -a q1_parTD "np.quantile(moyenne, .25)" \
    -a median_parTD "np.median(moyenne)" \
    -a q3_parTD "np.quantile(moyenne, .75)" \
    -a max_parTD "np.max(moyenne)" \

Conclusion

Dans mon usage, j'utilise intensivement toutes ces commandes, et je ne passe très peu par des fichiers temporaires, je pipe les commandes les unes dans les autres.

Exemple réel, voici une commande lancée en octobre passée:

csvspoon cat 't-5-c1.csv:Courriel=Adresse de courriel' \
  | csvspoon filter -a Courriel \
  | csvspoon apply -a T 1 \
  | csvspoon join -lr ../grpc.csv \
  | csvspoon filter -a 'C=="C1"' -a 'not T' \
  | csvspoon cat -- -:Courriel \
  | csvspoon join ../sme2.csv \
  > t-5-abs-c1.csv

Explication:

• le fichier t-5-c1.csv me permet d'avoir les résultats du tests du groupe C1, je cherche les absents, je ne récupère donc que les mails. Moodle me nomme la colonne en Adresse de courriel ce qui est chiant à manipuler, je la renomme.
• je filtre, car moodle me mets des lignes inutiles correspondants à aucun étudiant.
• j'ajoute une colonne T qui contient 1
• je fais une jointure externe avec le fichier qui contient la correspondance des étudiants avec les groupes de cours (colonne C)
• je filtre sur les étudiants devant être dans le groupe 1 et ayant T non positionné (donc absents lors du test).
• je vire les colonnes qui ne servent à rien.
• je fais une jointure avec un listing contenant les noms des étudiants, car avoir le nom, c'est mieux que les mails.

J'obtiens donc un fichier qui me répertorie les étudiants du groupe de cours 1 n'ayant pas passé le test.

csvformatmail: envoyer des mails à la chaine avec formatage

Pour certaines taches, j'ai besoin d'envoyer des mails en masse aux étudiants, mais avec du formatage. Pour cela, j'ai écrit un outil qui permet de le faire, il se base sur csvspoon, donc il y a certaine similitudes.

Le dépôt de dev est sur github, sous licence MIT.

Installation

C'est sur pypi, donc

pip3 install csvformatmail

Utilisation basique

Le contenu du template est évalué en temps que f-string, donc j'écris un template:

From: Moi enseignant <my-mail-address@example.org>
To: {mail}
Bcc: my-mail-address@example.org
Subject: Résultat du dernier test

Bonjour {Prénom} {Nom.capitalize()},

Vous avez obtenu au test les notes suivantes:

 - Partie A: {a:.1f}/10
 - Partie B: {b:.1f}/10

Donc au total: {a+b:.1f}/20.

En conséquence, vous avez {"réussi" if a+b>10 else "échoué"} le test.

-- 
Votre dévoué enseignant

Et là, il va falloir le donner à manger à csvformatmail. On doit donc avoir comme spécifié dans le template, les colonnes mail, Nom, Prénom, a et b, d'autres colonnes peuvent être présentes, ça ne change rien. Puisque nous appliquons un formatage de type float et des opérations sur a et b, il va falloir lui préciser que a et b sont des floats.

csvformatmail template.txt -t a:float -t b:float listing.csv

À noter: la lecture du csv est faite avec le module csvspoon, donc le renomage de colonne utilisé avec csvspoon fonctionne aussi ici.

Avec cette dernière commande il va utiliser un smtp local, il peut utiliser un smtp distant (avec un login), si un login est fourni, il demandera un passwd à l'exécution, et l'envoi se fera après être passé en TLS au moyen de starttls.

csvformatmail -h smtp.example.org -l mylogin template.txt -t a:float -t b:float listing.csv

N'ayez pas peur, il n'envoie pas les mails directement, il propose un prompt qui permet de les relire, et avant l'envoie, il demande de taper entièrement une phrase (un copier-collé n'est pas possible, puisqu'il faut mettre le nombre de mails dans la confirmation). Donc pas de craintes, tout est fait pour que l'envoie ne soit pas une erreur, quand on envoie 250 mails, on aime bien être certain de ne pas faire n'importe quoi.

Exemple de processus:

$ csvformatmail -h smtp.example.org -l login mail-t-10.txt t-10-v1.csv
Loaded 220 mails. What do you want to do with?
 - show
 - send
 - quit
Choice: send
To confirme, type "I want send <number> mails."
Confirmation: I want send 220 mails.
SMTP password for user login:

Utilisation plus avancée

Le contenu du template peut être plus complexe, et je peux définir du python avant mon template, pour faire des trucs sympa:

# python preamble begin
import numpy as np
def quartiles(list_values):
    q1, q2, q3 = np.percentile(list_values, (25,50,75))
    return f"{q1:.1f}, {q2:.1f}, {q3:.1f}"
# python preamble end

From: Moi enseignant <my-mail-address@example.org>
To: {mail}
Bcc: my-mail-address@example.org
Subject: Résultat du dernier test

Bonjour {Prénom} {Nom.capitalize()},

Vous avez obtenu au test les notes suivantes:

 - Partie A: {a:.1f}/10
 - Partie B: {b:.1f}/10

Donc au total: {a+b:.1f}/20.

En conséquence, vous avez {"réussi" if a+b>10 else "échoué"} le test.

Pour votre information, la moyenne de la partie A est {np.mean(cols['a']):.1}, 
et les quartiles sont {quartiles(cols['a'])}.

-- 
Votre dévoué enseignant

Puis, pour éviter de se faire mal voir par le smtp distant, on peut rajouter une temporisation de 10 secondes entre chaque mail:

csvformatmail -w 10 -h smtp.example.org -l mylogin template.txt -t a:float -t b:float listing.csv

Conclusion

Voici les outils que j'ai écrits, pour mon propre usage. Je les utilise intensivement, énormément, tout le temps. Récement, certains collègues m'ont fait comprendre que ça leur serait utile, j'ai donc du expliquer comment cela marchait, et je me suis donc dit que cela pouvait être utile à tous, et j'en fais donc un journal sur dlfp, en espérant vous être utile, ou du moins que le sujet vous intéresse.