Modification des données CSV / XLSX en Python pour admins Salesforce : vidéo et notes

09 Feb 2019 🔖 python pandas csv excel feuilles de calcul tutoriels salesforce samedi salesforce
💬 FR

Revoir la démonstration du 09 fév sur la simplicité et la puissance du Python pour modifier les feuilles de calcul – déstinée surtout aux administrateurs Salesforce.

Introduction

J’ai fait une démonstration “pratique” (interactive) “Salesforce Saturday”, déstinée aux administrateurs Salesforce, durante une heure.

Nous les admins, nous faisons tous de travaux avec Data Loader (2).

• On exporte des données aux fichiers CSV / XLSX.
• On les transforme avec Microsoft Excel.
• On importe le nouveau fichier à Salesforce avec Data Loader.

Mais quoi faire quand cette étape de transformation devient un peu pénible ?

Heureusement, en 2019, il existe un langage de programmation très bien aimé des non-développeurs, c’est à dire le Python.

J’ai démontré la facilité et la puissance de Python pour modifier les feuilles de calcul – surtout quand elles sont très grosses, quand l’on fait de tâches répétitives, et quand il faut rejoindre 2+ feuilles (sérieusement, qui rêve de faire un VLOOKUP) ?

Niveau tout débutant en programmation (mais avoir une certaine capacité avec les champs de formule et avec les formules en Excel vous aiderait – c’est au même niveau de difficulté).

Diapositives

Cliquez ici pour télécharger un fichier PDF des diapositives

Enregistrement en vidéo

(Cliquez ici pour voir l’enregistrement)

Exercises

Pourquoi coder nº 1 : modifier un gros fichier

Comme vous pouvez voir dans mon article de blog du 08 jan 2019, “Filtrer un gros fichier CSV avec Python”, Python peut modifier de très gros fichiers beaucoup plus vite que Excel.

On a supprimé les enregistrements dont le pays était l’Algérie, l’Arménie, l’Australie, ou la Barbade avec Excel et avec Python.

• En Excel, cette opération a fallu 7 secondes pour un fichier de 100.000 enregistrements (et n’a pas terminé en quelques minutes avec 1.000.000 enregistrements 😱).
• Avec Python, on a fini en 0,02 secondes pour les 100.000 et en 0,2 secondes pour les 1.000.000 enregistrements.

Le code entier est dans les diapositives; le code qui importe est :

import pandas
mauvaispays = ['Algeria','Armenia','Australia','Barbados']

df1 = pandas.read_csv('c:\\example\\100000 Sales Records.csv')
df1 = df1[~df1['Country'].isin(mauvaispays)]

df2 = pandas.read_csv('c:\\example\\1000000 Sales Records.csv')
df2 = df2[~df2['Country'].isin(mauvaispays)]

Les données sont du blog “E for Excel“.

Pourquoi coder nº 2 : tâches répétitives

Je recommande toujours de continuer d’utiliser Excel à chaque opportunité. Mais une tâche facile de quelques minutes en Excel peut devenir pénible s’il faut le refaire tous les jours.

J’ai démontré une série d’étapes qui n’a fallu qu’une minute en Excel:

• Ajouter une colonne “Notes”
• Filtrer le fichier 3 fois
• Ajouter 3 notes selon les données pendant les opérations de filtrer.

Normalement, une minute, ça va. Moi, je préférerais normalement Excel pour achever cette tâche !

Mais s’il fallait le faire chaque jour ? Même chaque semaine ? J’aurais envie d’automatiser mon travail … alors Python à l’aide !

import pandas

df = pandas.read_csv('c:\\example\\1000 Sales Records.csv')

lignes_cool_m = (df['Country'].str.startswith('M')) & (df['Order Priority'] == 'M')
lignes_froid = df['Country'].isin(['Canada','Greenland'])
lignes_gros_commandes = df['Total Revenue'] > 5000000

df['Notes'] = None
df['Notes'][lignes_cool_m] = 'Cool -- deux M !'
df['Notes'][lignes_froid] = 'Il fait froid'
df['Notes'][lignes_gros_commandes] = 'On commande beaucoup'

df.to_csv('c:\\example\\1000-avec-notes.csv', index=False)

Pourquoi coder nº 3 : rejoindre des fichiers

La jointure des tableaux, c’est vraiment mon cas d’utilisation préféré pour le code Python.

1. Me souvenir du syntaxe VLOOKUP() / INDEX(MATCH()) (RECHERCHEV() / INDEX(EQUIV()) en français ?) m’embête tellement.
2. Créer de colonnes supplémentaires pour concaténer les colonnes composantes un critère de concordances entre feuilles … je déteste de faire le poirier comme ça !
3. Copier-coller-modifier mes formules RECHERCHEV() de colonne en colonne pour ajouter chaque colonne de la deuxième feuille … beurk.
4. Ne pas savoir, quand je regarde ma formule dans un an, de quelles données elle s’agit (c’est quoi dans la colonne “AJ” ?!) ? Non, merci !

Voici la folie de rejoindre des données en Excel : c’est la formule d’une seule cellule ci-dessous. UNE SEULE ! Et quelles sortes de données se trouvent dans 'Feuille2'!$A:$A et 'Feuille2'!$G:$G ? Aucune idée sans regarder.

=IF(
 ISNA(
  INDEX(
   'Feuille2'!$A:$A,
   MATCH(
    TRIM($F2),
    'Feuille2'!$G:$G,
	0
   )
  )
 ),
 "",
 INDEX(
  'Feuille2'!$A:$A,
  MATCH(
   TRIM($F2),
   'Feuille2'!$G:$G,
   0
  )
 )
)

Par contre, voici une jointure en Python, qui lit les feuilles “Feuille1” et “Feuille2” d’un fichier “C:\exemple\deuxfeuilles.xlsx” et écrit sur la disque dur trois nouveaux fichiers:

1. unefeuille_comme_excel.xlsx
2. unefeuille_tous_enregistrements.xlsx
3. unefeuille_enregistrements_uniques.xlsx

import pandas

df1 = pandas.read_excel('c:\\example\\deuxfeuilles.xlsx', sheet_name='Feuille1')
df2 = pandas.read_excel('c:\\example\\deuxfeuilles.xlsx', sheet_name='Feuille2')

df2 = df2.rename(columns={'LastName':'Last', 'FirstName':'First', 'Em':'Email'})

jointuredf = df1.merge(df2, how='left', on=['Last','First','Email'])
jointure2df = df1.merge(df2, how='outer', on=['Last','First','Email'], indicator=True)
jointure3df = jointure2df.query('_merge != "both"')

jointuredf.to_excel('c:\\example\\unefeuille_comme_excel.xlsx', index=False)
jointure2df.to_excel('c:\\example\\unefeuille_tous_enregistrements.xlsx', index=False)
jointure3df.to_excel('c:\\example\\unefeuille_enregistrements_uniques.xlsx', index=False)

Si les contenus de la “Feuille1” sont ainsi :

Et si les contenus de la “Feuille2” sont ainsi :

Alors les contenus des 3 fichiers de sortie sont ainsi:

nº 1 : unefeuille_comme_excel.xlsx

nº 2 : unefeuille_tous_enregistrements.xlsx

nº 3 : unefeuille_enregistrements_uniques.xlsx

Exercises Collaboratives

Code de base

Pour les exercises collaboratives, on a commencé avec ce code-ci, que vous pouvez modifier ici:

import pandas
pandas.set_option('expand_frame_repr', False)

chemin1 = 'https://raw.githubusercontent.com/pypancsv/pypancsv/master/docs/_data/sample1.csv'
chemin2 = 'https://raw.githubusercontent.com/pypancsv/pypancsv/master/docs/_data/sample2.csv'

def p(quelquechose):
  print(quelquechose)
  print('---CLOISON---')

# #########################
# Ne supprimez pas le code au-dessus.  Le professeur l'a fourni.
# #########################

Code final

Après avoir fini, on a composé ce code-ci (ce qui inclue beaucoup de code transformé en “commentaires” pour qu’il n’éxécute pas), modifiable ici:

import pandas
pandas.set_option('expand_frame_repr', False)

chemin1 = 'https://raw.githubusercontent.com/pypancsv/pypancsv/master/docs/_data/sample1.csv'
chemin2 = 'https://raw.githubusercontent.com/pypancsv/pypancsv/master/docs/_data/sample2.csv'

def p(quelquechose):
  print(quelquechose)
  print('---CLOISON---')

# #########################
# Ne supprimez pas le code au-dessus.  Le professeur l'a fourni.
# #########################

#p('Salut !')

df1 = pandas.read_csv(chemin1)
#p(df1)

#p(len(df1))
#p(df1.columns)
#p(len(df1.columns))
#p(list(df1.columns))
#p(sorted(df1.columns))

col_nom = df1['Last']
#p(col_nom)
#p(list(col_nom))
cn_unq = col_nom.unique()
#p(cn_unq)
#p(len(cn_unq))

interessants = ['First','Last']
cols_nom_prenom = df1[interessants]
#p(cols_nom_prenom)
col_prenom = df1['First']
tous_nom = pandas.concat([col_nom, col_prenom])
#p(sorted(tous_nom))

df2 = pandas.read_csv(chemin2)
df2match = df2.rename(columns={'FirstName':'First', 'LastName':'Last'})
mergedf = df1.merge(df2match, on=interessants, how='outer', indicator=True)
#p(mergedf)
#p(mergedf.query('_merge != "both"'))

mergedf.to_csv('resultat.csv', index=False)
p('Fin')

Exercise 1 – afficher “Salut !” sur l’écran

Ce code :

p('Salut !')

A produit cette sortie :

Salut !
---CLOISON---

Exercise 2 – lire un fichier CSV et l’afficher sur l’écran

Ce code :

df1 = pandas.read_csv(chemin1)
p(df1)

A produit cette sortie :

      Id    First      Last           Email                      Company
0   5829    Jimmy    Buffet  jb@example.com                          RCA
1   2894  Shirley  Chisholm  sc@example.com       United States Congress
2    294  Marilyn    Monroe  mm@example.com                          Fox
3  30829    Cesar    Chavez  cc@example.com          United Farm Workers
4    827  Vandana     Shiva  vs@example.com                     Navdanya
5   9284   Andrea     Smith  as@example.com     University of California
6    724   Albert    Howard  ah@example.com  Imperial College of Science
---CLOISON---

Exercise 3 – afficher le nombre d’enregistrements dans le CSV et quelques infos sur les noms de ses colonnes

Ce code :

p(len(df1))
p(df1.columns)
p(len(df1.columns))
p(list(df1.columns))
p(sorted(df1.columns))

A produit cette sortie :

7
---CLOISON---
Index(['Id', 'First', 'Last', 'Email', 'Company'], dtype='object')
---CLOISON---
5
---CLOISON---
['Id', 'First', 'Last', 'Email', 'Company']
---CLOISON---
['Company', 'Email', 'First', 'Id', 'Last']
---CLOISON---

Exercise 4 – afficher quelques infos sur la colonne “nom de famille”

Ce code :

col_nom = df1['Last']
p(col_nom)
p(list(col_nom))
cn_unq = col_nom.unique()
p(cn_unq)
p(len(cn_unq))

A produit cette sortie :

0      Buffet
1    Chisholm
2      Monroe
3      Chavez
4       Shiva
5       Smith
6      Howard
Name: Last, dtype: object
---CLOISON---
['Buffet', 'Chisholm', 'Monroe', 'Chavez', 'Shiva', 'Smith', 'Howard']
---CLOISON---
['Buffet' 'Chisholm' 'Monroe' 'Chavez' 'Shiva' 'Smith' 'Howard']
---CLOISON---
7
---CLOISON---

Exercise 5 – afficher que les colonnes composant les noms + concaténer toutes les parties des noms dans une seule liste en ordre alphabétique

Ce code :

interessants = ['First','Last']
cols_nom_prenom = df1[interessants]
p(cols_nom_prenom)
col_prenom = df1['First']
tous_nom = pandas.concat([col_nom, col_prenom])
p(sorted(tous_nom))

A produit cette sortie :

     First      Last
0    Jimmy    Buffet
1  Shirley  Chisholm
2  Marilyn    Monroe
3    Cesar    Chavez
4  Vandana     Shiva
5   Andrea     Smith
6   Albert    Howard
---CLOISON---
['Albert', 'Andrea', 'Buffet', 'Cesar', 'Chavez', 'Chisholm', 'Howard', 'Jimmy', 'Marilyn', 'Monroe', 'Shirley', 'Shiva', 'Smith', 'Vandana']
---CLOISON---

Exercise 6 – rejoindre deux feuilles de calcul

Ce code:

df2 = pandas.read_csv(chemin2)
df2match = df2.rename(columns={'FirstName':'First', 'LastName':'Last'})
mergedf = df1.merge(df2match, on=interessants, how='outer', indicator=True)
p(mergedf)
p(mergedf.query('_merge != "both"'))

A produit cette sortie :

        Id    First      Last           Email                      Company PersonId                     Em FavoriteFood      _merge
0   5829.0    Jimmy    Buffet  jb@example.com                          RCA      NaN                    NaN          NaN   left_only
1   2894.0  Shirley  Chisholm  sc@example.com       United States Congress      NaN                    NaN          NaN   left_only
2    294.0  Marilyn    Monroe  mm@example.com                          Fox     x934         mm@example.com      Carrots        both
3  30829.0    Cesar    Chavez  cc@example.com          United Farm Workers      NaN                    NaN          NaN   left_only
4    827.0  Vandana     Shiva  vs@example.com                     Navdanya      02e         vs@example.com     Amaranth        both
5   9284.0   Andrea     Smith  as@example.com     University of California    9e84f         as@example.com         Kale        both
6    724.0   Albert    Howard  ah@example.com  Imperial College of Science      8xi  ahotherem@example.com     Potatoes        both
7      NaN  Shirley    Temple             NaN                          NaN    983mv         st@example.com    Lollipops  right_only
8      NaN   Donald      Duck             NaN                          NaN    k28fo         dd@example.com     Pancakes  right_only
---CLOISON---
        Id    First      Last           Email                 Company PersonId              Em FavoriteFood      _merge
0   5829.0    Jimmy    Buffet  jb@example.com                     RCA      NaN             NaN          NaN   left_only
1   2894.0  Shirley  Chisholm  sc@example.com  United States Congress      NaN             NaN          NaN   left_only
3  30829.0    Cesar    Chavez  cc@example.com     United Farm Workers      NaN             NaN          NaN   left_only
7      NaN  Shirley    Temple             NaN                     NaN    983mv  st@example.com    Lollipops  right_only
8      NaN   Donald      Duck             NaN                     NaN    k28fo  dd@example.com     Pancakes  right_only
---CLOISON---

Exercise 7 – créer un nouveau fichier CSV des données modifiées

Ce code:

mergedf.to_csv('resultat.csv', index=False)

A produit un fichier “resultat.csv” dont les contenus sont :

Id,First,Last,Email,Company,PersonId,Em,FavoriteFood,_merge
5829.0,Jimmy,Buffet,jb@example.com,RCA,,,,left_only
2894.0,Shirley,Chisholm,sc@example.com,United States Congress,,,,left_only
294.0,Marilyn,Monroe,mm@example.com,Fox,x934,mm@example.com,Carrots,both
30829.0,Cesar,Chavez,cc@example.com,United Farm Workers,,,,left_only
827.0,Vandana,Shiva,vs@example.com,Navdanya,02e,vs@example.com,Amaranth,both
9284.0,Andrea,Smith,as@example.com,University of California,9e84f,as@example.com,Kale,both
724.0,Albert,Howard,ah@example.com,Imperial College of Science,8xi,ahotherem@example.com,Potatoes,both
,Shirley,Temple,,,983mv,st@example.com,Lollipops,right_only
,Donald,Duck,,,k28fo,dd@example.com,Pancakes,right_only

Ou, plus lisible :

Solution “Coding On Demand”

Un des étudiants a demandé de l’aide avec un problème avancé. Voici une transformation que j’espère approcherait ses besoins :

Données d’entrée :

Code Python :

import pandas
chemin1 = 
df1 = pandas.read_csv('https://raw.githubusercontent.com/pypancsv/pypancsv/master/docs/_data/sample1.csv')

df1['parties'] = df1['Email'].str.split(pat='@', n=1, expand=False)

mauvais_lignes = df1['parties'].apply(lambda x : len(x) > 0 and x[0] in ['cc','mm'])

df1['nouveau'] = None
df1.loc[mauvais_lignes, 'nouveau'] = df1['parties'].apply(lambda x: x[1].split('.')[0])
df1.loc[mauvais_lignes, 'Company'] = df1['parties'].apply(lambda x: x[1].split('.')[1])
df1.loc[mauvais_lignes, 'Email'] = df1['parties'].apply(lambda x: '@' + x[1])

df1 = df1.drop(columns=['parties'])

df1.to_csv('c:\\example\\fichierdesortie.csv', index=False)

Données de sortie :

Ressources et liens

Anglais

• Les diapositives (PDF) d’une démonstration un peu plus technique que celle d’aujourd’hui, et aussi les exercises niveau débutant (mais plus éxigants que ceux d’aujourd’hui) qui accompagnent
• Ma liste de liens en anglais – je recommande surtout parmi ces liens :
  • Tâches Excel courantes illustrées en Python - partie 1 sur “Practical Business Python”
  • Tâches Excel courantes illustrées en Python - partie 2 sur “Practical Business Python”
  • Trouver les différences entre 2 fichiers Excel avec Python sur “Practical Business Python”
• Les vidéos et exercises de deux ateliers pratiques (de quelques heures) que j’ai enseigné aux administrateurs Salesforce :
  • nº 1 - “Python pour Salesforce 101” – la programmation pour tout débutants
  • nº 2 - “Python pour Salesforce 102” – beaucoup sur la concaténation et la jointure des feuilles de calcul
• Documentation officielle de toutes les commandes “Pandas” en Python
  • Documentation officielle de toutes les commandes pour manipuler les colonnes simples (“Séries”) en Python
  • Documentation officielle de toutes les commandes pour manipuler les colonnes simples (“Séries”) dont les contenus sont en texte clair en Python

Français

Désolée – beaucoup des ressources en français sont déstinées aux Data Scientists, alors trop compliquées pour les débutants qui ne veulent que faire quelques petites transformations à leurs feuilles de calcul.

Le Python pour la modification des fichiers CSV / XLS peut être très simple à écrire pour les tout débutants, mais en ce moment, il n’y a pas de telle documentation en français.

J’espère changer tout ça en éditant des ressources !

Mais pour le moment, voici les ressources les plus proches que j’ai pu trouver en français …

• Lecture et écriture de fichiers CSV et Excel avec Python sur python-simple.com (notez: il y a deux barres de défilement verticale – n’oubliez pas de tirer sur la barre intérieur pour voir tous les contenus)
• Modification des “DataFrames” avec Python sur python-simple.com (notez: il y a deux barres de défilement verticale – n’oubliez pas de tirer sur la barre intérieur pour voir tous les contenus). Un “DataFrame” est le mot technique pour un tableau de données quand on se sert de la partie de Python qui s’appelle “Pandas”.
• Concaténation et jointure des “DataFrames” avec Python sur python-simple.com (notez: il y a deux barres de défilement verticale – n’oubliez pas de tirer sur la barre intérieur pour voir tous les contenus).
• Tout sur les “DataFrames” en Python sur kaggle.com. Un “DataFrame” est le mot technique pour un tableau de données quand on se sert de la partie de Python qui s’appelle “Pandas”.
• Tout sur les “Séries” en Python sur kaggle.com. Une “Série” est le mot technique pour un enregristement OU une colonne des données d’un tableau quand on se sert de la partie de Python qui s’appelle “Pandas”.
• Les “commentaires” en Python
• Les “variables” et comment les affecter sur numworks.com
• Les “variables” et comment les affecter par Denis LE FUR.
• Création des “listes” en Python par Denis LE FUR. (Comme j’ai fait dans mon code avec mauvaispays = ['Algeria','Armenia','Australia','Barbados'])
• Manipulation des “listes” avec Python par Denis LE FUR.
• Usage des “dictionnaires” en Python
• Caractères et chaînes de caractères en Python par Denis LE FUR.
• Une initiation à Python Pandas par Raphaël da Silva