AddressList | Python Fiddle

### Import relevant modules.

import arcpy

import sys
import os
import csv
import datetime
import time
import subprocess

### Define toolbox.

class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Address Tools"
        self.alias = "address"

# List of tool classes associated with this toolbox
        self.tools = [AddressExtract]

### Define module constants.

# Set to True or False to change hard-coded paths of feature classes
# and output (to local or server locations).
SERVER_RUN = False

# Get two most recent years for identifying recently sold properties.
CUR_YEARS = datetime.datetime.now().year
CUR_YEARS = {CUR_YEARS, CUR_YEARS - 1}

### Define tools.

class AddressExtract(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Address Extract"
        self.description = ("Creates a list of addresses and carrier routes "
                            "based on a feature set or neighborhood "
                            "selection.")
        self.canRunInBackground = False

def getParameterInfo(self):
        """Define parameter definitions"""

# Define parameters.

# Input Feature Set.
        param0 = arcpy.Parameter(
            displayName="Input Feature Set",
            name="in_feat_set",
            datatype="GPFeatureRecordSetLayer",
            parameterType="Optional",
            direction="Input"
            )

# Alternatively, Input Neighborhood(s).
        param1 = arcpy.Parameter(
            displayName=("Alternatively, Input Neighborhood(s) "
                         "(Will Ignore Any Drawn Shapes)"),
            name="nbrhoods",
            datatype="GPString",
            parameterType="Optional",
            direction="Input",
            multiValue=True
            )

# Input Buffer Distance.
        param2 = arcpy.Parameter(
            displayName="Buffer Distance (Feet)",
            name="buff_dist",
            datatype="GPDouble",
            parameterType="Optional",
            direction="Input"
            )
            
        # Include Additional Address Information.
        param3 = arcpy.Parameter(
            displayName=("Include Taxpayer Name and Address (plus Buyer Name "
                         "for Properties Sold in {0})".format(
                             "-".join(sorted(str(i) for i in CUR_YEARS))
                             )),
            name="addl_addr",
            datatype="GPBoolean",
            parameterType="Optional",
            direction="Input"
            )

# Output Address List.
        param4 = arcpy.Parameter(
            displayName="Output Address List (.csv Table)",
            name="out_addr",
            datatype="DEFile",
            parameterType="Required",
            direction="Output"
            )
        
        # Output Carrier Route List.
        param5 = arcpy.Parameter(
            displayName="Output Carrier Route List (.csv Table)",
            name="out_rout",
            datatype="DEFile",
            parameterType="Required",
            direction="Output"
            )

# Output Feature Class.
        param6 = arcpy.Parameter(
            displayName="Output Feature Class",
            name="out_feat",
            datatype="DEFeatureClass",
            parameterType="Derived",
            direction="Output"
            )

# Set other parameter properties.

# Parameter Dependencies
        param6.parameterDependencies = [param1.name]
        param6.schema.clone = True

# Filters
        
        # Filter list for the 14 Shoreline neighborhoods.
        nbrhoods = [
            "Ballinger",
            "Briarcrest",
            "Echo Lake",
            "Highland Terrace",
            "Hillwood",
            "Innis Arden",
            "Meridian Park",
            "North City",
            "Parkwood",
            "Richmond Beach",
            "Richmond Highlands",
            "Ridgecrest",
            "The Highlands",
            "Westminster Triangle"
            ]
        param1.filter.list = nbrhoods
        
        param4.filter.list = ["csv"]
        param5.filter.list = ["csv"]

# Default values.
        if SERVER_RUN:
            tax_prcl = (r"Database Connections\geodata.sde\geodata.DBO.Land"
                        r"\geodata.DBO.TaxParcel")
            out_addr = r"E:\data\scratch\OutAddressList_RenameToSave.csv"
            out_rout = r"E:\data\scratch\OutAddressList_RenameToSave_CR.csv"
        else:
            tax_prcl = (r"J:\GIS\UTIL\Connection\gis-01.sde\maps.DBO.Land"
                        r"\maps.DBO.TaxParcel")
            out_addr = (r"J:\GIS\AddressLists"
                        r"\OutAddressList_RenameToSave.csv")
            out_rout = (r"J:\GIS\AddressLists"
                        r"\OutAddressList_RenameToSave_CR.csv")
            
        param0.value = tax_prcl
        param3.value = True
        param4.value = out_addr
        param5.value = out_rout

params = [param0, param1, param2, param3, param4, param5, param6]
        
        return params

def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""

out_addr   = parameters[4]
        out_rout   = parameters[5]
        
        # If Output Addresses is changed, change Output Carrier Routes
        # to the same file path, suffixed with "_CR", unless it has
        # already been manually altered.
        if not (out_rout.altered or out_addr.hasBeenValidated):
            out_rout.value = (os.path.splitext(out_addr.valueAsText)[0] +
                              "_CR.csv")
        
        return

def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""

in_feat_set = parameters[0]
        nbrhoods    = parameters[1]

# Require at least one of the input parameters, the feature set
        # or a neighborhood selection.
        if not (in_feat_set.value or nbrhoods.value):
            error_msg = ("At least one of the input parameters, the feature "
                         "set or a neighborhood selection, is required in "
                         "order for the tool to run.")
            in_feat_set.setErrorMessage(error_msg)
            nbrhoods.setErrorMessage(error_msg)
            
        return

def execute(self, parameters, messages):
        """The source code of the tool."""

### Start the script.

messages.addMessage("\n{0:*^33}\n".format(" Script initiated. "))

### Define parameters.

# Import parameters from tool dialog box.
        in_feat_set = parameters[0].value
        nbrhoods    = parameters[1].valueAsText
        buff_dist   = parameters[2].valueAsText
        addl_addr   = bool(parameters[3].value)
        out_addr    = parameters[4].valueAsText
        out_rout    = parameters[5].valueAsText
        out_feat    = parameters[6].value

# Convert float parameters to true floats.
        if buff_dist:
            buff_dist = float(buff_dist)
        else:
            buff_dist = None

# Convert list parameters to true lists.
        if nbrhoods:
            # ArcGIS uses semicolon delimiters for lists as text.
            nbrhoods = nbrhoods.split(";")

# Remove any single quotes that were inserted automatically.
            nbrhoods = [
                i[1:-1] if i[0] == i[-1] == "'" else i for i in nbrhoods
                ]
        else:
            nbrhoods = None

# Set environment variables.
        arcpy.env.overwriteOutput = True

# Set file paths.
        if SERVER_RUN:
            # Server file paths.
            tax_prcl = (r"Database Connections\geodata.sde\geodata.DBO.Land"
                        r"\geodata.DBO.TaxParcel")
            nbrhd_fc = (r"Database Connections\geodata.sde\geodata.DBO.Land"
                        r"\geodata.DBO.Neighborhood")
            sit_addr = (r"Database Connections\geodata.sde\geodata.DBO.Address"
                        r"\geodata.DBO.SiteAddress")
            car_rout = (r"Database Connections\geodata.sde\geodata.DBO.Address"
                        r"\geodata.DBO.CarrierRoutes")
            realprop = (r"Database Connections\geodata.sde"
                        r"\geodata.dbo.ASSESS_REALPROPERTY")
            sales    = (r"Database Connections\geodata.sde"
                        r"\geodata.dbo.ASSESS_SALES")
        else:
            # Local file paths.
            tax_prcl = (r"J:\GIS\UTIL\Connection\gis-01.sde\maps.DBO.Land"
                        r"\maps.DBO.TaxParcel")
            nbrhd_fc = (r"J:\GIS\UTIL\Connection\gis-01.sde\maps.DBO.Land"
                        r"\maps.DBO.Neighborhood")
            sit_addr = (r"J:\GIS\UTIL\Connection\gis-01.sde\maps.DBO.Address"
                        r"\maps.DBO.SiteAddress")
            car_rout = (r"J:\GIS\UTIL\Connection\gis-01.sde\maps.DBO.Address"
                        r"\maps.DBO.CarrierRoutes")
            realprop = (r"J:\GIS\UTIL\Connection\gis-01.sde"
                        r"\maps.dbo.ASSESS_REALPROPERTY")
            sales    = (r"J:\GIS\UTIL\Connection\gis-01.sde"
                        r"\maps.dbo.ASSESS_SALES")

### Make feature layers from the feature set and classes.

label = "Creating feature layers..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

if in_feat_set:
            messages.addMessage("\tInput Feature Set")
            arcpy.SetProgressorLabel(label + "\n\tInput Feature Set")
            arcpy.MakeFeatureLayer_management(in_feat_set, "f_set_layer")

if nbrhoods:
            messages.addMessage("\tNeighborhood")
            arcpy.SetProgressorLabel(label + "\n\tNeighborhood")
            arcpy.MakeFeatureLayer_management(nbrhd_fc, "nbrhood_layer")

messages.addMessage("\tTaxParcel")
        arcpy.SetProgressorLabel(label + "\n\tTaxParcel")
        arcpy.MakeFeatureLayer_management(tax_prcl, "parcel_layer")

messages.addMessage("\tSiteAddress")
        arcpy.SetProgressorLabel(label + "\n\tSiteAddress")
        arcpy.MakeFeatureLayer_management(sit_addr, "addr_layer")

messages.addMessage("\tCarrierRoutes")
        arcpy.SetProgressorLabel(label + "\n\tCarrierRoutes")
        arcpy.MakeFeatureLayer_management(car_rout, "route_layer")

messages.addMessage("\tReal Property Table")
        arcpy.SetProgressorLabel(label + "\n\tReal Property Table")
        arcpy.MakeTableView_management(realprop, "realprop_table")

messages.addMessage("\tSales Table")
        arcpy.SetProgressorLabel(label + "\n\tSales Table")
        arcpy.MakeTableView_management(sales, "sales_table")

# Set SQL expressions for certain layer selections.

# Neighborhood selection. Convert all names to uppercase in case
        # this changes in the feature class.
        if nbrhoods:
            sql_exp_nbrhd = "UPPER(NAME) IN ('{0}')".format(
                "', '".join(i.upper() for i in nbrhoods)
                )

# Mailable address selection.
        sql_exp_mail = (

# Newer addresses that meet a less stringent validation.
            "((created_date between dateadd(day, -700, GETDATE()) and "
            "GETDATE()) and ADD_TYPE = 'A') or "

# Older addresses that meet a more stringent validation.
            "(len(DPB) > 1 and len(CRRT) > 1 and ADD_TYPE = 'A' and "
            "(DUP_ERROR <> 'D' or DUP_ERROR IS NULL))"
            )

### Select tax parcels based on the feature set or
        ### neighborhood(s).

label = "Selecting parcels..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

if nbrhoods:
            # Use neighborhood(s), if defined, instead of feature set.
            messages.addMessage("\tUsing neighborhood(s):")
            for i in nbrhoods:
                messages.addMessage("\t\t" + i)

# Make SQL expression for neighborhoods.
            sql_exp = sql_exp_nbrhd

# Select neighborhood(s).
            messages.addMessage("\tSelecting neighborhood(s)...")
            arcpy.SelectLayerByAttribute_management("nbrhood_layer",
                                                    "NEW_SELECTION",
                                                    sql_exp)
            
            if buff_dist:
                # Invoke a buffer distance, if defined.
                messages.addMessage("\tBuffer distance: {0} feet".
                                    format(buff_dist))

# Return the buffered nbrhood as the output fc.
                temp_fc = r"in_memory\nbrhood_buffer"
##                temp_fc = (r"J:\GIS\users\JRitzman\Python\Custom Toolboxes"
##                           r"\scratch.gdb\address_nbrhood_buffer")
                arcpy.Buffer_analysis("nbrhood_layer", temp_fc, buff_dist)
                arcpy.SetParameter(6, temp_fc)
                
                arcpy.SelectLayerByLocation_management("parcel_layer",
                                                       "INTERSECT",
                                                       "nbrhood_layer",
                                                       buff_dist,
                                                       "NEW_SELECTION")
                parcels = int(arcpy.GetCount_management("parcel_layer").
                              getOutput(0))
                messages.addMessage("\t{0} parcels".format(parcels))
            else:
                messages.addMessage("\tBuffer distance: 0 feet")

# Return the neighborhood(s) as the output fc.
                temp_fc = r"in_memory\nbrhood_copy"
##                temp_fc = (r"J:\GIS\users\JRitzman\Python\Custom Toolboxes"
##                           r"\scratch.gdb\address_nbrhood_copy")
                arcpy.CopyFeatures_management("nbrhood_layer", temp_fc)
                arcpy.SetParameter(6, temp_fc)

arcpy.SelectLayerByLocation_management("parcel_layer",
                                                       "WITHIN",
                                                       "nbrhood_layer", None,
                                                       "NEW_SELECTION")
                arcpy.SelectLayerByLocation_management(
                    "parcel_layer", "CROSSED_BY_THE_OUTLINE_OF",
                    "nbrhood_layer",  None, "ADD_TO_SELECTION"
                    )
                parcels = int(arcpy.GetCount_management("parcel_layer").
                              getOutput(0))
                messages.addMessage("\t{0} parcels".format(parcels))

elif buff_dist:
            messages.addMessage("\tUsing feature set...")
            
            # Invoke a buffer distance, if defined.
            messages.addMessage("\tBuffer distance: {0} feet".
                                format(buff_dist))

# Return the buffered feature set as the output fc.
            temp_fc = r"in_memory\f_set_buffer"
##            temp_fc = (r"J:\GIS\users\JRitzman\Python\Custom Toolboxes"
##                       r"\scratch.gdb\address_f_set_buffer")
            arcpy.Buffer_analysis("f_set_layer", temp_fc, buff_dist)
            arcpy.SetParameter(6, temp_fc)
            
            arcpy.SelectLayerByLocation_management("parcel_layer", "INTERSECT",
                                                   "f_set_layer", buff_dist,
                                                   "NEW_SELECTION")
            parcels = int(arcpy.GetCount_management("parcel_layer").
                          getOutput(0))
            messages.addMessage("\t{0} parcels".format(parcels))
        else:
            messages.addMessage("\tUsing feature set...")
            
            # Return the feature set features as the output feature set.
            temp_fc = r"in_memory\f_set_copy"
##            temp_fc = (r"J:\GIS\users\JRitzman\Python\Custom Toolboxes"
##                       r"\scratch.gdb\address_f_set_copy")
            arcpy.CopyFeatures_management("f_set_layer", temp_fc)
            arcpy.SetParameter(6, temp_fc)

# Attempt to find an exact match between the feature set and
            # the parcel layer in order to avoid expanding a selection
            # that is already in the form of tax parcels if no buffer is
            # defined.
            messages.addMessage("\tBuffer distance: 0 feet")
            arcpy.SelectLayerByLocation_management("parcel_layer",
                                                   "ARE_IDENTICAL_TO",
                                                   "f_set_layer", None,
                                                   "NEW_SELECTION")
            f_set = int(arcpy.GetCount_management("f_set_layer").getOutput(0))
            exact = int(arcpy.GetCount_management("parcel_layer").getOutput(0))
            messages.addMessage("\t{0} feature set features".format(f_set))
            messages.addMessage("\t{0} exact parcels".format(exact))

# If parcels are selected using this method, use them.
            if not exact:
                # If no parcels are selected using this method, then a
                # more general feature set geometry is assumed and an
                # intersect is done between the feature set and the
                # parcel layer instead.
                arcpy.SelectLayerByLocation_management("parcel_layer",
                                                       "WITHIN",
                                                       "f_set_layer", None,
                                                       "NEW_SELECTION")
                arcpy.SelectLayerByLocation_management(
                    "parcel_layer", "CROSSED_BY_THE_OUTLINE_OF",
                    "f_set_layer",  None, "ADD_TO_SELECTION"
                    )
                parcels = int(arcpy.GetCount_management("parcel_layer").
                              getOutput(0))
                messages.addMessage("\t{0} parcels".format(parcels))
            else:
                # If less exact parcels than feature set features are
                # selected, then feature set features not corresponding
                # to exact parcels are back-selected and implemented
                # using an intersect to add to the parcel selection.
                # If all feature set features select an exact parcel,
                # then this section will add no new selected parcel
                # features.
                
                # Select feature set features corresponding to exact
                # parcels.
                arcpy.SelectLayerByLocation_management("f_set_layer",
                                                       "ARE_IDENTICAL_TO",
                                                       "parcel_layer", None,
                                                       "NEW_SELECTION")

# Switch the selection to select feature set features
                # not corresponding to exact parcels.
                arcpy.SelectLayerByLocation_management("f_set_layer",
                                                       None, None, None,
                                                       "SWITCH_SELECTION")

# Intersect the remaining feature set features with the
                # parcel layer and add to the first selection of exact
                # parcels.
                arcpy.SelectLayerByLocation_management("parcel_layer",
                                                       "WITHIN",
                                                       "f_set_layer", None,
                                                       "ADD_TO_SELECTION")
                arcpy.SelectLayerByLocation_management(
                    "parcel_layer", "CROSSED_BY_THE_OUTLINE_OF",
                    "f_set_layer",  None, "ADD_TO_SELECTION"
                    )
                addl = int(arcpy.GetCount_management("parcel_layer").
                           getOutput(0)) - exact
                messages.addMessage("\t{0} additional parcels".format(addl))
                
                
        ### Select site addresses based on the selected parcels.

label = "Selecting addresses..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

arcpy.SelectLayerByLocation_management("addr_layer", "INTERSECT",
                                               "parcel_layer", None,
                                               "NEW_SELECTION")

# Clear the tax parcel selection for later (to not restrain the
        # search cursor in case of distant TaxPIN match).
        arcpy.SelectLayerByAttribute_management("parcel_layer",
                                                "CLEAR_SELECTION")

# State the number of site addresses selected.
        num_addr = int(arcpy.GetCount_management("addr_layer").getOutput(0))
        messages.addMessage("\t{0} addresses".format(num_addr))

### Select only mailable site addresses.

label = "Selecting mailable addresses..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

# Print SQL query used for mailable addresses.
        messages.addMessage("\t----\nSQL:\n{0}\n\t----".format(sql_exp_mail))

sql_exp = sql_exp_mail
        arcpy.SelectLayerByAttribute_management("addr_layer",
                                                "SUBSET_SELECTION",
                                                sql_exp)

# State the number of mailable addresses selected.
        mail_addr = int(arcpy.GetCount_management("addr_layer").getOutput(0))
        messages.addMessage("\t{0} mailable addresses".format(mail_addr))
        
        # Exit gracefully if no mailable site addresses were selected.
        if not mail_addr:
            err_msg = ("No mailable site addresses were selected using "
                       "current parameters.")
            messages.addErrorMessage("\t" + err_msg)
            raise arcpy.ExecuteError(err_msg)

### Select carrier routes.

label = "Selecting carrier routes..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

# Select carrier routes based on mailable site addresses.
        arcpy.SelectLayerByLocation_management("route_layer", "INTERSECT",
                                               "addr_layer", None,
                                               "NEW_SELECTION")
            
        # State the number of carrier routes selected.
        car_rtes = int(arcpy.GetCount_management("route_layer").getOutput(0))
        messages.addMessage("\t{0} carrier routes".format(car_rtes))

### Parse address and carrier route data from selections.

label = "Parsing data..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

# Mailable site addresses.
        
        messages.addMessage("\tMailable addresses")
        arcpy.SetProgressorLabel(label + "\n\tMailable addresses")
        
        # Parse data using Data Access Search Cursor.
        messages.addMessage("\t\tParsing selection...")        
        addr_list = []
        with arcpy.da.SearchCursor("addr_layer",
                                   ["PIN", "RP", "ADDRESS", "ZIP"]
                                   ) as cursor:
            for row in cursor:
                # Pass values through text cleaning, numbers left alone.
                # All fields here are text anyways.
                row = [i.strip() if hasattr(i, "strip") and i.strip() else
                       i if not hasattr(i, "strip") else None for i in row]
                
                # Only want addresses with intact data.
                if row[0] and row[2] and row[3]:                    
                    # Append to list as tuple (to enable set later).
                    # (TaxPIN, Condo PIN, Address, City, State, ZIP)
                    addr_list.append(tuple(row[:-1]) + ("SHORELINE", "WA") +
                                     tuple(row[-1:]))

# Remove duplicates (also, sort) by passing through a set.
        messages.addMessage("\t\tRemoving duplicates...")        
        addr_list = sorted(set(addr_list))

# Convert to list of lists (rather than list of tuples).
        messages.addMessage("\t\tConverting to lists...")
        addr_list = [list(i) for i in addr_list]

# Create TaxPIN set to match parcels.
        messages.addMessage("\t\tCreating TaxPIN list and set...")
        addr_taxpin_list = [i[0] for i in addr_list if not i[1]]
        addr_taxpin_set = set(addr_taxpin_list)

# Create Condo PIN set to match real property accounts.
        messages.addMessage("\t\tCreating Condo PIN list and set...")
        addr_rp_list = [i[1] for i in addr_list if i[1]]
        addr_rp_set = set(addr_rp_list)

# Taxpayer names and addresses and buyer names.
        
        if addl_addr:
            messages.addMessage("\tTaxpayers and buyers")
            arcpy.SetProgressorLabel(label + "\n\tTaxpayers and buyers")

# Only include buyer names for sales in the most recent two
            # years.
            CUR_YEARS = datetime.datetime.now().year
            CUR_YEARS = {CUR_YEARS, CUR_YEARS - 1}
        
            # Parse parcels using Data Access Search Cursor.
            messages.addMessage("\t\tParsing parcels...")
            addl_list = []
            addl_fields = [
                "TAXPIN", "TAXPAYER", "TAXPAYERADDRESS", "TAXPAYERCITY",
                "TAXPAYERSTATE", "TAXPAYERZIP", "BUYER", "SALEDATE"
                ]
            with arcpy.da.SearchCursor("parcel_layer", addl_fields) as cursor:
                for row in cursor:
                    # Clean text data and separate date.
                    row = [i.strip() if hasattr(i, "strip") and i.strip() else
                           i if not hasattr(i, "strip") else None for i in row]
                    date = row[-1]

# Only want parcels that match address list TaxPINs.
                    taxpin = row[0]
                    if taxpin in addr_taxpin_set:                        
                        # ArcGIS date fields give date objects, so
                        # compare the year to the most recent two years.
                        if date and (date.year in CUR_YEARS):
                            pass
                        else:
                            row[-2:] = [None, None]

# Append to list.
                        addl_list.append(row)

# Sort additional list and create lists for matching.
            addl_list.sort()
            addl_taxpin_list = [i[0] for i in addl_list]
            addl_taxpin_set = set(addl_taxpin_list)

# Define sets of state abbreviations and names to help split
            # the CityState field into the City and State fields.
            us_state_terr_abbr = {"AL", "AK", "AZ", "AR", "CA", "CO", "CT",
                                  "DE", "DC", "FL", "GA", "HI", "ID", "IL",
                                  "IN", "IA", "KS", "KY", "LA", "ME", "MD",
                                  "MA", "MI", "MN", "MS", "MO", "MT", "NE",
                                  "NV", "NH", "NJ", "NM", "NY", "NC", "ND",
                                  "OH", "OK", "OR", "PA", "RI", "SC", "SD",
                                  "TN", "TX", "UT", "VT", "VA", "WA", "WV",
                                  "WI", "WY", "AS", "GU", "MP", "PR", "VI",
                                  "FM", "MH", "PW", "AA", "AE", "AP"}
            state_full = {'ALABAMA', 'ALASKA', 'ARIZONA', 'ARKANSAS',
                          'CALIFORNIA', 'COLORADO', 'CONNECTICUT', 'DELAWARE',
                          'FLORIDA', 'GEORGIA', 'HAWAII', 'IDAHO', 'ILLINOIS',
                          'INDIANA', 'IOWA', 'KANSAS', 'KENTUCKY', 'LOUISIANA',
                          'MAINE', 'MARYLAND', 'MASSACHUSETTS', 'MICHIGAN',
                          'MINNESOTA', 'MISSISSIPPI', 'MISSOURI', 'MONTANA',
                          'NEBRASKA', 'NEVADA', 'NEW HAMPSHIRE', 'NEW JERSEY',
                          'NEW MEXICO', 'NEW YORK', 'NORTH CAROLINA',
                          'NORTH DAKOTA', 'OHIO', 'OKLAHOMA', 'OREGON',
                          'PENNSYLVANIA', 'RHODE ISLAND', 'SOUTH CAROLINA',
                          'SOUTH DAKOTA', 'TENNESSEE', 'TEXAS', 'UTAH',
                          'VERMONT', 'VIRGINIA', 'WASHINGTON', 'WEST VIRGINIA',
                          'WISCONSIN', 'WYOMING', 'DISTRICT OF COLUMBIA',
                          'AMERICAN SAMOA', 'GUAM', 'NORTHERN MARIANA ISLANDS',
                          'PUERTO RICO', 'UNITED STATES VIRGIN ISLANDS'}        
            country_full = {'AFGHANISTAN', 'ALBANIA', 'ALGERIA', 'ANDORRA',
                            'ANGOLA', 'ANTIGUA AND BARBUDA', 'ARGENTINA',
                            'ARMENIA', 'ARUBA', 'AUSTRALIA', 'AUSTRIA',
                            'AZERBAIJAN', 'THE BAHAMAS', 'BAHRAIN',
                            'BANGLADESH', 'BARBADOS', 'BELARUS', 'BELGIUM',
                            'BELIZE', 'BENIN', 'BHUTAN', 'BOLIVIA',
                            'BOSNIA AND HERZEGOVINA', 'BOTSWANA', 'BRAZIL',
                            'BRUNEI', 'BULGARIA', 'BURKINA FASO', 'BURMA',
                            'BURUNDI', 'CAMBODIA', 'CAMEROON', 'CANADA',
                            'CAPE VERDE', 'CENTRAL AFRICAN REPUBLIC', 'CHAD',
                            'CHILE', 'CHINA', 'COLOMBIA', 'COMOROS',
                            'DEMOCRATIC REPUBLIC OF THE CONGO',
                            'REPUBLIC OF THE CONGO', 'COSTA RICA',
                            'COTE D\'IVOIRE', 'CROATIA', 'CUBA', 'CURACAO',
                            'CYPRUS', 'CZECH REPUBLIC', 'DENMARK', 'DJIBOUTI',
                            'DOMINICA', 'DOMINICAN REPUBLIC', 'EAST TIMOR',
                            'TIMOR-LESTE', 'ECUADOR', 'EGYPT', 'EL SALVADOR',
                            'EQUATORIAL GUINEA', 'ERITREA', 'ESTONIA',
                            'ETHIOPIA', 'FIJI', 'FINLAND', 'FRANCE', 'GABON',
                            'THE GAMBIA', 'GEORGIA', 'GERMANY', 'GHANA',
                            'GREECE', 'GRENADA', 'GUATEMALA', 'GUINEA',
                            'GUINEA-BISSAU', 'GUYANA', 'HAITI', 'HOLY SEE',
                            'HONDURAS', 'HONG KONG', 'HUNGARY', 'ICELAND',
                            'INDIA', 'INDONESIA', 'IRAN', 'IRAQ', 'IRELAND',
                            'ISRAEL', 'ITALY', 'JAMAICA', 'JAPAN', 'JORDAN',
                            'KAZAKHSTAN', 'KENYA', 'KIRIBATI', 'NORTH KOREA',
                            'SOUTH KOREA', 'KOSOVO', 'KUWAIT', 'KYRGYZSTAN',
                            'LAOS', 'LATVIA', 'LEBANON', 'LESOTHO', 'LIBERIA',
                            'LIBYA', 'LIECHTENSTEIN', 'LITHUANIA',
                            'LUXEMBOURG', 'MACAU', 'MACEDONIA', 'MADAGASCAR',
                            'MALAWI', 'MALAYSIA', 'MALDIVES', 'MALI', 'MALTA',
                            'MARSHALL ISLANDS', 'MAURITANIA', 'MAURITIUS',
                            'MEXICO', 'MICRONESIA', 'MOLDOVA', 'MONACO',
                            'MONGOLIA', 'MONTENEGRO', 'MOROCCO', 'MOZAMBIQUE',
                            'NAMIBIA', 'NAURU', 'NEPAL', 'NETHERLANDS',
                            'NETHERLANDS ANTILLES', 'NEW ZEALAND', 'NICARAGUA',
                            'NIGER', 'NIGERIA', 'NORTH KOREA', 'NORWAY',
                            'OMAN', 'PAKISTAN', 'PALAU',
                            'PALESTINIAN TERRITORIES', 'PANAMA',
                            'PAPUA NEW GUINEA', 'PARAGUAY', 'PERU',
                            'PHILIPPINES', 'POLAND', 'PORTUGAL', 'QATAR',
                            'ROMANIA', 'RUSSIA', 'RWANDA',
                            'SAINT KITTS AND NEVIS', 'SAINT LUCIA',
                            'SAINT VINCENT AND THE GRENADINES', 'SAMOA',
                            'SAN MARINO', 'SAO TOME AND PRINCIPE',
                            'SAUDI ARABIA', 'SENEGAL', 'SERBIA', 'SEYCHELLES',
                            'SIERRA LEONE', 'SINGAPORE', 'SINT MAARTEN',
                            'SLOVAKIA', 'SLOVENIA', 'SOLOMON ISLANDS',
                            'SOMALIA', 'SOUTH AFRICA', 'SOUTH KOREA',
                            'SOUTH SUDAN', 'SPAIN', 'SRI LANKA', 'SUDAN',
                            'SURINAME', 'SWAZILAND', 'SWEDEN', 'SWITZERLAND',
                            'SYRIA', 'TAIWAN', 'TAJIKISTAN', 'TANZANIA',
                            'THAILAND', 'TIMOR-LESTE', 'TOGO', 'TONGA',
                            'TRINIDAD AND TOBAGO', 'TUNISIA', 'TURKEY',
                            'TURKMENISTAN', 'TUVALU', 'UGANDA', 'UKRAINE',
                            'UNITED ARAB EMIRATES', 'UNITED KINGDOM',
                            'URUGUAY', 'UZBEKISTAN', 'VANUATU', 'VENEZUELA',
                            'VIETNAM', 'YEMEN', 'ZAMBIA', 'ZIMBABWE'}

# Combine the different sets to create a single set of
            # recognized state and country abbreviations and names.
            state_full.update(us_state_terr_abbr, country_full)

# Separate out the multiword states.
            state_multi = {i for i in state_full if " " in i}

# Parse realprop table in case details are needed for condo
            # taxpayers.
            if not addr_rp_set:
                # If no addresses have an RP (only condos do), define
                # blank lists and set.
                condo_list = []
                condo_rp_list = [i[0] for i in condo_list]                                       
                condo_rp_set = set(condo_rp_list)
            else:
                messages.addMessage("\t\tParsing real property table...")
                condo_list = []
                with arcpy.da.SearchCursor("realprop_table", [
                        "PIN", "AcctNbr", "TaxpayerName", "AddrLine",
                        "CityState", "ZipCode"
                        ]) as cursor:
                    for row in cursor:
                        # Only want accounts that match address list
                        # RPs (real property PINs).
                        rp = (row[0].strip() if row[0] and
                              row[0].strip() else None)
                        if rp in addr_rp_set:                        
                            realprop_row = [rp]
                            
                            # Append additional data based on field
                            # type.
                            for ind, i in enumerate(row[1:], start=1):
                                if cursor.fields[ind] == "CityState":
                                    city_state = (
                                        i.strip() if i and i.strip() else None
                                        )
                                    if not city_state:
                                        realprop_row.extend([None, None])
                                        continue

# Split city and state if state
                                    # abbreviation or name is
                                    # recognized.
                                    city, delim_space, state_name = (
                                        city_state.rpartition(" ")
                                        )
                                    if (state_name.upper() in
                                            {"WASH", "WAA", "WN"}):
                                        state_name = "WA"
                                    if state_name.upper() in state_full:
                                        # Check for single-word states.
                                        realprop_row.extend([city, state_name])
                                    else:
                                        # Check for multiword states
                                        # before giving up.
                                        for state_name in state_multi:
                                            if city_state.endswith(state_name):
                                                city = city_state.rsplit(
                                                    state_name
                                                    )[0].strip()
                                                realprop_row.extend(
                                                    [city, state_name]
                                                    )
                                                break
                                        else:
                                            # Add a note in the State
                                            # field if a city and state
                                            # could not be confidently
                                            # separated.
                                            realprop_row.extend(
                                                [city_state,
                                                 "[Check City Field]"]
                                                )
                                else:
                                    try:
                                        # Strip whitespace and replace
                                        # empty strings with NoneTypes.
                                        realprop_row.append(
                                            i.strip() if i and i.strip() else
                                            None
                                            )
                                    except AttributeError:
                                        # No cleaning needed for
                                        # numerical data.
                                        realprop_row.append(i)

# Append the assembled row to the list.
                            condo_list.append(realprop_row)

# Remove duplicates from condo list based on account
                # number.
                condo_list.sort()
                condo_list_temp = condo_list[:]
                
                condo_list = []
                condo_already_added = set()
                for i in condo_list_temp:
                    if i[0] not in condo_already_added:
                        # Remove account number (position 1).
                        condo_list.append([i[0]] + i[2:])
                        condo_already_added.add(i[0])
                del condo_list_temp, condo_already_added
        
                # Sort condo list and create lists for matching.
                condo_list.sort()
                condo_rp_list = [i[0] for i in condo_list]                                       
                condo_rp_set = set(condo_rp_list)
                
                # Parse sales table in case details are needed for condo
                # taxpayers.
                messages.addMessage("\t\tParsing sales table...")
                sales_list = []
                with arcpy.da.SearchCursor(
                        "sales_table", ["PIN", "SALE_DATE", "BUYER_NAME"]
                        ) as cursor:
                    for row in cursor:
                        # Clean text data and separate date.
                        row = [i.strip() if
                               hasattr(i, "strip") and i.strip() else
                               i if not hasattr(i, "strip") else
                               None for i in row]

# Convert string date to true date object.
                        if row[-2]:
                            row[-2] = datetime.datetime.strptime(row[-2],
                                                                 "%m/%d/%Y")
                        date = row[-2]

# Only want sales that match condo list PINs.
                        rp = row[0]
                        if rp in condo_rp_set:
                            # ArcGIS date fields give date objects, so
                            # compare the year to the most recent two
                            # years.
                            if date and (date.year in CUR_YEARS):
                                # Append to list.
                                sales_list.append(row)

# Sort sales and keep only the most recent for each PIN.
                # Oldest on top.
                sales_list.sort(reverse=True)
                sales_list_temp = sales_list[:]
                
                sales_list = []
                sales_already_added = set()
                for i in sales_list_temp:
                    if i[0] not in sales_already_added:
                        # Reverse date and buyer (positions 1 and 2).
                        sales_list.append([i[0], i[2], i[1]])
                        sales_already_added.add(i[0])
                del sales_list_temp, sales_already_added

# Sort sales list and create lists for matching.
                sales_list.sort()
                sales_rp_list = [i[0] for i in sales_list]                                       
                sales_rp_set = set(sales_rp_list)

# Join buyer name and sale date into the condo list.
                for i in condo_list:
                    # Match PIN (0) and append buyer name (1).
                    if i[0] in sales_rp_set:
                        i.extend(sales_list[sales_rp_list.index(i[0])][1:])
                    else:
                        i.extend([None, None])

# Add new values back into address list.

messages.addMessage("\t\tAdding back to address list...")

# Define blank columns (omit TaxPIN or RP column as that
            # will only be used for matching, not adding).
            num_cols = len(addl_fields[1:])
            blank_cols = [None] * num_cols

# Extend each row with additional data or blank columns.
            for i in addr_list:
                # Check condo list.
                if i[1] in condo_rp_set:
                    i.extend(condo_list[condo_rp_list.index(i[1])][1:])
                    
                # Check parcel list.
                elif i[0] in addl_taxpin_set:
                    i.extend(addl_list[addl_taxpin_list.index(i[0])][1:])

# Append blank columns if unsuccessful.
                else:
                    i.extend(blank_cols)

# Convert all datetime objects (final column) to dates.
            # This helps with Excel formatting since it adds 0:00 at the
            # end of datetime objects without time data.
            for i in addr_list:
                if i[-1]:
                    i[-1] = i[-1].date()

# Carrier routes.
        
        messages.addMessage("\tCarrier routes")
        arcpy.SetProgressorLabel(label + "\n\tCarrier routes")
    
        # Parse data using Data Access Search Cursor.
        messages.addMessage("\t\tParsing selection...")        
        rout_list = []
        with arcpy.da.SearchCursor("route_layer",
                                   ["ZIP", "CR", "TOTRESCNT", "BIZCNT"]
                                   ) as cursor:
            for row in cursor:
                # Pass values through text cleaning, numbers left alone.
                row = [i.strip() if hasattr(i, "strip") and i.strip() else
                       i if not hasattr(i, "strip") else None for i in row]
                
                # Only want carrier routes with intact ZIP and CR.
                if row[0] and row[1]:
                    # Append to list as tuple (to enable set later).
                    rout_list.append(tuple(row))

# Remove duplicates (also, sort) by passing through a set.
        messages.addMessage("\t\tRemoving duplicates...")        
        rout_list = sorted(set(rout_list))

# Convert to list of lists (rather than list of tuples).
        messages.addMessage("\t\tConverting to lists...")
        rout_list = [list(i) for i in rout_list]

### Clean data formatting.

label = "Cleaning data formatting..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

# Ensure unicode text data can be encoded into ASCII for writing
        # to the .csv file. This is really only an issue with the last
        # character in "REBANE KENNETH P", which is a degree symbol,
        # the taxpayer for tax parcel TaxPIN "1568100110".
        
        messages.addMessage("\tChecking encoding to ASCII...")
        arcpy.SetProgressorLabel(label + "\n\tChecking encoding to ASCII...")
        
        for row in addr_list:
            for ind, i in enumerate(row):
                # Only test unicode objects.
                if isinstance(i, unicode):
                    try:
                        # Try to encode to "ascii".
                        i.encode("ascii")
                    except UnicodeEncodeError:
                        messages.addMessage("\t\tChanging: " + i)
                        
                        # If unsuccessful, work character by character
                        # to copy the string into ASCII as faithfully as
                        # possible, replacing non-ASCII characters with
                        # "[non-ASCII character]"s.
                        new_str = ""
                        for char in i:
                            try:
                                new_str += char.encode("ascii")
                            except UnicodeEncodeError:
                                # Have to even add the message as
                                # unicode to avoid an error.
                                messages.addMessage(u"\t\t\t\"{0}\" --> \""
                                                    u"[non-ASCII character]\"".
                                                    format(char))
                                new_str += "[non-ASCII character]"

# Replace the unicode string with the new ASCII-
                        # compatible string.
                        row[ind] = new_str
                        
        for row in rout_list:
            for ind, i in enumerate(row):
                # Only test unicode objects.
                if isinstance(i, unicode):
                    try:
                        # Try to encode to "ascii".
                        i.encode("ascii")
                    except UnicodeEncodeError:
                        messages.addMessage("\t\tChanging: " + i)
                        
                        # If unsuccessful, work character by character
                        # to copy the string into ASCII as faithfully as
                        # possible, replacing non-ASCII characters with
                        # "[non-ASCII character]"s.
                        new_str = ""
                        for char in i:
                            try:
                                new_str += char.encode("ascii")
                            except UnicodeEncodeError:
                                messages.addMessage("\t\t\t\"{0}\" --> \""
                                                    "[non-ASCII character]\"".
                                                    format(char))
                                new_str += "[non-ASCII character]"

# Replace the unicode string with the new ASCII-
                        # compatible string.
                        row[ind] = new_str

### Print resulting information to csv files.

label = "Printing .csv files..."
        messages.addMessage(label)
        arcpy.SetProgressorLabel(label)

# Addresses.
        messages.addMessage("\tAddresses")
        arcpy.SetProgressorLabel(label + "\n\tAddresses")

with open(out_addr, "wb") as csv_file:
            writer = csv.writer(csv_file)

# Write the column headers, based on which optional columns
            # were included.
            if addl_addr:
                header = ["TaxPIN", "Condo PIN", "Site Address", "Site City",
                          "Site State", "Site ZIP", "Taxpayer",
                          "Taxpayer Address", "Taxpayer City",
                          "Taxpayer State", "Taxpayer ZIP",
                          "Buyer ({0} Sales Only)".format(
                              "-".join(sorted(str(i) for i in CUR_YEARS))
                              ), "Sale Date"]
            else:
                header = ["TaxPIN", "Condo PIN", "Site Address", "Site City",
                          "Site State", "Site ZIP"]            
            writer.writerow(header)

# Write the rows.
            writer.writerows(addr_list)
            
        # Carrier routes.
        messages.addMessage("\tCarrier routes")
        arcpy.SetProgressorLabel(label + "\n\tCarrier routes")

with open(out_rout, "wb") as csv_file:
            writer = csv.writer(csv_file)

# Write the column headers.
            header = ["ZIP", "Carrier Route",
                      "Residential Address Count", "Business Address Count"]
            writer.writerow(header)

# Write the rows.
            writer.writerows(rout_list)

### Restate results.

label = "Restating results..."
        messages.addMessage("\n" + label)
        arcpy.SetProgressorLabel(label)

# State any output parameters and their values.
        for param in parameters:
            if param.direction == "Output":
                messages.addMessage("\n{0}:\n{1}".format(param.displayName,
                                                         param.value))

### If being run locally, attempt to open address CSV file in
        ### Excel.

if not SERVER_RUN:
            label = "*** Attempting to open in Excel... ***"
            messages.addMessage("\n" + label)
            arcpy.SetProgressorLabel(label)

excel_path = (r"C:\Program Files (x86)\Microsoft Office\Office14"
                          r"\EXCEL.EXE")

# Check for existence of Microsoft Excel 2010 application.
          #  if os.path.exists(excel_path):
                # Attempt to open using subprocess module.
                 # output = subprocess.check_output([excel_path, out_addr])
            messages.addMessage(out_addr)
            os.startfile(out_addr)
        
        
        ### End the script.

messages.addMessage("\n{0:*^33}\n".format(" Script completed. "))

return

##class Tool(object):
##    def __init__(self):
##        """Define the tool (tool name is the name of the class)."""
##        self.label = "Tool"
##        self.description = ""
##        self.canRunInBackground = False
##
##    def getParameterInfo(self):
##        """Define parameter definitions"""
##        params = None
##        return params
##
##    def isLicensed(self):
##        """Set whether tool is licensed to execute."""
##        return True
##
##    def updateParameters(self, parameters):
##        """Modify the values and properties of parameters before internal
##        validation is performed.  This method is called whenever a parameter
##        has been changed."""
##        return
##
##    def updateMessages(self, parameters):
##        """Modify the messages created by internal validation for each tool
##        parameter.  This method is called after internal validation."""
##        return
##
##    def execute(self, parameters, messages):
##        """The source code of the tool."""
##        return

Python Fiddle

Python Cloud IDE