Deduplicate Salesforce Contact Email Addresses with Python
Tags: salesforce, python, csv, tutorial
Published: 2018-11-23 21:31:53

---
title: Deduplicate Salesforce Contact Email Addresses with Python
published: true
tags: salesforce,python,csv,tutorial
cover_image: https://katiekodes.com/images/reader-problem-solved-excel-python-npspdedupe.png
canonical_url: https://katiekodes.com/salesforce-python-qa-001-answer/
---

Salesforce [#AwesomeAdmin](https://twitter.com/hashtag/awesomeadmin) Matthew ([@matthewsalibi](https://twitter.com/matthewsalibi)) [asked how he could use Python to deduplicate Salesforce NPSP email addresses](https://katiekodes.com/salesforce-python-qa-001/) across several fields. Today I’ll provide you a script you can copy, paste, and try on your own data. And for the geeks, I’ll break it down and explain how every line works.

## Data & Expectations

### Input Data

As a recap, Matthew proposed that we start with a CSV-formatted export of his “Contacts” table, “ **contacts.csv** ,” that looked something like this _(I consolidated his examples and added a bit of complexity)_:

| SalesforceID | Human-Readable ID | FirstName | LastName | PreferredEmail | PersonalEmail | WorkEmail | AlternateEmail |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 003…01 | BBBB | Chidi | Anagonye | Work | ca@gp.com | ca@hotmail.com | ca@hotmail.com |
| 003…02 | DDDD | Eleanor | Shellstrop | Personal | es@gp.com |   |   |
| 003…03 | EEEE | Jason | Mendoza | Work | jm@gp.com | jm@gp.com | jm@gp.com |
| 003…04 | FFFF | Eleanor | Shellstrop | Alternate | es@hotmail.com |   | es@gp.com |
| 003…05 | GGGG | Janet | Della-Denunzio | Alternate | jd@gp.com |   | jm@gp.com |
| 003…06 | HHHH | Tahani | Al-Jamil | Alternate | ta@gp.com | ta@yahoo.com | ta@gp.com |
| 003…07 | IIII | Chidi | Anagonye | Work |   | ca@gp.com |   |
| 003…08 | JJJJ | Mindy | St. Claire | Personal | ms@mp.com |   |   |
| 003…09 | KKKK | Kamilah | Al-Jamil | Personal | ka@kafoundation.org |   |   |
| 003…10 | AAAA | Eleanor | Shellstrop | Alternate |   |   | es@gp.com |
| 003…11 | CCCC | Ellie | Shellstrop | Work |   | es@gp.com |   |
| 003…12 | LLLL | Mindy | St. Claire | Personal | ms@yahoo.com | ms@hotmail.com | ms@hotmail.com |
| 003…13 | MMMM | Mindy | St. Claire | Work | ms@z.com | ms@z.com | ms@z.com |
| 003…14 | NNNN | Tahani | Al-Jamil | Alternate |   |   | ta@gp.com |

### Output File #1

Matthew [wanted to see](https://katiekodes.com/salesforce-python-qa-001/#de-duping-named-email-fields-within-a-single-record) an “ **output1.csv** ” that would identify duplicates _within_ rows and provide a “cleanup” file, ready for Data Loader, that looks like this:

| SalesforceID | Human-Readable ID | FirstName | LastName | PreferredEmail | PersonalEmail | WorkEmail | AlternateEmail |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 003…01 | BBBB | Chidi | Anagonye | Work | ca@gp.com | ca@hotmail.com |   |
| 003…03 | EEEE | Jason | Mendoza | Work |   | jm@gp.com |   |
| 003…06 | HHHH | Tahani | Al-Jamil | Alternate |   | ta@yahoo.com | ta@gp.com |
| 003…13 | MMMM | Mindy | St. Claire | Work |   | ms@z.com |   |

### Output File #2A

To identify entire _rows_ suspected of being redundant Contacts, Matthew [proposed a “ready for DemandTools” file](https://katiekodes.com/salesforce-python-qa-001/#de-duping-named-email-fields-across-records) “ **output2a.csv** ” that would look like the data below.

This data is compatible with the “Select Master / Non-Master records from file” functionality of DemandTools’s “Single Table Dedupe” module.

Each row indicates a “same email address, same first name” pairing that was found in multiple Contact records _(where the “email address” could have appeared in any of the “Email” fields that come with the NPSP)_.

Each column is the Salesforce ID of a Contact in which the email address was found.

| Master | Duplicate\_1 | Duplicate\_2 |
| --- | --- | --- |
| 003…01 | 003…07 |   |
| 003…10 | 003…02 | 003…04 |
| 003…06 | 003…14 |   |

### Output File #2B

For sets of Contacts that shared an email address but _not_ a first name, Matthew wanted to see a bit more detail in the output file “ **output2b.csv** ”, to facilitate human review. He suggested it look like the output below.

| emAddr | Master | Master\_FN | Master\_LN | Duplicate\_1 | Duplicate\_1\_FN | Duplicate\_1\_LN |
| --- | --- | --- | --- | --- | --- | --- |
| es@gp.com | 003…10 | Eleanor | Shellstrop | 003…11 | Ellie | Shellstrop |
| jm@gp.com | 003…03 | Jason | Mendoza | 003…05 | Janet | Della-Denunzio |

* * *

![](https://3.bp.blogspot.com/-5_6YUIk62jc/T9XkEZOZDlI/AAAAAAAAEcU/vyuTeWex3DA/s1600/Solar-powered-car-fan-large.jpg)

## Running The Code Yourself

Unfortunately, I haven’t yet made a blog post about running Python code on your own computer, so if you’re totally new to Python, you’re a bit stuck – but show this code to a colleague who _does_ know how!

Or, if you’re **adventurous** , [download an “Anaconda suite” installer](https://www.anaconda.com/download/) for your computer’s operating system and give it a try.

- Don’t bother installing VSCode when it asks if you want to.
- Once it’s done, bring up the “Anaconda Navigator” and open “Spyder.” 
  - Type `print('hello')` in the code-editing panel at the left, hit “run” near the top (if a box pops up, hit “cancel” and do it again), and then check to see if `hello` is printed at the right-hand side of your screen in an output console _(probably bottom right)_.
  - If so, congrats – you just wrote a program! Backspace out `print(hello)` and copy/paste my code in instead, then start tweaking it.

And [stay tuned](twitter.com/katiekodes) for my “office hours” and “hands-on trainings,” where I teach **total non-programmers** to run code like this.

* * *

![](https://c.pxhere.com/photos/30/93/coding_code_programming_programming_language_source_code-89334.jpg!d)

## The Code

There’s no shame in simply copying this code, pasting it into your Python “IDE,” and making a few tweaks!

You will, of course, have to do a bit of “Find & Replace” to fix up column-name references _(which, by the way, are **case-sensitive to match their capitalization in your input CSV file** )_, if your columns aren’t precisely spelled “PersonalEmail,” “WorkEmail,” “Human-Readable ID,” etc.

### Code to produce “Output1” only

```
import time
start_time = time.time()
import pandas
pandas.set_option('expand_frame_repr', False)

emFieldNames = ['PersonalEmail','WorkEmail','AlternateEmail'] # Edit this before running
pickToField = {'Personal':'PersonalEmail','Work':'WorkEmail','Alternate':'AlternateEmail'} # Edit this before running
inputFilePath = r'c:\example\contacts.csv' # Edit this before running
output1FilePath = r'c:\example\output1.csv' # Edit this before running
output1NoneFoundFilePath = r'c:\example\nonefound1.txt' # Edit this before running

df = pandas.read_csv(inputFilePath, dtype=object)

df['ChangedAnything'] = False

validPrefEmailIndicatorTFSeries = df['PreferredEmail'].map(pickToField).isin(df.columns)
df.loc[validPrefEmailIndicatorTFSeries,'PrefEmAddr'] = df.lookup(validPrefEmailIndicatorTFSeries.index, df[validPrefEmailIndicatorTFSeries]['PreferredEmail'].map(pickToField))

for fieldName in emFieldNames:
    needsClearingTFSer = (df['PreferredEmail'].map(pickToField) != fieldName) & (df[fieldName] == df['PrefEmAddr'])
    df.loc[needsClearingTFSer,fieldName] = None
    df.loc[needsClearingTFSer,'ChangedAnything'] = True

output1df = df[df['ChangedAnything']]

output1df = output1df.drop(columns=['PrefEmAddr','ChangedAnything'])

if len(output1df) > 0:
    output1df.to_csv(output1FilePath, index=False, quoting=1)
else:
    with open(output1NoneFoundFilePath, 'w') as f:
        f.writelines('No records found')

print('Done running script. Took ' + str(round(time.time()-start_time,2)) + ' seconds.')
```

### Code to produce “Output2A” and “Output2B” only

```
import time
start_time = time.time()
from collections import OrderedDict
import pandas
pandas.set_option('expand_frame_repr', False)

emFieldNames = ['PersonalEmail','WorkEmail','AlternateEmail'] # Edit this before running
pickToField = {'Personal':'PersonalEmail','Work':'WorkEmail','Alternate':'AlternateEmail'} # Edit this before running
inputFilePath = r'c:\example\contacts.csv' # Edit this before running
output2aFilePath = r'c:\example\output2a.csv' # Edit this before running
output2aNoneFoundFilePath = r'c:\example\nonefound2a.txt' # Edit this before running
output2bFilePath = r'c:\example\output2b.csv' # Edit this before running
output2bNoneFoundFilePath = r'c:\example\nonefound2b.txt' # Edit this before running

df = pandas.read_csv(inputFilePath, dtype=object)

df = df.sort_values('Human-Readable ID')
df = df.reset_index(drop=True)
df = df.set_index(['Human-Readable ID','SalesforceID','FirstName','LastName','PreferredEmail'])

allEmailsSeries = df[emFieldNames].stack()

groupedEmailSeriesGroupItems = allEmailsSeries.groupby(allEmailsSeries).groups.items()

emsAndTheirCellsFound = {emaddr:([x for x in indices]) for emaddr,indices in groupedEmailSeriesGroupItems if len(indices) > 1}

output2aList = []
output2bList = []
for emAddr, foundCells in emsAndTheirCellsFound.items():
    seenFirstNamesAndTheirContacts = {}
    numCellsFound = len(foundCells)
    output2bFact = OrderedDict()
    if numCellsFound > 1:
        for cellLoopCounter, cell in enumerate(foundCells):
            if cell[2] not in seenFirstNamesAndTheirContacts:
                seenFirstNamesAndTheirContacts[cell[2]] = [cell[:-1]]
                if cellLoopCounter == 0:
                    output2bFact['emAddr'] = emAddr
                    output2bFact['Master'] = cell[1]
                    output2bFact['Master_FN'] = cell[2]
                    output2bFact['Master_LN'] = cell[3]
                else:
                    uniqueFirstNamesSeenCountMinus1Str = str(len(seenFirstNamesAndTheirContacts) - 1)
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)] = cell[1]
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)+'_FN'] = cell[2]
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)+'_LN'] = cell[3]
            elif cell[:-1] not in seenFirstNamesAndTheirContacts[cell[2]]:
                seenFirstNamesAndTheirContacts[cell[2]].append(cell[:-1])
            if cellLoopCounter == numCellsFound-1:
                if 'Duplicate_1' in output2bFact:
                    output2bList.append(output2bFact)
                for firstName, contactsForFN in seenFirstNamesAndTheirContacts.items():
                    output2aFact = OrderedDict()
                    if len(contactsForFN) > 1:
                        for contactLoopCounter, contact in enumerate(contactsForFN):
                            if contactLoopCounter == 0:
                                output2aFact['Master'] = contact[1]
                            else:
                                output2aFact['Duplicate_'+str(contactLoopCounter)]=contact[1]
                    if 'Duplicate_1' in output2aFact:
                        output2aList.append(output2aFact)

if len(output2aList) > 0:
    output2adf = pandas.DataFrame(output2aList)
    output2adf.to_csv(output2aFilePath, index=False, quoting=1)
else:
    with open(output2aNoneFoundFilePath, 'w') as f:
        f.writelines('No records found')
    
if len(output2bList) > 0:
    output2bdf = pandas.DataFrame(output2bList)
    output2bdf.to_csv(output2bFilePath, index=False, quoting=1)
else:
    with open(output2bNoneFoundFilePath, 'w') as f:
        f.writelines('No records found')

print('Done running script. Took ' + str(round(time.time()-start_time,2)) + ' seconds.')
```

### Code to produce all 3 output files at once

```
import time
start_time = time.time()
from collections import OrderedDict
import pandas
pandas.set_option('expand_frame_repr', False)

emFieldNames = ['PersonalEmail','WorkEmail','AlternateEmail'] # Edit this before running
pickToField = {'Personal':'PersonalEmail','Work':'WorkEmail','Alternate':'AlternateEmail'} # Edit this before running
inputFilePath = r'c:\example\contacts.csv' # Edit this before running
output1FilePath = r'c:\example\output1.csv' # Edit this before running
output1NoneFoundFilePath = r'c:\example\nonefound1.txt' # Edit this before running
output2aFilePath = r'c:\example\output2a.csv' # Edit this before running
output2aNoneFoundFilePath = r'c:\example\nonefound2a.txt' # Edit this before running
output2bFilePath = r'c:\example\output2b.csv' # Edit this before running
output2bNoneFoundFilePath = r'c:\example\nonefound2b.txt' # Edit this before running

df = pandas.read_csv(inputFilePath, dtype=object)

# ### Output1 portion starts here ###
df['ChangedAnything'] = False

validPrefEmailIndicatorTFSeries = df['PreferredEmail'].map(pickToField).isin(df.columns)
df.loc[validPrefEmailIndicatorTFSeries,'PrefEmAddr'] = df.lookup(validPrefEmailIndicatorTFSeries.index, df[validPrefEmailIndicatorTFSeries]['PreferredEmail'].map(pickToField))

for fieldName in emFieldNames:
    needsClearingTFSer = (df['PreferredEmail'].map(pickToField) != fieldName) & (df[fieldName] == df['PrefEmAddr'])
    df.loc[needsClearingTFSer,fieldName] = None
    df.loc[needsClearingTFSer,'ChangedAnything'] = True

output1df = df[df['ChangedAnything']]

output1df = output1df.drop(columns=['PrefEmAddr','ChangedAnything'])

if len(output1df) > 0:
    output1df.to_csv(output1FilePath, index=False, quoting=1)
else:
    with open(output1NoneFoundFilePath, 'w') as f:
        f.writelines('No records found')
# ### Output1 portion ends here ###

# ### Output2 portion starts here ###
df = df.sort_values('Human-Readable ID')
df = df.reset_index(drop=True)
df = df.set_index(['Human-Readable ID','SalesforceID','FirstName','LastName','PreferredEmail'])

allEmailsSeries = df[emFieldNames].stack()

groupedEmailSeriesGroupItems = allEmailsSeries.groupby(allEmailsSeries).groups.items()

emsAndTheirCellsFound = {emaddr:([x for x in indices]) for emaddr,indices in groupedEmailSeriesGroupItems if len(indices) > 1}

output2aList = []
output2bList = []
for emAddr, foundCells in emsAndTheirCellsFound.items():
    seenFirstNamesAndTheirContacts = {}
    numCellsFound = len(foundCells)
    output2bFact = OrderedDict()
    if numCellsFound > 1:
        for cellLoopCounter, cell in enumerate(foundCells):
            if cell[2] not in seenFirstNamesAndTheirContacts:
                seenFirstNamesAndTheirContacts[cell[2]] = [cell[:-1]]
                if cellLoopCounter == 0:
                    output2bFact['emAddr'] = emAddr
                    output2bFact['Master'] = cell[1]
                    output2bFact['Master_FN'] = cell[2]
                    output2bFact['Master_LN'] = cell[3]
                else:
                    uniqueFirstNamesSeenCountMinus1Str = str(len(seenFirstNamesAndTheirContacts) - 1)
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)] = cell[1]
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)+'_FN'] = cell[2]
                    output2bFact['Duplicate_'+str(uniqueFirstNamesSeenCountMinus1Str)+'_LN'] = cell[3]
            elif cell[:-1] not in seenFirstNamesAndTheirContacts[cell[2]]:
                seenFirstNamesAndTheirContacts[cell[2]].append(cell[:-1])
            if cellLoopCounter == numCellsFound-1:
                if 'Duplicate_1' in output2bFact:
                    output2bList.append(output2bFact)
                for firstName, contactsForFN in seenFirstNamesAndTheirContacts.items():
                    output2aFact = OrderedDict()
                    if len(contactsForFN) > 1:
                        for contactLoopCounter, contact in enumerate(contactsForFN):
                            if contactLoopCounter == 0:
                                output2aFact['Master'] = contact[1]
                            else:
                                output2aFact['Duplicate_'+str(contactLoopCounter)]=contact[1]
                    if 'Duplicate_1' in output2aFact:
                        output2aList.append(output2aFact)

if len(output2aList) > 0:
    output2adf = pandas.DataFrame(output2aList)
    output2adf.to_csv(output2aFilePath, index=False, quoting=1)
else:
    with open(output2aNoneFoundFilePath, 'w') as f:
        f.writelines('No records found')
    
if len(output2bList) > 0:
    output2bdf = pandas.DataFrame(output2bList)
    output2bdf.to_csv(output2bFilePath, index=False, quoting=1)
else:
    with open(output2bNoneFoundFilePath, 'w') as f:
        f.writelines('No records found')
# ### Output2 portion ends here ###

print('Done running script. Took ' + str(round(time.time()-start_time,2)) + ' seconds.')
```

* * *

## Stop reading here if you don’t want to learn Python

You don’t have to thoroughly understand the code above to make use of it.

Just be sure that your “output” files are different filenames than your “input” file and you can’t do too much damage.

Worst-case scenario, you’ll freeze your computer running the program against too large of a file – but several million rows is **nothing** _(a minute or few)_, so get up and stretch. It’s good for you!

For the curious, the rest of this post is total geekery peeking under the hood.

* * *

![](https://www.publicdomainpictures.net/pictures/60000/velka/woman-teacher-cartoon.jpg)

## Code Explanations

I'm struggling to get all the formatting to show up correctly on DEV, so check out my breakdown at [the original blog post](https://katiekodes.com/salesforce-python-qa-001-answer/#code-explanations) and come back here with questions, comments, or corrections.  I look forward to hearing from you!