match names in csv file to filename in folder
<p>I have got a list of about 7000 names in a csv file that is arranged by surname, name, date of birth etc.  I also have a folder of about 7000+ scanned documents (enrolment forms) which have the name of each person as a filename. </p>

<p>Now the filenames may not exactly match the names in the csv ie. John Doe in the csv, filename will be John-Michael Doe etc..</p>

<p>How would I go abouts writing a program that looks through the csv and see what filenames are missing in the scanned folder?</p>

<p>I am a complete novice in programming and any advice is appreciated. </p>

<p>The first thing you want to do is read the CSV into memory. You can do this with the <a href="https://docs.python.org/3/library/csv.html" rel="nofollow"><code>csv</code> module</a>. The most useful tool there is <code>csv.DictReader</code>, which takes the first line of the file as keys in a dictionary, and reads the remainder:</p>

<pre><code>import csv
with open('/path/to/yourfile.csv', 'r') as f:
    rows = list(csv.DictReader(f))

from pprint import pprint
pprint(rows[:100])
</code></pre>

<p>In windows, the path would look different, and would be something like <code>c:/some folder/some other folder/</code> (note the forward-slashes instead of backslashes).</p>

<p>This will show the first 100 rows from the file. For example if you have columns named "First Name", "Last Name", "Date of Birth", this will look like:</p>

<pre><code>[{'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'},
 {'Date of Birth': 'Jan 1, 1970', 'First Name': 'John', 'Last Name': 'Doe'}
 ...]
</code></pre>

<p>Next you want to get a list of all the 7000 files, using <code>os.listdir</code>:</p>

<pre><code>import os
images_directory = '/path/to/images/'
image_paths = [
    os.path.join(images_directory, filename)
    for filename in os.listdir(images_directory)]
</code></pre>

<p>Now you'll need some way to extract the names from the files. This depends crucially on the way the files are structured. The tricky-to-use but very powerful tool for this task is called a <em>regular expression</em>, but probably something simple will suffice. For example, if the files are named like "first-name last-name.pdf", you could write a simple parsing method like:</p>

<pre><code>def parse_filename(filename):
    name, extension = filename.split('.')
    first_name, last_name = name.split(' ')
    return first_name.replace('-', ' '), last_name.replace('-', ' ')
</code></pre>

<p>The exact implementation will depend on how the files are named, but the key things to get you started are <a href="https://docs.python.org/3/library/stdtypes.html#str.split" rel="nofollow"><code>str.split</code></a>, <a href="https://docs.python.org/3/library/stdtypes.html#str.strip" rel="nofollow"><code>str.strip</code></a> and a few others in that same class. You might also take a look at the <a href="https://docs.python.org/3/library/re.html" rel="nofollow"><code>re</code> module for handling regular expressions</a>. As I said, that's a more advanced/powerful technique, so it may not be worth worrying about right now.</p>

<p>A simple algorithm to do the matching would be something like the following:</p>

<pre><code>name_to_filename = {parse_filename(filename.lower()): filename for filename in filenames}
matched_rows = []
unmatched_files = []
for row in rows:
    name_key = (row['First Name'].lower(), row['Last Name'].lower())
    matching_file = name_to_filename.get(name_key)  # This sees if we have a matching file name, and returns
                                                    # None otherwise.
    new_row = row.copy()
    if matching_file:
        new_row['File'] = matching_file
        print('Matched "%s" to %s' % (' '.join(name_key), matching_file))
    else:
        new_row['File'] = ''
        print('No match for "%s"' % (' '.join(name_key)))
    matched_rows.append(new_row)
with open('/path/to/output.csv', 'w') as f:
    writer = csv.DictWriter(f, ['First Name', 'Last Name', 'Date of Birth', 'File])
    writer.writeheader()
    writer.writerows(matched_rows)
</code></pre>

<p>This should give you an output spreadsheet with whatever rows you could match automatically matched up, and the remaining ones blank. Depending on how clean your data is, you might be able to just match the remaining few entries by hand. There's only 7000, and the "dumb" heuristic will probably catch most of them. If you need more advanced heuristics, you might look at <a href="https://en.wikipedia.org/wiki/Jaccard_index" rel="nofollow">Jaccard similarity</a> of the "words" in the name, and the <a href="https://docs.python.org/3/library/difflib.html" rel="nofollow">difflib</a> module for approximate string matching.</p>

<p>Of course most of this code won't <em>quite</em> work on your problem, but hopefully it's enough to get you started.</p>

python