Locating contact information from a CSV spreadsheet

Question

I'm trying to find a more effective way to access variables within for loops. I felt that my attempt which works is not an effective way to access variables that are within for loops.

mylist=None is the variable in which I want to access within the for loop.

Also, I want to find a way to minimize the lines of my code either using functions or other suggested approaches and etc.

import csv
import os
from var_dump import var_dump
from orderedset import OrderedSet




# if phone or websites dont appear on spreadsheet change the row indexs

fil = 'newfiles/output8.csv'
with open(fil) as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    websites = OrderedSet()
    phonenumbers = OrderedSet()
    emails = OrderedSet()
    locations = list()
    mylist = None
    data = ["electric", "electricians"]
    # remove urls that have these strings,  only works with the first string
    # but not second one.
    remo = ["listings", "nationwide", "/mip", "car", "/locations", "/YB", "?utm_source", "/locallocksmithnearby",
            "key.me", "find-locksmiths-near-me", "premium-locksmiths", "locksmithnearyou24hrs"]
    for row in readCSV:
        website = row[1].strip('[]\'')
        email = row[0].lower().strip('[]\'')
        phonenumber = row[2]
        location = row[3]
        if location:
            if website not in websites:
                if phonenumber not in phonenumbers:
                    if email not in emails:
                        for x in data:
                            if x in website:
                                for r in remo:
                                    if all(r not in website for r in remo):
                                        if phonenumber not in phonenumbers:
                                            if email not in emails:
                                                websites.add(website)
                                                phonenumbers.add(phonenumber)
                                                emails.add(email)
                                                locations.append(location)
                                                mylist = zip(emails, websites, phonenumbers, locations)


    if mylist is not None:
        number = 0
        while os.path.exists('newfiles/newoutput%s.csv' % number):
            number += 1
        with open('newfiles/newoutput%s.csv' % number, 'w') as csvfile:
            writer = csv.writer(
                csvfile, delimiter=",")
            writer.writerow(
                ['Email', 'Website', 'Phone Number', 'Location'])
            # var_dump(mylist)
            for i in mylist:
                writer.writerow(list(i))

Answer 1

You would do well to use generator functions to break this down into parts that might be re-usable for other steps in your pipeline.

For example, the first thing you do in your loop is read a row from the CSV file and process some of the fields. That's a good candidate, right there:

def normalized_csv_rows(reader):
    """Filter CSV data, cleaning up email & web fields.        """
    for row in reader:
        email, web, phone, locn = row
        email = email.lower().strip("[]'")
        web = web.strip("[]'")
        yield email, web, phone, locn

The next obvious thing I see is that you require three of your fields to be unique. That's another good candidate for a filtering generator:

def require_unique_field(num, reader):
    seen = set()
    for row in reader:
        field = row[num]
        if field not in seen:
            seen.add(field)
            yield row

You can stack these up to require several unique fields. Finally, you require the location field to be "truthy", which generally means non-empty. So let's go with that:

def require_nonempty_field(num, reader):
    for row in reader:
        if row[num]:
            yield row

With these tools in hand, let's look at your for loop:

for row in readCSV:
    website = row[1].strip('[]\'')
    email = row[0].lower().strip('[]\'')
    phonenumber = row[2]
    location = row[3]
    if location:
        if website not in websites:
            if phonenumber not in phonenumbers:
                if email not in emails:
                    for x in data:
                        if x in website:
                            for r in remo:
                                if all(r not in website for r in remo):
                                    if phonenumber not in phonenumbers:
                                        if email not in emails:
                                            websites.add(website)
                                            phonenumbers.add(phonenumber)
                                            emails.add(email)
                                            locations.append(location)
                                            mylist = zip(emails, websites, phonenumbers, locations)

The first few lines are taken care of by our normalization function:

reader = readCSV
reader = normalized_csv_rows(reader)

The if location: is our require_nonempty_field function, with a field number of 3:

reader = require_nonempty_field(3, reader)

The lines like if email not in emails: (which occurs twice - I hope somebody was drunk for St. Patty's day) can be replaced with our require_unique_field function:

reader = require_unique_field(0, reader)
reader = require_unique_field(1, reader)
reader = require_unique_field(2, reader)

So we can rewrite the top part like this:

EMAIL, WEBSITE, PHONE, LOCATION = 0,1,2,3

data = ["electric", "electricians"]
# remove urls that have these strings,  only works with the first string
# but not second one.
remo = ["listings", "nationwide", "/mip", "car", "/locations", "/YB", "?utm_source", "/locallocksmithnearby",
        "key.me", "find-locksmiths-near-me", "premium-locksmiths", "locksmithnearyou24hrs"]
fil = 'newfiles/output8.csv'

with open(fil) as csvfile:
    reader = csv.reader(csvfile, delimiter=",")
    reader = normalized_csv_rows(reader)
    reader = require_nonempty_field(LOCATION, reader)
    reader = require_unique_field(EMAIL, reader)
    reader = require_unique_field(WEBSITE, reader)
    reader = require_unique_field(PHONE, reader)

Now, however, we come to the tricky part. You have a list of "must contain" items, data. But that list includes one word that is a prefix of another word.

If electricians is part of website, then electric is also part of website, since electric is a substring of electricians. The result is that this will tend to cause those rows containing electricians to be emitted twice. I suspect this is why you have two copies of the if email in emails: conditionals included in the stack.

A better idea would be to pre-process the filter words, to eliminate the longer word:

data.sort(key=len)
data2 = []
for word in data:
    for prefix = data2:
        if word.startswith(prefix):
            break
    else:
        data2.append(word)
data = data2
del data2

Once we have a "clean" list, you can write another generator function. Better still, we can make the cleanup part of the generator function, to make sure the user doesn't give us bad data:

def field_contains(num, strings, reader):
    """Read rows from reader and pass those which have any member of
    strings as a substring of field num.

    """
    strings_by_len = sorted(strings, key=len)
    substrings = []
    for word in strings_by_len:
        for prefix in substrings:
            if word.startswith(prefix):
                break
        else:
            substrings.append(word)

    for row in reader:
        field = row[num]
        if any(s in field for s in substrings):
            yield row

And you can add that to the constraints on the reader:

reader = field_contains(WEBSITE, data, reader)

Similarly, you have a "blocking" list, where some substrings must not be in the website field. This is a similar function to the last, except you want to yield the row when the condition fails:

if not any(s in field for s in substrings):
    yield row

Then add that to the constraints:

reader = field_does_not_contain(WEBSITE, remo, reader)

At this point, reader is a stack of generators that will only yield the rows you want. However, not a single one of them has been read! So you can just pass this in to your output code:

writer.writerows(reader)

Please note, however, that it's important to keep this nested below the with open(fil) statement, since none of the data has been read in! The structure should look like:

with open(fil) ...:
    reader = ...
    with open(whatever) as outputcsv:
        writer = ...
        writer.writerows(reader)