Python for CSV Files - Deduplicating "Some States, But Not Others"

(Version française en dessous)

I encountered a lovely "Excel to Python" business problem this morning. Many thanks to my colleague for explaining it to me, and apologies for snooping.

He had a spreadsheet containing 20,000 American high schools (column B), what state they were in (column C), and which admissions counselor was assigned to the school (column A).

His university assigns individual admissions counselors to schools within the states of Delaware (DE) and Maryland (MD), but elsewhere, each counselor covers an entire state. Nevertheless, this particular spreadsheet had every single high school listed out, with its state and counselor, no matter where it was located.

What my colleague wanted to do was reduce the size of the spreadsheet so that Delaware and Maryland schools would be listed individually, but so that the rest of the spreadsheet would simply contain a list of the remaining 48 U.S. states and the counselors assigned to them.

The input CSV file looked like this:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Mo,Denver HS,CO
Ana,Howard HS,DE
Mo,Boulder HS,CO
Mo,Boise HS,ID
Leia,Fairfax HS,VA
Mo,Excel Academy,ID
Mo,Mann HS,ID
Leia,Roanoke HS,VA

He wanted the output CSV file to look like this:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Ana,Howard HS,DE
Mo,STATE-CO,CO
Mo,STATE-ID,ID
Leia,STATE-VA,VA

Because we know exactly which states are treated "one way" versus "another way," this is a very straightforward "split the dataset horizontally, do something to one part of the split, and concatenate it back together" operation.

In fact, we don't even really need Python for this. In Excel, in 2 minutes, we could:

1. Filter the spreadsheet to "everything but DE & MD"
2. Extract those rows to a new tab of the spreadsheet
3. Do "Remove Duplicates" on column C
   • (or, if we wanted to dummy-check for accidental territory splits in the "other 48" states, on columns A & C)
4. Alter the contents of column B to be a concatenation of the word "STATE-" and the contents of column C
5. Un-filter our original (now shorter) worksheet
6. Cut-paste our "temporary" worksheet back onto the end of it

But if we did want to use Python, the script would be pretty straightforward. It would imitate those same steps in Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
dfdemd = df[df['State'].isin(['DE','MD'])]
dfoth48 = df[~df['State'].isin(['DE','MD'])]
dfoth48 = dfoth48.drop_duplicates(subset=['State'], keep='first')
dfoth48['School'] = 'STATE-'+dfoth48['State']
df = pandas.concat([dfdemd,dfoth48])
df.to_csv('c:\\example\\FilteredHighSchools.csv', index=0, quoting=1)

Note that if this were an Excel XLSX file, we would use read_excel and to_excel instead (eliminating the ", quoting=1" option).

---

Where the problem gets really interesting is that we can let the computer tell us which states have "just 1 counselor for the whole state" and which states "should be listed school-by-school, because they seem to be split."

In Excel, we would:

1. Add a "column D" counting the number of unique "column A" values per value in "column C."
   • (There are various tips and tricks available on the internet to accomplish this in Excel.)
2. Then we would do the same "split, alter part of the data, and re-concatenate" trick we did earlier, only we would "split" based on "column D" instead of based on "column C" (looking for "1" vs. "2 & up").

Again, in Python, we'll imitate our steps in Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
df['Counts'] = df['State'].map(df[['Counselor','State']].drop_duplicates(keep='first')['State'].value_counts())
df_mult_couns = df[df['Counts'] > 1]
df_1_couns = df[df['Counts'] == 1]
df_1_couns = df_1_couns.drop_duplicates(subset=['State'], keep='first')
df_1_couns['School'] = 'STATE-'+df_1_couns['State']
df = pandas.concat([df_mult_couns,df_1_couns])
df = df.drop(['Counts'], axis=1)
df.to_csv('c:\\example\\SmartFilteredHighSchools.csv', index=0, quoting=1)

This script is a lot like the previous one, only I renamed "dfdemd" to "df_mult_couns" because we are presuming that we don't yet know it's Delaware and Maryland that have multiple counselors per state. Similarly, I renamed "dfoth48" to "df_1_couns."

The new lines of code are the 3rd line and the 2nd-to-last line. Also, the 4th & 5th lines have been altered to filter on "Counts" instead of filtering on "State." Still, once we have split our data, we no longer need the "Counts" column (we could have "dropped" it from each fragment of our dataset here instead of from the re-concatenated dataset right before exporting to CSV), and we do our "dropping of duplicates" in the "1-counselor-per-state" states as usual, by "State."

Line 3 is our most complicated line of code. Let's work from the middle, starting inside the ".map()" command.

1. "df[['Counselor','State']]" produces a new DataFrame that is a copy of our original DataFrame, but with data from only those 2 columns.
2. Appending "drop_duplicates(keep='first')" to it produces a new DataFrame that is a copy of it, only using "Counselor" and "State" together (that is, all 2 columns of our input DataFrame) as a matching key.
3. Appending "['State'].value_counts()" to that "deduplicated" DataFrame produces a new "Series" (fancy list-like thing) where the "item IDs" are the states from our data set, and the values are the number of unique "Counselor" values for that "State" value.

Now that we know what's inside the ".map()" command (a "Series" of states and their "unique Counselor value counts"), let's take a look at Line 3 of our code, starting after the "=."

4. "df['State'].map(ourSeriesOfStatesWithCounselorCounts)" produces a new "Series," with the same set of "item IDs" as the "row IDs" of our original DataFrame we imported from CSV, and with values populated by numbers indicating the number of "distinct counselor values" for the "State" found in that row of our data.

Its output looks like this:

0     2
1     2
2     2
3     2
4     1
5     2
6     1
7     1
8     1
9     1
10    1
11    1
Name: State, dtype: int64

5. Finally, all we do by putting this to the right of "df['Counts'] = " is to add a new "Counts" column to our original DataFrame and fill it in with these numbers.

Voila! We've added "column D!"

It was a little fancy getting there, but save this script to your computer -- or bookmark this blog post -- and you can copy/paste/tweak it any time it seems useful, whether or not you understand it enough to write it from scratch. (Don't worry -- I had to Google it and copy/paste/modify other people's code myself. 😊)

---

---

🇫🇷 - en français

Aujourd'hui j'ai rencontré un beau problème Excel bien adapté à résoudre avec Python.

Un collègue travaillait sur une feuille de calcul contenant 20 000 lycées américains (colonne B), l'état des E-U où chacun se trouve (colonne C) et le nom du recruteur qui se charge de l'école (colonne A).

L'université où travaille mon collègue divise la charge de travail du Maryland ("MD") et du Delaware ("DE"). Aux autres 48 états, un seul recruteur gère tous les lycées de l'état.

Mon collègue voulait réduire la taille de cette feuille de calcul. Il ne voulait changer aucun enregistrement du Delaware ni du Maryland. Pour les 48 autres états, il voulait éliminer la liste des écoles et garder uniquement 48 enregistrements, contenant chaque état et le recruteur qui s'en charge.

Sa feuille de calcul ressemblait à celle-ci:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Mo,Denver HS,CO
Ana,Howard HS,DE
Mo,Boulder HS,CO
Mo,Boise HS,ID
Leia,Fairfax HS,VA
Mo,Excel Academy,ID
Mo,Mann HS,ID
Leia,Roanoke HS,VA

Il souhaitait une feulle de calcul de sortie comme celle-ci:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Ana,Howard HS,DE
Mo,STATE-CO,CO
Mo,STATE-ID,ID
Leia,STATE-VA,VA

Comme on sait déjà comment diviser la feuille pour traiter les données de telle ou telle manière, le travail est très simple. Cela consiste à diviser les données en "DE + MD" et "ailleurs", à dédupliquer la partie "ailleurs", et à concaténer les deux divisions.

En fait, on n'a pas vraiment besoin de Python. C'est un travail de 2 minutes avec Excel.

1. filtrer la feuille de calcul pour extraire les enregistrements "ailleurs"
2. extraire ces lignes dans un nouvel onglet
3. dédupliquer sur la colonne "C"
   • (ou, pour vérifier qu'il n'y ait pas d'erreurs de saisie de données, sur "A" et "C" ensemble, cherchant plus que 2 enregistrements par état)
4. modifier le contenu de la colonne "B" pour qu'il soit une concaténation du mot "STATE-" et du contenu de la colonne "C"
5. supprimer le filtre de notre onglet de feuille de calcul d'origine (où rien ne reste que les données "Maryland" et "Delaware")
6. couper-coller les contenus du nouvel onglet à la fin de l'originale

Mais le code Python ne serait pas trop compliqué. Le script imiterait nos étapes Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
dfdemd = df[df['State'].isin(['DE','MD'])]
dfoth48 = df[~df['State'].isin(['DE','MD'])]
dfoth48 = dfoth48.drop_duplicates(subset=['State'], keep='first')
dfoth48['School'] = 'STATE-'+dfoth48['State']
df = pandas.concat([dfdemd,dfoth48])
df.to_csv('c:\\example\\FilteredHighSchools.csv', index=0, quoting=1)

(Pour un fichier XLSX et non CSV, on se servirait des commandes "read_excel" et "to_excel" et on supprimerait le code ", quoting=1" de la fin du script.)

---

Mais notre problème devient vraiment intéressant car nous pouvons laisser Python calculer quels états n'ont qu'un seul recruteur et quels états sont la charge de plusieurs recruteurs.

Avec Excel, il s'agit de:

1. Ajouter une "colonne D" qui compte le nombre de valeurs uniques du "colonne A" pour chaque valeur dans "colonne C."
   • (Il y a des astuces en ligne pour y arriver.)
2. Répéter notre opération de "scinder, modifier une partie, concaténer" de plus tôt. Sauf que nous divisons par la colonne "D" ("1" et "2 ou plus").

Encore une fois, le code Python va imiter nos étapes Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
df['Counts'] = df['State'].map(df[['Counselor','State']].drop_duplicates(keep='first')['State'].value_counts())
df_mult_couns = df[df['Counts'] > 1]
df_1_couns = df[df['Counts'] == 1]
df_1_couns = df_1_couns.drop_duplicates(subset=['State'], keep='first')
df_1_couns['School'] = 'STATE-'+df_1_couns['State']
df = pandas.concat([df_mult_couns,df_1_couns])
df = df.drop(['Counts'], axis=1)
df.to_csv('c:\\example\\SmartFilteredHighSchools.csv', index=0, quoting=1)

Ce script ressemble beaucoup au précédent. J'ai renommé "dfdemd" en "df_mult_couns" car dans ce cas, nous ne savons pas encore que "Delaware" et "Maryland" auront plusieurs recruteurs par état. De même, j'ai renommé "dfoth48" en "df_1_couns".

Les nouvelles lignes de code sont la 3ème ligne et l'avant-dernière ligne.

En outre, les lignes 4 et 5 ont été modifiées pour filtrer sur "Counts" au lieu de filtrer sur "State".

Apres les lignes 4 et 5, nous n'avons plus besoin de la colonne "Counts". (Nous aurions pu le supprimer de chaque scinde après les lignes 4 et 5, mais j'ai décidé plutôt de supprimer cette colonne des données reconcaténées dans l'avant-dernière ligne).

On fait toujours notre déduplication principale par "State."

La ligne 3 du code est la ligne la plus compliquée. Inspectons-la en commençant au milieu, à l'intérieur de la commande ".map()".

1. "df[['Counselor','State']]" génère un nouveau "DataFrame" qui est une copie de notre DataFrame d'origine, mais avec des données provenant uniquement de ces 2 colonnes.
2. L'ajout de ".drop_duplicates(keep = 'first')" le déduplique par recruteur et état, pour générer encore un nouveau "DataFrame".
3. L'ajout de "['State'].value_counts()" à ce DataFrame "dédupliqué" génère une nouvelle "Série" (élément de type liste ... amélioré ... plutôt tableau associatif) où les clés sont nos états et les valeurs sont le nombre de recruteurs uniques pour chaque état.

Maintenant que nous comprenons l'intérieur de ".map()" ("Série" avec les états comme clé), examinons la partie de la ligne 3 qui suit le "=".

4. "df['State'].map(notre_Tableau_Associatif)" produit une nouvelle "Série" avec le même ensemble de clés que sont les clés des enregistrements de notre "DataFrame" d'origine (de notre fichier CSV) -- c'est-à-dire les numéros qui identifie les lignes du fichier. Les valeurs de cette nouvelle "Série" sont les nombres de recruteurs qui correspondent à l'état trouvé sous "State" pour chaque enregistrement.

La sortie de cette commande ressemble à:

0     2
1     2
2     2
3     2
4     1
5     2
6     1
7     1
8     1
9     1
10    1
11    1
Name: State, dtype: int64

5. Finalement, "df['Counts'] = " ne fait qu'ajouter cette liste nombres de recruteurs au DataFrame d'origine comme nouvelle colonne (nommée "Counts").

Et voilà! On a ajouté la "colonne D" ! Après, ce n'est qu'une question de finir les filtres.

Comme j'ai dit, la ligne 3 était peut-être un peu compliqué, mais enregistrez ce script dans un fichier sur votre ordinateur -- ou ajoutez cet article à vos favoris -- et vous pouvez le copier-coller-modifier au moment qu'il vous convient (que vous le compreniez bien ou non). (Ne vous inquiétez pas, moi aussi j'ai Googlé pour écrire cet article ! 😊)

---

---

"Python for Salesforce Administrators" Table of Contents

• Introduction & Table of Contents
• Before You Begin Programming
• Installing Python 3 on Windows
• First Scripts - Import/Export CSVs, Filter Out Rows
• Rename/Drop/Add Columns
• Recognizing Pandas DataFrames vs. Series (Important!)
• Fancier Row-Filtering and Data-Editing
• Combining Multiple Tables
• Filtering Rows By Maximum Date Per Group
• Introduction to XML and JSON