Notebook

Car Complaints dashboard
A Study of Car Complaints Data using Geospatial Analysis and Outlier Detection

Introduction

Customer complaints are often tricky to handle, with multiple data sources not often used simultaneously (including text and geolocation of complaints, service centres, etc.) This demo highlights Vantage features that address this problem with assistance from graphics libraries in Python.
The demo seeks to provide the business user a fuller view of their customers where to focus highlighting cases for special attention.
Key benefits of this kind of analysis

Better understanding of the current status through visual representation
Easy implementation meant to scale as it leverages Vantage functionalities
Unconventional usage of Vantage functions (geospatial for attribution, multi-level outlier detection)

Data is sourced from a public database by the National Highway Traffic Safety Administration (NHTSA) of the USA with a few modifications to analyse data on a state-county level.

1000 records of cars by Ford motors were randomly sampled
Each complaint was added a geolocation (latitude, longitude) to a location in Iowa (to simulate data coming from a single state)
Records were limited to 2019

We will do two kind of analysis

Service Center Analysis - A geospatial attribution of complaints to its nearby service centres and county-specific ranking to search for blind spots.
Defect Analysis - Defect outlier detection to spot complaints with parts that defected earlier than expected.

1. Import python packages, connect to Vantage and explore the dataset

In [1]:

#import libraries
import geopandas as gpd
import matplotlib.pyplot as plt 
import pandas as pd
import seaborn as sns
import getpass
from teradataml.dataframe.dataframe import DataFrame
from teradataml.dataframe.dataframe import in_schema
from teradataml.context.context import create_context, remove_context, get_context
import plotly.express as px
import json
from pandas import json_normalize
import numpy as np
import plotly.express as px
from  ipywidgets import widgets, interact

display.max_rows = 5 

You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell.

In [2]:

%run -i ../startup.ipynb
eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)
print(eng)
eng.execute('''SET query_band='DEMO=Car_Complaints_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')

Performing setup ...
Setup complete

... Logon successful
Connected as: xxxxxsql://demo_user:xxxxx@host.docker.internal/dbc
Engine(teradatasql://demo_user:***@host.docker.internal)

Out[2]:

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f682861a310>

Getting Data for This Demo

We have provided data for this demo on cloud storage. You have the option of either running the demo using foreign tables to access the data without using any storage on your environment or downloading the data to local storage which may yield somewhat faster execution, but there could be considerations of available storage. There are two statements in the following cell, and one is commented out. You may switch which mode you choose by changing the comment string.

In [3]:

#%run -i ../run_procedure.py "call get_data('DEMO_Car_cloud');"
 # takes about 30 seconds, estimated space: 0 MB
%run -i ../run_procedure.py "call get_data('DEMO_Car_local');" 
# takes about 1min 40seconds, estimated space: 1.5 MB

That ran for   0:01:39.28 with 25 statements and 0 errors.

Next is an optional step – if you want to see status of databases/tables created and space used.

In [4]:

%run -i ../run_procedure.py "call space_report();"

You have:  #databases=2 #tables=9 #views=4  You have used 2.9 MB of 27,914.4 MB available - 0.0%  ... Space Usage OK
 
   Database Name                  #tables  #views     Avail MB      Used MB
   demo_user                            5       0  27,878.5 MB       1.4 MB 
   DEMO_Car                             0       4       0.0 MB       0.0 MB 
   DEMO_Car_db                          4       0      35.9 MB       1.5 MB

2. Initial Data Sets

Let us take a look at the source tables we have.

In [5]:

df1 = DataFrame(in_schema("DEMO_Car", "Complaint_Locations"))
df1

Out[5]:

cmplid	geometry
1564346	POINT (-95.203935381326289 41.84356706992024)
1592038	POINT (-90.759632737352533 41.841014106885609)
1589958	POINT (-91.83893923805752 42.112720943539308)
1560043	POINT (-93.367653121862645 41.813915134399217)
1569892	POINT (-91.011096824541681 42.001379944902283)
1563040	POINT (-93.289931174023664 41.452359024785594)
1594709	POINT (-93.451746875460316 41.848272119173828)
1537918	POINT (-91.659043652379495 41.386208392399162)
1563164	POINT (-93.579228558913059 41.664012549435313)
1577051	POINT (-94.215753723691407 43.387213236595919)

In [6]:

df1.shape

Out[6]:

(1000, 2)

In [7]:

df1.dtypes

Out[7]:

COLUMN NAME	TYPE
cmplid	int
geometry	str

From above we can see Complaint_Locations table has complaint id and the geo location from where the complaint was raised.It has 1000 records in it.

In [8]:

df2 = DataFrame(in_schema("DEMO_Car", "Service_Centers"))
df2

Out[8]:

service_center_id	service_center	lat	lon	start_yearmonth	end_yearmonth	geometry
78	Station 78	41.929	-94.844	201808	201904	POINT (-94.84424315 41.92872697)
116	Station 116	42.614	-94.483	201809	201903	POINT (-94.48267434 42.61391254)
93	Station 93	42.472	-94.484	201906	202010	POINT (-94.48434541 42.4716659)
133	Station 133	42.966	-95.184	201811	202004	POINT (-95.18362174 42.96590341)
108	Station 108	42.731	-92.743	201805	201912	POINT (-92.74326291 42.73074588)
26	Station 26	41.145	-94.285	201807	201812	POINT (-94.28532956 41.14490542)
131	Station 131	43.094	-94.539	201906	202103	POINT (-94.5394441 43.09422115)
76	Station 76	42.134	-94.619	201902	202007	POINT (-94.61892353 42.13362844)
38	Station 38	41.284	-93.477	201905	202101	POINT (-93.47723369 41.28409804)
61	Station 61	41.540	-94.747	201911	202106	POINT (-94.74675189 41.5397029)

In [9]:

df2.shape

Out[9]:

(138, 7)

In [10]:

df2.dtypes

Out[10]:

COLUMN NAME	TYPE
service_center_id	int
service_center	str
lat	decimal.Decimal
lon	decimal.Decimal
start_yearmonth	int
end_yearmonth	int
geometry	str

From above we can see Service_Centers table has information of the service center. We have information on 138 service centers.

In [11]:

df3 = DataFrame(in_schema("DEMO_Car", "Complaints"))
df3

Out[11]:

complaint_id	complaint_date	purchase_date	manufacture_date	days_to_defect	months_to_defect	complaint_desc	manufacturer	model	model_year	car_part
1564346	2019/03/10	2014/05/27	2010/07/27	1748	58	TL* THE CONTACT OWNS A 2010 FORD F-150. WHILE DRIVING APPROXIMATELY 60 MPH, THE VEHICLE LOST ACCELERATION AND THE WRENCH WARNING INDICATOR ILLUMINATED. THE CONTACT PUMPED THE ACCELERATOR PEDAL AND THE VEHICLE REGAINED ACCELERATION. THE CONTACT ALSO STATED THAT THE LOSS OF THROTTLE POWER OCCURRED FOUR TIMES IN ONE DAY. THE CONTACT DROVE THE VEHICLE TO PEORIA FORD (9130 W BELL RD, PEORIA, AZ 85382) WHERE IT WAS DIAGNOSED THAT FUSE 70 BURNED AND MELTED THE FUSE BOX. THE VEHICLE WAS REPAIRED. THE MANUFACTURER WAS NOTIFIED OF THE FAILURE. THE APPROXIMATE FAILURE MILEAGE WAS 71,000.	ford motor company	F-150	2010	VEHICLE SPEED CONTROL
1592038	2019/08/14	2012/03/05	2007/07/19	2718	89	VEHICLE WAS IN MOTION FOR AWHILE, CAR WAS RUNNING OKAY UNTIL I CAME TO A STOPLIGHT AND THEN ALL OF A SUDDEN MY CAR GOES LIMP. MY VEHICLE MAKES LOUD/HARD CLANK NOISE. LOSS OF POWER, WRENCH LIGHT CAME ON. PULLED OVER, LOUD/HARD CLANK SOUND WITH EVERY GEAR CHANGE P-2. VEHICLE HAD DISPLAYED NO ISSUES PRIOR TO THIS. I AM ABLE TO PULL THE CAR OVER, TURN IT OFF AND TAKE THEY KEY OUT OF THE IGNITION. THEN THE WRENCH LIGHT IS OFF WHEN I RESTART THE CAR. STAYS IN LOW GEAR FOR A LONG TIME WHEN I START TO ACCELERATE, AND UNTIL VEHICLE EVENTUALLY ACCELERATES FASTER, OTHER CARS ALMOST RUNNING INTO THE BACK OF MY CAR SINCE MY CAR IS NOT ACCELERATING AT A NORMAL PACE. I AM GREATLY CONCERNED ABOUT WHAT APPEARS TO BE A HUGE SAFETY ISSUE WITH THIS CAR. THERE ARE MULTIPLE COMPLAINTS ABOUT THE SAME ISSUE TO BE FOUND ALL OVER THE INTERNET. FORD NEEDS TO ISSUE A RECALL AND FIX THIS ASAP BEFORE SOMEONE GETS SERIOUSLY HURT OR IS KILLED FROM THE EXTREME LOSS OF POWER THE VEHICLE IS SUFFERING FROM WHEN THIS OCCURS!	ford motor company	EDGE	2007	ELECTRONIC STABILITY CONTROL
1589958	2019/08/08	2017/10/21	2011/12/13	656	22	MY 2012 F150 WITH 82K, MILES ON IT 08/08/2019 GOT IN MY VEHICLE STARTER VEHICLE IT MADE A STRANGE NOISE FROM ENGINE COMPARTMENT PROCEEDED TO BACK OUT OF DRIVEWAY AND HAD NO POWER STEERING. WHAT SCARES ME IS WHAT IF I HAD BEEN ON THE FREEWAY AT 50 MILES AN HOUR OR BETTER AND THE POWER STEERING WENT OUT? ALSO MY DOOR LOCK ON DRIVER SIDE WOULD NOT WORK PAID $400 TO HAVE IT FIXED NOW THE PASSENGER SIDE HAS FAILED. MY QUESTION IS WHAT IF I COULD NOT GET OUT IF MY TRUCKS POWER STEERING WENT OUT WHILE DRIVING AND CRASHED I COULDNT GET OUT. I FEEL THIS IS NOT A COINCIDENT,I FEEL THIS IS FACTORY DEFECTIVE PARTS! THERES TOO MANY OF THE SAME REPORTED INCIDENTS, DOES SOMEONE HAVE TO DIE BEFORE WE GET A FIX? MY NEXT STEP IS SOCIAL MEDIA AND A 20 20 INVESTIGATION INTO WHY NO ONE HAS MADE AN ATTEMPT TO SAVE HUMAN LIVES AS OPPOSED TO BRIBERY AND FAT BANK ACCOUNTS. I BELIEVE IT IS A POWER STEERING MODULAR/FACTORY DEFECT. ALSO I DONT KNOW WHAT IS MAKING THE DOOR LOCKS FAIL EXCEPT FOR FACTORY DEFECTIVE EQUIPMENT,IM NOT A MECHANIC JUST A HARD WORKING AMERICAN CITIZEN, MADE IN AMERICA USED TO MEAN SOMETHING! IVE MADE COPIES OF THIS REPORT FOR FUTURE USE. FOR FUTURE CORRESPONDENCE; [XXX] TO WHOMEVER RECEIVES THIS REPORT I HOPE DOESNT PUT IT IN THE DEAD FILE. THANK YOU, I THINK? INFORMATION REDACTED PURSUANT TO THE FREEDOM OF INFORMATION ACT (FOIA), 5 U.S.C. 552(B)(6). *TR	ford motor company	F-150	2012	ELECTRICAL SYSTEM
1560043	2019/02/26	2004/06/25	2004/05/20	5359	176	FORD INERTIA SWITCH. WHILE DRIVING ON THE HIGHWAY AT 65 MILES PER HOUR THE VEHICLE SUDDENLY LOST ALL POWER AND PROPULSION, CAUSING MY POWER STEERING TO GO OFF AS WELL AS ALL INSTRUMENTATION TO TURN OFF, WHILE MOVING AT 65 MPH. THE ONLY THING I COULD DO IS TURN MY HAZARD LIGHTS ON FIND AN EXIT AND COAST TO THE SIDE OF THE ROAD. THIS CAUSED AN EXTREMELY UNSAFE SITUATION, AND ONE THAT VERY WELL COULD HAVE ENDED IN DEATH. THE ONLY ISSUE IS THAT IT OCCURS VERY SPORADIC AND ITS VERY HARD TO TROUBLESHOOT OR RECREATE THE FAULT.	ford motor company	EXPLORER SPORT TRAC	2005	FUEL/PROPULSION SYSTEM
1569892	2019/05/20	2016/10/03	2011/05/29	959	31	I WAS DRIVING TO WORK THROUGH A BUSY TOWN I WAS PREPARING TO STOP AT A STOPLIGHT AND MY BRAKES WENT TO THE FLOOR THERE WERENT ANY SIGNS OF BRAKE ISSUES PRIOR SO I ASSUMED THE BRAKES NEEDED REPLACING SO WHEN I GOT OFF WORK I WENT AND BOUGHT BRAKES MY HUSBAND WAS GONNA PUT THEM ON WHEN HE CHECKED HE SAID THE BRAKES WERE FINE THAT THE FLUID COULD BE LEAKING THE FLUID LEVEL WAS FINE AND NO LEAKS WERE FOUND HE STILL LOOKED FOR ONE JUST IN CASE SO NEXT HE SAID IT COULD BE MASTER CYLINDER AND RESERVOIR I WAS LOOKING ONLINE AT THIS POINT AND SEEN ALL THE SAME ISSUES OTHERS WERE HAVING AS WELL WITH THIS SUV I TOLD HIM THERE WASNT ANY NEED TO SPEND MORE MONEY BECAUSE THE WAY IT SOUNDED WAS THAT ITS NOT GONNA FIX THE ISSUE ANY HOW BECAUSE FORD ITSELF CANT FIGURE OUT WHY BRAKES ARE GOING TO THE FLOOR THEMSELVES SO IM LEFT WITH A UNSAFE VEHICLE TO DRIVE WITH A PAYOFF OF 10000 DOLLARS WHAT DO I DO? IM SO TIRED OF GETTING SCREWED THAT IM ABT TO HIRE AN ATTORNEY AND FIND OUT WHY AM I LEFT WITH THIS PROBLEM TO FACE AND WHY OTHERS ARE HAVING TO SUFFER AS WELL IF THERES AN ONGOING LAWSUIT NEAR WASHINGTON NC LET ME KNOW IF NOT THERES SOON TO BE ONE IF OTHERS NEED TO JOIN AS WELL	ford motor company	EDGE	2012	ELECTRICAL SYSTEM
1563040	2019/01/02	2017/03/30	2014/04/14	643	22	TL* THE CONTACT OWNS A 2014 FORD FOCUS. THE CONTACT STATED THAT WHILE DRIVING, THE VEHICLE DELAYS TO ACCELERATE AND THE ABNORMALLY SHOOK. THE DEALER AND THE MANUFACTURER HAD NOT BEEN CONTACTED. THE FAILURE MILEAGE WAS 70,000.	ford motor company	FOCUS	2014	POWER TRAIN
1594709	2019/08/22	2018/09/19	2012/12/04	337	11	MY BACK UP CAMERA SAYS CAMERA IS UNAVAILABLE. PLEASE CONTACT YOUR DEALERSHIP. WHEN I PUT THE CAR IN REVERSE. I JUST BOUGHT THE CAR TWO DAYS AGO AND IT STARTED DOING THIS, THIS MORNING.	ford motor company	EDGE	2013	ELECTRICAL SYSTEM
1537918	2019/02/14	2018/03/29	2014/09/12	322	11	THE TRANSMISSION IS SHUTTERING AND SOMETIMES MAKES LONG HESITATIONS TO SHIFT. THIS IS DANGEROUS AND NEEDS TO BE RECALLED. I TOOK IT TO TIDWELL FORD IN KENNESAW, GA AND THEY TOLD ME ITS NORMAL AND UNTIL A CODE (WRENCH) LIGHTS ON MY DASH, THEY CANNOT DO ANYTHING. I BOUGHT THE CAR WITH 48K ON THE MILEAGE AND NOW HAVE 54K. THE TRANSMISSION (POWERTRAIN) IS WARRANTIED TO 60000. I AM AFRAID THIS IS GOING TO BE A DISASTER. I WOULD LIKE THE CAR FIXED OR ANOTHER CAR. THE GEARS GRIND AND NOISES HAPPEN WHEN TURNING. I NEED A RESOLUTION ON THIS BEFORE MY WARRANTY RUNS OUT CODE OR NO CODE. I JUST PICKED MY CAR UP AND IT IS STILL SHUTTERING. TIDWELL FORD DID NOT HELP ME.	ford motor company	FOCUS	2015	POWER TRAIN
1563164	2019/02/05	2013/02/09	2012/06/18	2187	72	DOWNSHIFTS INTO 1ST... STILL...AFTER THE RECALL APPOINTMENT. RESET, BUT CONTINUES AND WE HAVE ONLY HAD THE TRUCK A SHORT TIME. IT HAS ALMOST CAUSED A FEW WRECKS AND ONE WITH A TRAILER PULLING MACHINERY. WE BOUGHT THIS TRUCK TO HAVE A NEWER TRUCK TO HAUL OUR HORSES AS WELL, AND CANNOT DO THAT. THIS NEEDS TO BE TAKEN CARE OF! WE HAVE ALL FORD F 150S IN OUR FAMILY AND HATE TO HAVE TO CHANGE TO SOMETHING ELSE IF THIS CANNOT BE RESOLVED. A NEW TRANSMISSION/??? IS IT GOING TO TAKE A DEATH TO FIX WHAT IS WRONG??? TRUCK IS ALWAYS IN MOTION AND AT VARIOUS TIMES WHEN THIS HAPPENS. NO WARNING, CAN BE FINE FOR DAYS THEN HAPPENS ALL OF A SUDDEN. STREET, HIGHWAY, DOESNT MATTER. AND NOW ITS NOT WANTING TO COME BACK DOWN AFTER SHIFTING BACK UP FROM 1ST. NOT SURE WHAT TO DO NOW. CANT SELL IN GOOD CONSCIOUS TO SOMEONE, SO IT SITS. BEAUTIFUL TRUCK, JUST BAD TRANNY. OH, AND ALSO, THE TRUCK THINKS THE REAR DOOR IS OPEN AND ITS NOT, WHICH KEEPS US FROM BEING ABLE TO LOCK IT AS WELL AS THE LIGHTS REMAIN ON FOR A BIT AFTER SHUTTING OFF (USING THE BATTERY). NOT SURE IF THIS HAS A RECALL OR NOT.	ford motor company	F-150	2013	POWER TRAIN
1577051	2019/06/20	2016/08/30	2014/11/21	1024	34	TL* THE CONTACT OWNS A 2015 FORD F-150. WHILE DRIVING 70 MPH, THE VEHICLES THROTTLE COMPLETELY FAILED AND SEVERAL UNKNOWN WARNING INDICATORS ILLUMINATED. THE CONTACT WAS ABLE TO COAST THE VEHICLE TO THE SIDE OF THE ROAD AND RESTART THE VEHICLE. THE VEHICLE WAS TAKEN TO HOLMES TUTTLE FORD LINCOLN (660 W AUTO MALL DR, TUCSON, AZ 85705, (866) 930-6255) WHERE IT WAS DIAGNOSED THAT THE THROTTLE BODY NEEDED TO BE REPLACED. THE VEHICLE WAS REPAIRED. THE MANUFACTURER WAS NOT NOTIFIED OF THE FAILURE. THE FAILURE MILEAGE WAS 79,500.	ford motor company	F-150	2015	VEHICLE SPEED CONTROL

In [12]:

df3.shape

Out[12]:

(1000, 11)

In [13]:

df3.dtypes

Out[13]:

COLUMN NAME	TYPE
complaint_id	int
complaint_date	datetime.date
purchase_date	datetime.date
manufacture_date	datetime.date
days_to_defect	int
months_to_defect	int
complaint_desc	str
manufacturer	str
model	str
model_year	int
car_part	str

Complaint table has the information of the complaint registered.

Additionaly we have also taken the IOWA county boundry information from https://geodata.iowa.gov/datasets/iowa::iowa-county-boundaries

In [14]:

df4 = DataFrame(in_schema("DEMO_Car", "Counties"))
df4

Out[14]:

county_id	county_name	population	geometry	geo_mbr
19063	Emmet	9551	POLYGON ((-94.898 43.5008,-94.878 43.5008,-94.8756 43.5008,-94.8749 43.5008,-94.8582 43.5008,-94.8377 43.5008,-94.818 43.501,-94.8179 43.501,-94.7979 43.5011,-94.7784 43.5011,-94.7583 43.501,-94.7506 43.501,-94.7499 43.501,-94.7383 43.501,-94.7185 43.5009,-94.7183 43.5009,-94.6983 43.5009,-94.6784 43.5008,-94.6782 43.5008,-94.6602 43.5008,-94.6405 43.5006,-94.6404 43.5006,-94.6254 43.5007,-94.625 43.5007,-94.6204 43.5007,-94.6006 43.5006,-94.5808 43.5005,-94.5807 43.5005,-94.5609 43.5004,-94.5424 43.5005,-94.5225 43.5005,-94.5025 43.5005,-94.5005 43.5005,-94.4999 43.5005,-94.4824 43.5005,-94.4623 43.5003,-94.4428 43.5004,-94.4426 43.5004,-94.4425 43.4883,-94.4426 43.4739,-94.4425 43.4595,-94.4425 43.4594,-94.4424 43.445,-94.4423 43.4305,-94.4423 43.4142,-94.4423 43.4141,-94.4424 43.3998,-94.4424 43.3997,-94.4424 43.3854,-94.4424 43.3853,-94.4426 43.3709,-94.4427 43.3564,-94.4428 43.3421,-94.4428 43.3273,-94.4428 43.3129,-94.4429 43.2984,-94.443 43.284,-94.4431 43.2696,-94.4431 43.2552,-94.4629 43.255,-94.4631 43.255,-94.4828 43.2551,-94.5024 43.2551,-94.5215 43.2552,-94.5411 43.2551,-94.5603 43.2554,-94.5802 43.2552,-94.6 43.2552,-94.6197 43.2552,-94.625 43.2552,-94.6255 43.2552,-94.6395 43.2553,-94.6595 43.2552,-94.6786 43.2553,-94.6983 43.2553,-94.7182 43.2553,-94.7183 43.2553,-94.7382 43.2553,-94.7579 43.2552,-94.7581 43.2552,-94.7777 43.2553,-94.7969 43.2552,-94.7971 43.2552,-94.8168 43.2553,-94.817 43.2553,-94.8369 43.2551,-94.837 43.2551,-94.8566 43.2552,-94.8765 43.255,-94.8767 43.255,-94.8962 43.2552,-94.9137 43.255,-94.9139 43.255,-94.9139 43.2551,-94.9139 43.2695,-94.9142 43.284,-94.9141 43.2984,-94.914 43.313,-94.9141 43.3275,-94.914 43.3414,-94.9139 43.3559,-94.9139 43.3705,-94.914 43.3748,-94.914 43.375,-94.9141 43.3849,-94.9143 43.3997,-94.9144 43.4142,-94.9145 43.4304,-94.9145 43.4305,-94.9147 43.4448,-94.9147 43.4449,-94.9147 43.4593,-94.9148 43.4738,-94.9148 43.4882,-94.9148 43.5009,-94.898 43.5008))	(-94.9148,43.255,-94.4423,43.5011)
19061	Dubuque	96802	POLYGON ((-90.8962 42.6748,-90.8955 42.6746,-90.8948 42.6743,-90.8937 42.674,-90.8927 42.6738,-90.8919 42.6736,-90.8906 42.6732,-90.8898 42.6731,-90.8888 42.6728,-90.888 42.6726,-90.8873 42.6723,-90.8866 42.6721,-90.8855 42.6719,-90.8845 42.6717,-90.8833 42.6716,-90.8821 42.6713,-90.8813 42.6711,-90.8806 42.6709,-90.8799 42.6709,-90.8791 42.6706,-90.8784 42.6705,-90.8779 42.6705,-90.8775 42.6704,-90.8767 42.6702,-90.876 42.6701,-90.8752 42.67,-90.8751 42.6698,-90.8742 42.6698,-90.8733 42.6698,-90.8726 42.6698,-90.8718 42.6695,-90.8709 42.6693,-90.8695 42.6691,-90.8673 42.6686,-90.8664 42.6684,-90.8652 42.6682,-90.8643 42.668,-90.8635 42.6677,-90.8626 42.6677,-90.8617 42.6674,-90.8607 42.6672,-90.8598 42.6669,-90.8591 42.6666,-90.8583 42.6664,-90.8579 42.6662,-90.8568 42.666,-90.8561 42.6656,-90.8552 42.6655,-90.8534 42.6652,-90.8527 42.6651,-90.8518 42.6647,-90.8506 42.6643,-90.8494 42.6641,-90.8487 42.664,-90.8478 42.6639,-90.847 42.6638,-90.8463 42.6636,-90.8451 42.6633,-90.8444 42.6631,-90.8436 42.663,-90.8427 42.6629,-90.8418 42.6627,-90.8411 42.6626,-90.8404 42.6624,-90.8391 42.6623,-90.8383 42.6622,-90.8375 42.6621,-90.8363 42.662,-90.8355 42.6619,-90.8347 42.6618,-90.8335 42.6616,-90.8328 42.6616,-90.8317 42.6615,-90.8305 42.6613,-90.8295 42.6612,-90.8286 42.661,-90.8277 42.6608,-90.827 42.6607,-90.8261 42.6604,-90.8249 42.6601,-90.8244 42.66,-90.8237 42.6598,-90.823 42.6597,-90.8216 42.6594,-90.8209 42.6592,-90.8204 42.6591,-90.8202 42.6591,-90.8194 42.659,-90.8186 42.6589,-90.8179 42.6588,-90.8172 42.6586,-90.816 42.6583,-90.8152 42.6583,-90.8141 42.6581,-90.813 42.658,-90.8123 42.6579,-90.8109 42.6577,-90.8098 42.6576,-90.8088 42.6574,-90.8081 42.6573,-90.8069 42.657,-90.8058 42.6569,-90.805 42.6568,-90.802 42.6564,-90.8009 42.6562,-90.8001 42.656,-90.799 42.6559,-90.7986 42.6559,-90.7977 42.6558,-90.797 42.6556,-90.7958 42.6554,-90.7951 42.6553,-90.7937 42.6549,-90.793 42.6548,-90.7921 42.6546,-90.7911 42.6544,-90.7903 42.6542,-90.7895 42.6542,-90.7887 42.6539,-90.7878 42.6536,-90.787 42.6535,-90.7852 42.6535,-90.7836 42.6533,-90.7824 42.653,-90.7814 42.6529,-90.7805 42.6529,-90.7797 42.6527,-90.7789 42.6525,-90.778 42.6524,-90.7771 42.6523,-90.7759 42.652,-90.775 42.6518,-90.774 42.6518,-90.7723 42.6516,-90.7715 42.6516,-90.7707 42.6515,-90.7698 42.6514,-90.7688 42.6513,-90.7679 42.651,-90.7667 42.6506,-90.7656 42.6504,-90.7649 42.6503,-90.764 42.6501,-90.7627 42.6498,-90.7616 42.6495,-90.7606 42.6492,-90.7599 42.6492,-90.7586 42.6486,-90.7577 42.6483,-90.7572 42.6481,-90.7564 42.648,-90.7553 42.6478,-90.7547 42.6477,-90.7536 42.6476,-90.7529 42.6475,-90.7518 42.6473,-90.7507 42.6471,-90.7502 42.6471,-90.7487 42.6465,-90.7475 42.6462,-90.7461 42.6458,-90.7448 42.6455,-90.7443 42.6453,-90.7435 42.6452,-90.7428 42.6451,-90.7419 42.645,-90.7409 42.6448,-90.7401 42.6447,-90.7399 42.6446,-90.7391 42.6445,-90.738 42.6445,-90.7368 42.6443,-90.7359 42.6442,-90.7351 42.6442,-90.7343 42.6441,-90.7333 42.6439,-90.7322 42.6436,-90.7312 42.6434,-90.7298 42.643,-90.7291 42.6429,-90.7278 42.6426,-90.7269 42.6424,-90.7261 42.6422,-90.7252 42.642,-90.7244 42.6417,-90.7235 42.6415,-90.7221 42.6411,-90.7214 42.641,-90.7206 42.6408,-90.7203 42.6407,-90.7197 42.6405,-90.7187 42.6403,-90.7179 42.6399,-90.7171 42.6396,-90.7159 42.639,-90.7152 42.6387,-90.714 42.6384,-90.7129 42.638,-90.712 42.6377,-90.7113 42.6373,-90.7105 42.637,-90.7098 42.6365,-90.7088 42.6359,-90.7077 42.6352,-90.707 42.6347,-90.7062 42.6341,-90.7053 42.6334,-90.7048 42.6329,-90.704 42.6324,-90.7034 42.6318,-90.7029 42.6311,-90.7022 42.6305,-90.7019 42.6301,-90.7015 42.6295,-90.7013 42.6292,-90.701 42.6288,-90.7008 42.6283,-90.7005 42.6274,-90.7005 42.6268,-90.7005 42.6262,-90.7006 42.6255,-90.7006 42.625,-90.7008 42.6244,-90.7006 42.6239,-90.7005 42.6233,-90.7004 42.6227,-90.7001 42.622,-90.6995 42.6213,-90.6992 42.6208,-90.6987 42.6202,-90.6981 42.6194,-90.6979 42.6187,-90.6974 42.6182,-90.6965 42.6173,-90.6961 42.6167,-90.6954 42.6161,-90.6949 42.6154,-90.6948 42.6153,-90.6942 42.6147,-90.6936 42.6138,-90.6934 42.6132,-90.693 42.6127,-90.6929 42.6121,-90.6924 42.6112,-90.6922 42.6105,-90.6919 42.6097,-90.6917 42.6088,-90.6914 42.6079,-90.6912 42.607,-90.6909 42.6065,-90.6908 42.606,-90.6906 42.6054,-90.6904 42.6047,-90.69 42.604,-90.6897 42.6033,-90.6893 42.6023,-90.6888 42.6015,-90.6885 42.601,-90.6884 42.6007,-90.6881 42.5999,-90.6879 42.5991,-90.6878 42.5983,-90.6878 42.5976,-90.6878 42.597,-90.6878 42.5965,-90.6878 42.5957,-90.6878 42.5948,-90.6877 42.5943,-90.6876 42.5936,-90.6874 42.5929,-90.6873 42.5923,-90.6869 42.5914,-90.6867 42.5909,-90.6861 42.59,-90.6855 42.5892,-90.685 42.5887,-90.6843 42.5878,-90.6838 42.587,-90.6834 42.5865,-90.6833 42.5863,-90.6825 42.5853,-90.6825 42.5852,-90.6824 42.5852,-90.6819 42.5845,-90.6816 42.584,-90.681 42.5833,-90.6803 42.5824,-90.6798 42.5818,-90.6793 42.5811,-90.6785 42.5804,-90.6777 42.5797,-90.6769 42.5789,-90.6758 42.5783,-90.6746 42.5775,-90.6736 42.577,-90.6724 42.5764,-90.6713 42.5757,-90.6706 42.5754,-90.6695 42.5748,-90.6687 42.5744,-90.6679 42.5741,-90.6672 42.5736,-90.6664 42.5732,-90.6656 42.5729,-90.6648 42.5722,-90.6643 42.5719,-90.664 42.5717,-90.6636 42.571,-90.6631 42.5703,-90.6628 42.5697,-90.6622 42.5691,-90.6621 42.569,-90.6616 42.5683,-90.6612 42.5674,-90.6609 42.5666,-90.6606 42.5657,-90.6603 42.565,-90.6601 42.5641,-90.6598 42.5633,-90.6597 42.5622,-90.6595 42.5616,-90.6594 42.5609,-90.6594 42.5602,-90.6594 42.5595,-90.6593 42.5588,-90.6593 42.5582,-90.6592 42.5575,-90.6592 42.5574,-90.659 42.5568,-90.6589 42.5563,-90.6586 42.5554,-90.6583 42.5549,-90.6581 42.5543,-90.6578 42.5537,-90.6577 42.5532,-90.6574 42.5526,-90.6568 42.552,-90.6563 42.5514,-90.6558 42.5509,-90.655 42.5504,-90.6541 42.5498,-90.6531 42.5491,-90.6522 42.5486,-90.6515 42.5482,-90.6505 42.5476,-90.6498 42.5472,-90.6491 42.5467,-90.6483 42.5463,-90.6476 42.5459,-90.6468 42.5452,-90.6459 42.5445,-90.6458 42.5443,-90.6453 42.5436,-90.645 42.5431,-90.6449 42.5425,-90.6444 42.5417,-90.6443 42.5411,-90.644 42.5405,-90.6438 42.5399,-90.6435 42.5389,-90.6432 42.5384,-90.6431 42.5376,-90.6429 42.5369,-90.6428 42.5361,-90.6428 42.5354,-90.6426 42.5348,-90.6424 42.5342,-90.6423 42.5335,-90.642 42.5328,-90.6419 42.5322,-90.6417 42.5317,-90.6416 42.531,-90.6414 42.5301,-90.6414 42.53,-90.6413 42.5295,-90.6411 42.529,-90.6408 42.5281,-90.6404 42.527,-90.6399 42.5261,-90.6396 42.5252,-90.6393 42.5246,-90.6387 42.5237,-90.6384 42.5231,-90.638 42.5222,-90.6377 42.5214,-90.6374 42.5208,-90.6371 42.5201,-90.6368 42.5193,-90.6365 42.5186,-90.6365 42.5178,-90.6363 42.5172,-90.6363 42.5167,-90.6361 42.5159,-90.6361 42.5154,-90.6361 42.515,-90.6363 42.5143,-90.6365 42.5137,-90.6368 42.5131,-90.637 42.5126,-90.6373 42.512,-90.6378 42.5114,-90.6384 42.5108,-90.6388 42.5102,-90.6395 42.5095,-90.6402 42.509,-90.6409 42.5083,-90.6416 42.5075,-90.642 42.5071,-90.6422 42.5069,-90.643 42.5061,-90.6437 42.5054,-90.6443 42.5047,-90.6448 42.504,-90.6453 42.5032,-90.6459 42.5023,-90.6465 42.5014,-90.6468 42.5008,-90.6471 42.5003,-90.6478 42.5003,-90.6471 42.5,-90.6474 42.4994,-90.6478 42.4989,-90.6483 42.4982,-90.6487 42.4976,-90.6493 42.4969,-90.6501 42.4963,-90.6506 42.4957,-90.6514 42.4952,-90.6522 42.4947,-90.653 42.4941,-90.6539 42.4936,-90.6548 42.493,-90.6554 42.4924,-90.6557 42.4918,-90.6562 42.4912,-90.6566 42.4906,-90.6566 42.49,-90.6566 42.4893,-90.6566 42.4888,-90.6565 42.488,-90.6565 42.4873,-90.6565 42.4866,-90.6565 42.4861,-90.6565 42.4859,-90.6565 42.4851,-90.6565 42.4845,-90.6565 42.4839,-90.6563 42.4829,-90.656 42.482,-90.6558 42.4814,-90.6554 42.4809,-90.6549 42.48,-90.6544 42.4793,-90.6541 42.4788,-90.6538 42.4781,-90.6532 42.4771,-90.6527 42.4765,-90.6516 42.4756,-90.651 42.4749,-90.6502 42.4742,-90.6494 42.4737,-90.6485 42.4728,-90.6472 42.4718,-90.6467 42.4716,-90.6466 42.4715,-90.6464 42.4714,-90.6457 42.4709,-90.6447 42.4704,-90.6438 42.4699,-90.6429 42.4694,-90.6415 42.4684,-90.6408 42.468,-90.6396 42.4672,-90.6389 42.4668,-90.6381 42.4664,-90.6374 42.4661,-90.6366 42.4658,-90.6359 42.4655,-90.6352 42.4651,-90.634 42.4646,-90.6328 42.464,-90.6319 42.4636,-90.6307 42.4629,-90.63 42.4626,-90.6288 42.4619,-90.6276 42.4612,-90.6269 42.4607,-90.6268 42.4607,-90.6262 42.4604,-90.6256 42.4598,-90.625 42.4592,-90.6241 42.4586,-90.6231 42.4581,-90.6222 42.4577,-90.6213 42.4571,-90.6202 42.4563,-90.6191 42.4557,-90.6179 42.4549,-90.6175 42.4547,-90.617 42.4545,-90.6162 42.4541,-90.6155 42.4538,-90.6143 42.4533,-90.6131 42.4528,-90.6122 42.4527,-90.6112 42.4525,-90.6103 42.4524,-90.6092 42.4522,-90.6079 42.4517,-90.6071 42.4515,-90.6066 42.4514,-90.6058 42.4512,-90.6049 42.451,-90.604 42.4508,-90.6031 42.4505,-90.6021 42.4503,-90.6011 42.4501,-90.6002 42.4498,-90.5991 42.4496,-90.5981 42.4495,-90.5972 42.4492,-90.5965 42.449,-90.5957 42.4489,-90.5948 42.4487,-90.5939 42.4485,-90.593 42.4483,-90.5923 42.448,-90.5912 42.4476,-90.5904 42.4474,-90.5895 42.4469,-90.5883 42.4465,-90.5879 42.4463,-90.5871 42.446,-90.5862 42.4456,-90.5854 42.4453,-90.5847 42.445,-90.584 42.4447,-90.5827 42.4443,-90.5817 42.444,-90.5805 42.4436,-90.5793 42.4432,-90.5781 42.4429,-90.5773 42.4426,-90.5763 42.4424,-90.5755 42.4422,-90.5741 42.4419,-90.5731 42.4417,-90.5719 42.4416,-90.5709 42.4415,-90.57 42.4413,-90.5689 42.4408,-90.5684 42.4406,-90.5679 42.4404,-90.5672 42.4401,-90.5667 42.4399,-90.566 42.4392,-90.5648 42.4383,-90.564 42.4377,-90.5631 42.4369,-90.5626 42.4363,-90.5619 42.4355,-90.5612 42.4347,-90.5608 42.4341,-90.5604 42.4335,-90.56 42.4329,-90.5597 42.4323,-90.5594 42.4318,-90.5591 42.4312,-90.559 42.4306,-90.5587 42.43,-90.5586 42.4292,-90.5584 42.4287,-90.5584 42.428,-90.5582 42.4273,-90.5582 42.4268,-90.5581 42.4258,-90.5581 42.4255,-90.5581 42.4252,-90.5581 42.4247,-90.5582 42.4241,-90.5581 42.4236,-90.5581 42.4227,-90.5579 42.4221,-90.5579 42.4213,-90.5579 42.4203,-90.5578 42.4197,-90.5573 42.4189,-90.5567 42.4182,-90.5563 42.4174,-90.5557 42.4169,-90.5547 42.416,-90.5539 42.4156,-90.5532 42.415,-90.5524 42.4146,-90.5512 42.4141,-90.5502 42.4134,-90.5493 42.413,-90.5489 42.4129,-90.5484 42.4127,-90.5477 42.4125,-90.5469 42.4124,-90.5461 42.4122,-90.5453 42.4121,-90.5444 42.412,-90.5435 42.4117,-90.5423 42.4116,-90.5414 42.4112,-90.541 42.4111,-90.5399 42.4108,-90.5389 42.4104,-90.5379 42.41,-90.5371 42.4098,-90.5364 42.4095,-90.535 42.409,-90.5341 42.4088,-90.5334 42.4085,-90.5325 42.4081,-90.5316 42.4079,-90.5309 42.4076,-90.5301 42.4074,-90.5298 42.4073,-90.5292 42.407,-90.5279 42.4066,-90.5267 42.4062,-90.5255 42.4057,-90.5243 42.4053,-90.5231 42.4047,-90.5224 42.4044,-90.5213 42.404,-90.5205 42.4036,-90.5193 42.4033,-90.5184 42.4031,-90.5172 42.4028,-90.5163 42.4026,-90.5156 42.4024,-90.5148 42.402,-90.5135 42.4015,-90.5124 42.4011,-90.5113 42.4006,-90.5103 42.4002,-90.5102 42.4002,-90.5092 42.3998,-90.508 42.3994,-90.507 42.399,-90.5058 42.3985,-90.5046 42.398,-90.5035 42.3974,-90.5028 42.3971,-90.502 42.3968,-90.5015 42.3962,-90.5007 42.3959,-90.5003 42.3954,-90.4993 42.3949,-90.4983 42.3942,-90.4974 42.3937,-90.4965 42.393,-90.4958 42.3926,-90.495 42.3918,-90.4944 42.3911,-90.4936 42.3902,-90.4928 42.3896,-90.4919 42.3889,-90.491 42.3881,-90.49 42.3875,-90.4899 42.3875,-90.4891 42.3869,-90.4882 42.3862,-90.4872 42.3857,-90.4863 42.3852,-90.4856 42.3846,-90.4847 42.3845,-90.4833 42.3845,-90.4823 42.3845,-90.4815 42.3845,-90.4805 42.3845,-90.4795 42.3844,-90.4787 42.3842,-90.4775 42.384,-90.4768 42.3836,-90.4761 42.3834,-90.4753 42.3829,-90.4746 42.3822,-90.4741 42.3819,-90.4899 42.3819,-90.4909 42.3818,-90.4911 42.3818,-90.4999 42.3819,-90.5002 42.3819,-90.5003 42.3819,-90.5097 42.3818,-90.5111 42.3818,-90.5293 42.382,-90.5307 42.382,-90.5487 42.3822,-90.5488 42.3822,-90.5683 42.3823,-90.5879 42.3822,-90.6077 42.3822,-90.6274 42.3821,-90.6467 42.3822,-90.6469 42.3822,-90.6651 42.3823,-90.665 42.3674,-90.6651 42.3529,-90.6651 42.3384,-90.6652 42.3239,-90.6652 42.3238,-90.6655 42.3093,-90.6654 42.2947,-90.6656 42.2947,-90.6853 42.2947,-90.705 42.2948,-90.7242 42.2949,-90.7244 42.2949,-90.7436 42.2948,-90.7633 42.295,-90.7635 42.295,-90.7819 42.295,-90.8014 42.2951,-90.8213 42.2951,-90.8403 42.2949,-90.8404 42.2949,-90.86 42.295,-90.8797 42.2948,-90.8799 42.2948,-90.8981 42.2948,-90.8982 42.2948,-90.9176 42.295,-90.9178 42.295,-90.9179 42.295,-90.9369 42.2952,-90.9563 42.2954,-90.9566 42.2954,-90.9757 42.2955,-90.9761 42.2955,-90.9958 42.2955,-90.996 42.2955,-91.0138 42.2957,-91.0334 42.2957,-91.053 42.2957,-91.0726 42.2957,-91.0727 42.2957,-91.0729 42.2957,-91.0911 42.2956,-91.0916 42.2956,-91.111 42.2957,-91.1115 42.2957,-91.1116 42.2957,-91.1248 42.2957,-91.1256 42.2957,-91.1303 42.2956,-91.1303 42.2957,-91.1306 42.3103,-91.1305 42.3248,-91.1305 42.3394,-91.1305 42.354,-91.1305 42.3686,-91.1303 42.3827,-91.1304 42.3973,-91.1305 42.4117,-91.1306 42.4264,-91.1306 42.4409,-91.1307 42.4553,-91.1307 42.4713,-91.134 42.4713,-91.1341 42.4859,-91.1343 42.5004,-91.1345 42.5149,-91.1345 42.515,-91.1347 42.5295,-91.1346 42.5441,-91.1346 42.5586,-91.1345 42.573,-91.1341 42.5877,-91.1338 42.6021,-91.1338 42.6022,-91.1335 42.6167,-91.1328 42.6313,-91.133 42.6459,-91.1133 42.6459,-91.0937 42.6463,-91.0935 42.6463,-91.0934 42.6463,-91.074 42.6464,-91.0541 42.6465,-91.0346 42.6467,-91.0344 42.6467,-91.0149 42.6467,-90.9961 42.6464,-90.996 42.6464,-90.9763 42.6461,-90.9565 42.646,-90.9367 42.646,-90.9168 42.6459,-90.8971 42.6456,-90.8973 42.6602,-90.897 42.6751,-90.8962 42.6748))	(-91.1347,42.2947,-90.4741,42.6751)
19183	Washington	22143	POLYGON ((-91.522 41.5106,-91.5223 41.5105,-91.5225 41.5105,-91.5226 41.5104,-91.5229 41.5104,-91.523 41.5105,-91.5233 41.5105,-91.5236 41.5105,-91.524 41.5104,-91.5242 41.5103,-91.5244 41.5103,-91.5246 41.5103,-91.5251 41.5103,-91.5255 41.5102,-91.5259 41.51,-91.5262 41.5099,-91.5265 41.5098,-91.5268 41.5096,-91.5269 41.5095,-91.5271 41.5093,-91.5274 41.5089,-91.5275 41.5087,-91.5277 41.5085,-91.5278 41.5082,-91.5278 41.5079,-91.5276 41.5077,-91.5276 41.5076,-91.5275 41.5074,-91.5273 41.5072,-91.5272 41.5071,-91.5271 41.5069,-91.5269 41.5068,-91.5267 41.5066,-91.5266 41.5065,-91.5264 41.5064,-91.5261 41.5062,-91.5258 41.5061,-91.5255 41.506,-91.5252 41.5058,-91.5248 41.5055,-91.5245 41.5053,-91.524 41.5052,-91.5237 41.5051,-91.5233 41.5049,-91.5229 41.5048,-91.5225 41.5047,-91.5223 41.5045,-91.5221 41.5043,-91.522 41.5041,-91.5214 41.5041,-91.5211 41.5038,-91.5208 41.5037,-91.5205 41.5036,-91.5201 41.5034,-91.5197 41.5033,-91.5196 41.5031,-91.5194 41.5027,-91.5191 41.5023,-91.5193 41.502,-91.5193 41.5018,-91.5194 41.5016,-91.5194 41.5014,-91.5192 41.501,-91.5191 41.5008,-91.5192 41.5007,-91.5194 41.5006,-91.5198 41.5005,-91.5198 41.5003,-91.5198 41.5001,-91.5198 41.5,-91.5198 41.4997,-91.5199 41.4994,-91.5199 41.4992,-91.5199 41.499,-91.5198 41.4988,-91.5196 41.4987,-91.5196 41.4985,-91.5195 41.4984,-91.5194 41.4982,-91.5192 41.498,-91.5191 41.4977,-91.5188 41.4975,-91.5186 41.4974,-91.5183 41.4973,-91.518 41.4972,-91.5176 41.4972,-91.5175 41.4971,-91.5172 41.4969,-91.5167 41.4969,-91.5164 41.4969,-91.516 41.4971,-91.5155 41.497,-91.5151 41.497,-91.5148 41.4972,-91.5146 41.4973,-91.5144 41.4973,-91.5142 41.4975,-91.5139 41.4977,-91.5138 41.4978,-91.5136 41.498,-91.5133 41.4982,-91.5132 41.4983,-91.513 41.4987,-91.5129 41.4989,-91.5128 41.4991,-91.5126 41.4994,-91.5125 41.4996,-91.5125 41.4998,-91.5125 41.5,-91.5123 41.5002,-91.5123 41.5005,-91.5122 41.5007,-91.5121 41.5008,-91.5119 41.501,-91.5118 41.5012,-91.5116 41.5014,-91.5115 41.5016,-91.5114 41.5017,-91.5112 41.5018,-91.5111 41.5019,-91.511 41.5021,-91.5106 41.5023,-91.5105 41.5025,-91.5102 41.5026,-91.5099 41.5026,-91.5095 41.5027,-91.5092 41.5027,-91.5084 41.5026,-91.5082 41.5025,-91.508 41.5023,-91.5077 41.5021,-91.5076 41.502,-91.5076 41.5018,-91.5074 41.5016,-91.5074 41.5014,-91.5074 41.5012,-91.5072 41.501,-91.5072 41.5008,-91.5072 41.5005,-91.5074 41.5003,-91.5075 41.5002,-91.5076 41.5,-91.5078 41.4997,-91.5079 41.4996,-91.5082 41.4996,-91.5082 41.4994,-91.5084 41.4992,-91.5085 41.4991,-91.5088 41.499,-91.509 41.4989,-91.5091 41.4988,-91.5094 41.4988,-91.5098 41.4987,-91.51 41.4985,-91.5103 41.4983,-91.5104 41.4981,-91.5105 41.4978,-91.5107 41.4977,-91.5107 41.4975,-91.5109 41.4973,-91.5109 41.4971,-91.5109 41.4969,-91.5109 41.4967,-91.511 41.4965,-91.511 41.4962,-91.511 41.4961,-91.511 41.4959,-91.5112 41.4956,-91.5112 41.4954,-91.5111 41.4951,-91.5109 41.4949,-91.5108 41.4947,-91.5106 41.4945,-91.5105 41.4943,-91.5103 41.494,-91.5099 41.4938,-91.5096 41.4936,-91.5093 41.4934,-91.5089 41.4932,-91.5086 41.4932,-91.5083 41.493,-91.508 41.4929,-91.5079 41.4928,-91.5073 41.4928,-91.5067 41.4929,-91.5065 41.4929,-91.5064 41.493,-91.5061 41.493,-91.506 41.4931,-91.5057 41.4933,-91.5055 41.4934,-91.5052 41.4935,-91.5051 41.4937,-91.5048 41.4938,-91.5045 41.4938,-91.5044 41.4937,-91.5042 41.4935,-91.5041 41.4934,-91.5039 41.4933,-91.5037 41.4932,-91.5037 41.493,-91.5036 41.4928,-91.5035 41.4924,-91.5033 41.4922,-91.5033 41.492,-91.5033 41.4918,-91.5032 41.4915,-91.5032 41.4914,-91.503 41.4911,-91.503 41.4909,-91.5029 41.4907,-91.5028 41.4903,-91.5025 41.4901,-91.5023 41.49,-91.5022 41.4897,-91.5021 41.4895,-91.5019 41.4894,-91.5018 41.489,-91.5016 41.4888,-91.5016 41.4886,-91.5016 41.4882,-91.5016 41.488,-91.5016 41.4877,-91.5018 41.4875,-91.5019 41.4873,-91.5019 41.4871,-91.5021 41.487,-91.5023 41.4867,-91.5023 41.4865,-91.5024 41.4861,-91.5024 41.4859,-91.5024 41.4857,-91.5026 41.4856,-91.5027 41.4854,-91.5027 41.4852,-91.5028 41.485,-91.5029 41.4847,-91.5027 41.4845,-91.5027 41.4843,-91.5023 41.4841,-91.5021 41.4838,-91.5019 41.4835,-91.5017 41.4833,-91.5017 41.4831,-91.5016 41.4828,-91.5016 41.4826,-91.5016 41.4825,-91.5016 41.4823,-91.5016 41.482,-91.5016 41.4819,-91.5016 41.4817,-91.5016 41.4816,-91.5016 41.4815,-91.5016 41.4812,-91.5016 41.4808,-91.5018 41.4805,-91.5019 41.4803,-91.5021 41.48,-91.5021 41.4796,-91.5021 41.4794,-91.5019 41.479,-91.5019 41.4788,-91.5018 41.4786,-91.5018 41.4784,-91.5016 41.4782,-91.5016 41.478,-91.5016 41.4777,-91.5018 41.4776,-91.5021 41.4775,-91.5023 41.4772,-91.5025 41.4771,-91.5027 41.477,-91.503 41.4769,-91.5031 41.4768,-91.5034 41.4768,-91.5036 41.4767,-91.5037 41.4766,-91.5041 41.4766,-91.5044 41.4765,-91.5047 41.4764,-91.505 41.4764,-91.5055 41.4765,-91.5057 41.4765,-91.506 41.4766,-91.5065 41.4766,-91.5072 41.4766,-91.5075 41.4766,-91.5077 41.4767,-91.5078 41.4768,-91.5079 41.4768,-91.5083 41.4769,-91.5085 41.4769,-91.5087 41.477,-91.5088 41.4772,-91.5093 41.4772,-91.5096 41.4772,-91.5098 41.4772,-91.51 41.477,-91.5103 41.477,-91.5105 41.477,-91.5109 41.477,-91.511 41.477,-91.5113 41.4769,-91.5115 41.4769,-91.5117 41.4769,-91.512 41.4768,-91.5122 41.4766,-91.5125 41.4764,-91.5128 41.4763,-91.5132 41.4761,-91.5135 41.4758,-91.5137 41.4755,-91.5138 41.4753,-91.5138 41.475,-91.514 41.4749,-91.5141 41.4746,-91.5141 41.4742,-91.5141 41.474,-91.5141 41.4736,-91.5141 41.4732,-91.514 41.4729,-91.514 41.4727,-91.514 41.4725,-91.514 41.4723,-91.5142 41.4721,-91.5142 41.4719,-91.5143 41.4717,-91.5143 41.4715,-91.5144 41.4714,-91.5146 41.4712,-91.5148 41.4709,-91.5149 41.4707,-91.5151 41.4705,-91.5152 41.4702,-91.5154 41.4702,-91.5155 41.4699,-91.5158 41.4697,-91.5161 41.4695,-91.5162 41.4693,-91.5164 41.4692,-91.5164 41.4688,-91.5164 41.4686,-91.5162 41.4685,-91.5162 41.4683,-91.5161 41.4681,-91.516 41.4679,-91.5158 41.4676,-91.5155 41.4675,-91.5154 41.4674,-91.5152 41.4673,-91.5151 41.4672,-91.5147 41.467,-91.5144 41.4668,-91.5139 41.4668,-91.5135 41.4666,-91.513 41.4665,-91.5126 41.4663,-91.5123 41.4662,-91.5122 41.4661,-91.5119 41.4661,-91.5116 41.4659,-91.5113 41.4658,-91.511 41.4657,-91.5107 41.4657,-91.5106 41.4656,-91.5104 41.4655,-91.5101 41.4654,-91.5099 41.4653,-91.5097 41.4652,-91.5096 41.465,-91.5094 41.4648,-91.5093 41.4647,-91.5091 41.4646,-91.509 41.4644,-91.5086 41.4641,-91.5083 41.4638,-91.5081 41.4637,-91.5078 41.4635,-91.5074 41.4631,-91.507 41.463,-91.5067 41.4626,-91.5064 41.4623,-91.5061 41.462,-91.5058 41.4618,-91.5055 41.4616,-91.5052 41.4614,-91.5049 41.4611,-91.5046 41.4611,-91.5044 41.4609,-91.5039 41.4608,-91.5036 41.4607,-91.5033 41.4606,-91.5032 41.4606,-91.5029 41.4606,-91.5026 41.4605,-91.5022 41.4605,-91.5019 41.4605,-91.5013 41.4604,-91.5006 41.4601,-91.4999 41.4598,-91.4996 41.4597,-91.4995 41.4596,-91.4994 41.4595,-91.4992 41.4592,-91.499 41.4589,-91.499 41.4586,-91.499 41.4584,-91.499 41.4582,-91.4992 41.458,-91.4992 41.4577,-91.4994 41.4574,-91.4995 41.4572,-91.4996 41.4571,-91.4996 41.4569,-91.4996 41.4566,-91.4998 41.4564,-91.4998 41.4562,-91.4999 41.456,-91.4999 41.4558,-91.4999 41.4555,-91.4999 41.4553,-91.4998 41.4552,-91.4998 41.4549,-91.4996 41.4548,-91.4995 41.4544,-91.4993 41.4543,-91.499 41.4542,-91.4989 41.4541,-91.4987 41.4539,-91.4986 41.4537,-91.4986 41.4536,-91.4986 41.4534,-91.4986 41.4532,-91.4987 41.4531,-91.4989 41.453,-91.499 41.4529,-91.4993 41.4528,-91.4994 41.4527,-91.4997 41.4525,-91.4998 41.4525,-91.5002 41.4524,-91.5005 41.4524,-91.5008 41.4523,-91.5011 41.4523,-91.5017 41.4522,-91.502 41.4521,-91.5021 41.452,-91.5021 41.4518,-91.5023 41.4517,-91.5025 41.4516,-91.5025 41.4514,-91.5026 41.4511,-91.5026 41.4509,-91.5028 41.4508,-91.5028 41.4506,-91.5028 41.4504,-91.5029 41.4503,-91.5029 41.45,-91.5029 41.4498,-91.5028 41.4496,-91.5028 41.4494,-91.5028 41.4491,-91.5028 41.4489,-91.5026 41.4487,-91.5025 41.4486,-91.5022 41.4483,-91.5021 41.448,-91.5019 41.4478,-91.5018 41.4477,-91.5016 41.4476,-91.5015 41.4474,-91.5014 41.4471,-91.5012 41.4468,-91.5009 41.4467,-91.5005 41.4466,-91.5003 41.4465,-91.5 41.4464,-91.4998 41.4463,-91.4997 41.4462,-91.4996 41.4461,-91.4992 41.4459,-91.499 41.4457,-91.4987 41.4454,-91.4984 41.4452,-91.4981 41.4451,-91.4979 41.445,-91.4976 41.4449,-91.4975 41.4448,-91.4972 41.4447,-91.4971 41.4446,-91.4967 41.4443,-91.4966 41.4442,-91.4965 41.444,-91.4963 41.4439,-91.4962 41.4438,-91.496 41.4436,-91.4957 41.4434,-91.4954 41.4432,-91.4951 41.443,-91.4947 41.4428,-91.4942 41.4426,-91.4938 41.4425,-91.4935 41.4423,-91.4931 41.4422,-91.4928 41.4419,-91.4923 41.4417,-91.4921 41.4415,-91.4919 41.4413,-91.4918 41.4412,-91.4916 41.4409,-91.4915 41.4407,-91.4912 41.4404,-91.491 41.4402,-91.4909 41.4401,-91.4907 41.4398,-91.4904 41.4394,-91.4903 41.439,-91.4901 41.4387,-91.49 41.4385,-91.4898 41.4383,-91.4897 41.4381,-91.4895 41.4377,-91.4893 41.4372,-91.4891 41.4369,-91.4889 41.4366,-91.4886 41.4361,-91.4884 41.4358,-91.4882 41.4354,-91.488 41.4351,-91.4879 41.4347,-91.4878 41.4345,-91.4876 41.4341,-91.4875 41.4339,-91.4873 41.4336,-91.487 41.4333,-91.487 41.433,-91.4869 41.4328,-91.4867 41.4325,-91.4866 41.4322,-91.4864 41.4318,-91.4863 41.4315,-91.4861 41.4311,-91.486 41.4307,-91.4858 41.4303,-91.4858 41.43,-91.4857 41.4297,-91.4854 41.4291,-91.4853 41.429,-91.4851 41.4287,-91.4848 41.4286,-91.4846 41.4284,-91.4844 41.4282,-91.4842 41.4281,-91.4842 41.4279,-91.4844 41.4278,-91.4844 41.4273,-91.4844 41.427,-91.4844 41.4268,-91.4842 41.4265,-91.4842 41.4261,-91.4842 41.4259,-91.4842 41.4257,-91.4842 41.4255,-91.4842 41.4253,-91.4842 41.4249,-91.4841 41.4247,-91.4841 41.4244,-91.4841 41.424,-91.4841 41.424,-91.4841 41.4239,-91.4841 41.4093,-91.4841 41.4092,-91.4843 41.3948,-91.4843 41.3804,-91.4843 41.3803,-91.4843 41.3658,-91.4844 41.3512,-91.4847 41.3368,-91.4849 41.3223,-91.4849 41.3222,-91.4849 41.3077,-91.485 41.2932,-91.4852 41.2786,-91.4853 41.2641,-91.4853 41.2496,-91.4856 41.2351,-91.486 41.2207,-91.486 41.2205,-91.486 41.206,-91.486 41.1916,-91.486 41.1915,-91.4859 41.177,-91.4859 41.1625,-91.4859 41.1624,-91.5001 41.1625,-91.5002 41.1625,-91.5004 41.1625,-91.5049 41.1626,-91.5053 41.1626,-91.5241 41.1627,-91.5245 41.1627,-91.5433 41.163,-91.5443 41.163,-91.5445 41.163,-91.5627 41.1632,-91.5634 41.1632,-91.582 41.1634,-91.5828 41.1634,-91.6009 41.1636,-91.6203 41.1634,-91.6253 41.1633,-91.6254 41.1633,-91.6394 41.1632,-91.6395 41.1632,-91.6586 41.1629,-91.6588 41.1629,-91.6734 41.163,-91.6778 41.163,-91.6779 41.163,-91.6851 41.163,-91.697 41.1629,-91.6972 41.1629,-91.7044 41.1629,-91.7161 41.163,-91.7354 41.1627,-91.7544 41.1623,-91.7734 41.162,-91.7736 41.162,-91.7928 41.1618,-91.8117 41.1617,-91.812 41.1617,-91.8304 41.1616,-91.8497 41.1617,-91.8689 41.1619,-91.8882 41.1624,-91.8883 41.1624,-91.9075 41.1629,-91.9076 41.1629,-91.927 41.1632,-91.9459 41.1637,-91.9453 41.1779,-91.9448 41.1928,-91.9448 41.1929,-91.9447 41.2076,-91.9446 41.2222,-91.9446 41.2223,-91.9447 41.2368,-91.9448 41.2516,-91.9454 41.266,-91.9454 41.2661,-91.9458 41.2804,-91.9461 41.2951,-91.9464 41.3096,-91.9467 41.3241,-91.9467 41.3242,-91.9469 41.3378,-91.9467 41.3522,-91.9467 41.3523,-91.9467 41.3667,-91.9467 41.3812,-91.9467 41.3813,-91.9465 41.3958,-91.9464 41.4103,-91.9467 41.4249,-91.9464 41.4393,-91.9464 41.4394,-91.9462 41.4538,-91.946 41.4684,-91.946 41.4685,-91.9459 41.4831,-91.946 41.4974,-91.9458 41.5111,-91.9262 41.5111,-91.9254 41.5111,-91.9063 41.5112,-91.8871 41.5112,-91.8678 41.5112,-91.8485 41.5116,-91.8484 41.5116,-91.8294 41.5117,-91.8108 41.5117,-91.8102 41.5117,-91.7913 41.5118,-91.7907 41.5118,-91.7719 41.5117,-91.7714 41.5117,-91.7527 41.5116,-91.7526 41.5116,-91.7523 41.5116,-91.7377 41.5114,-91.7332 41.5113,-91.733 41.5113,-91.7137 41.5113,-91.6955 41.5111,-91.6951 41.5111,-91.6762 41.5111,-91.676 41.5111,-91.6756 41.5111,-91.6567 41.5108,-91.6566 41.5108,-91.6374 41.5107,-91.6181 41.5105,-91.5989 41.5105,-91.5803 41.5106,-91.5795 41.5106,-91.5608 41.5109,-91.5602 41.5109,-91.5417 41.511,-91.5415 41.511,-91.5411 41.511,-91.541 41.511,-91.522 41.5109,-91.522 41.5106))	(-91.9469,41.1616,-91.4841,41.5118)
19101	Jefferson	18077	POLYGON ((-91.7161 41.163,-91.7161 41.1629,-91.7161 41.1628,-91.7162 41.1485,-91.7166 41.1341,-91.7166 41.134,-91.7166 41.1251,-91.7166 41.125,-91.7166 41.1194,-91.7166 41.105,-91.7168 41.0906,-91.7171 41.0761,-91.7171 41.0759,-91.7173 41.0614,-91.7174 41.0468,-91.7179 41.0322,-91.7179 41.0177,-91.7179 41.0032,-91.718 41.0031,-91.7182 40.9886,-91.7183 40.9739,-91.718 40.9594,-91.718 40.9449,-91.7179 40.9303,-91.7181 40.9157,-91.7182 40.9012,-91.7182 40.9011,-91.7374 40.9009,-91.7375 40.9009,-91.7568 40.9006,-91.7759 40.9007,-91.795 40.9009,-91.8138 40.901,-91.814 40.901,-91.8329 40.9012,-91.8521 40.9013,-91.8522 40.9013,-91.8712 40.9012,-91.8907 40.9014,-91.9101 40.9015,-91.9291 40.9012,-91.9485 40.901,-91.9486 40.901,-91.9677 40.9007,-91.9869 40.9004,-92.006 40.9002,-92.0064 40.9002,-92.0253 40.9001,-92.0256 40.9001,-92.0446 40.9001,-92.0449 40.9001,-92.0644 40.9,-92.0835 40.9,-92.0836 40.9,-92.1022 40.9,-92.1026 40.9,-92.1212 40.8999,-92.1215 40.8999,-92.1216 40.8999,-92.1252 40.9,-92.1255 40.9,-92.1406 40.9,-92.1407 40.9,-92.1592 40.9,-92.1596 40.9,-92.179 40.8999,-92.179 40.9143,-92.1791 40.9286,-92.1791 40.9287,-92.1791 40.9432,-92.1791 40.9433,-92.1792 40.9578,-92.1792 40.9723,-92.1791 40.9881,-92.1795 41.0027,-92.1795 41.0172,-92.1797 41.0316,-92.1797 41.0317,-92.1797 41.046,-92.1797 41.0462,-92.1799 41.0607,-92.18 41.0762,-92.18 41.0763,-92.1801 41.0907,-92.18 41.1051,-92.1801 41.1194,-92.18 41.1341,-92.1802 41.1485,-92.1802 41.1626,-92.1593 41.1626,-92.1402 41.1628,-92.1211 41.163,-92.102 41.1629,-92.0828 41.163,-92.0637 41.1632,-92.0635 41.1632,-92.043 41.1636,-92.0238 41.1636,-92.0234 41.1636,-92.0041 41.1638,-91.9844 41.1637,-91.9841 41.1637,-91.9652 41.1639,-91.965 41.1639,-91.9646 41.1639,-91.9459 41.1637,-91.927 41.1632,-91.9076 41.1629,-91.9075 41.1629,-91.8883 41.1624,-91.8882 41.1624,-91.8689 41.1619,-91.8497 41.1617,-91.8304 41.1616,-91.812 41.1617,-91.8117 41.1617,-91.7928 41.1618,-91.7736 41.162,-91.7734 41.162,-91.7544 41.1623,-91.7354 41.1627,-91.7161 41.163,-91.7161 41.163))	(-92.1802,40.8999,-91.7161,41.1639)
19095	Iowa	16207	POLYGON ((-91.8313 41.8473,-91.8312 41.8328,-91.8311 41.8182,-91.8309 41.8038,-91.8309 41.8037,-91.8311 41.7893,-91.8314 41.7746,-91.8314 41.7745,-91.832 41.7595,-91.8323 41.7502,-91.8323 41.7498,-91.8324 41.7451,-91.833 41.7304,-91.8336 41.7159,-91.8338 41.7014,-91.8338 41.7012,-91.8341 41.6868,-91.8341 41.6867,-91.8338 41.673,-91.8338 41.6728,-91.8335 41.6585,-91.8332 41.644,-91.8331 41.6297,-91.8331 41.6296,-91.833 41.615,-91.833 41.6006,-91.8329 41.6006,-91.827 41.6005,-91.8275 41.5841,-91.8279 41.5697,-91.8281 41.5552,-91.8286 41.5406,-91.829 41.5262,-91.8294 41.5117,-91.8484 41.5116,-91.8485 41.5116,-91.8678 41.5112,-91.8871 41.5112,-91.9063 41.5112,-91.9254 41.5111,-91.9262 41.5111,-91.9458 41.5111,-91.9648 41.5108,-91.9649 41.5108,-91.9651 41.5108,-91.9842 41.5107,-92.0034 41.5105,-92.0228 41.5106,-92.0423 41.5106,-92.0651 41.5105,-92.0844 41.5102,-92.1037 41.5103,-92.123 41.5102,-92.1232 41.5102,-92.1251 41.5102,-92.1254 41.5102,-92.1422 41.5102,-92.1613 41.5101,-92.1615 41.5101,-92.1616 41.5101,-92.1818 41.5101,-92.2009 41.51,-92.2199 41.5099,-92.2393 41.51,-92.2586 41.5099,-92.278 41.5098,-92.2973 41.5098,-92.2975 41.5243,-92.2976 41.5388,-92.2977 41.5533,-92.2977 41.5676,-92.2977 41.5821,-92.2978 41.6015,-92.2994 41.6016,-92.2996 41.6016,-92.2996 41.6161,-92.2996 41.6162,-92.2997 41.6249,-92.2997 41.6252,-92.2997 41.6305,-92.2997 41.6306,-92.2999 41.6452,-92.3001 41.6597,-92.3001 41.6742,-92.2999 41.6891,-92.3 41.7034,-92.2999 41.718,-92.2997 41.7325,-92.2997 41.7327,-92.2997 41.7472,-92.2994 41.7617,-92.2994 41.7618,-92.299 41.7762,-92.2989 41.7906,-92.2989 41.7907,-92.2987 41.8051,-92.2986 41.8195,-92.2986 41.8196,-92.2984 41.834,-92.2984 41.8485,-92.298 41.8629,-92.2781 41.8629,-92.278 41.8629,-92.259 41.8626,-92.2589 41.8626,-92.2394 41.8628,-92.2201 41.8628,-92.2007 41.8628,-92.1812 41.8627,-92.1811 41.8627,-92.1614 41.8626,-92.1609 41.8626,-92.1421 41.8627,-92.1418 41.8627,-92.1253 41.8628,-92.125 41.8628,-92.1227 41.8627,-92.1225 41.8627,-92.1223 41.8627,-92.1032 41.8627,-92.0838 41.8628,-92.0837 41.8628,-92.0835 41.8628,-92.0646 41.8629,-92.045 41.8627,-92.0257 41.8626,-92.0256 41.8626,-92.0061 41.8623,-91.9863 41.8622,-91.9671 41.8621,-91.967 41.8621,-91.9478 41.8619,-91.9284 41.8618,-91.9282 41.8618,-91.9091 41.8617,-91.9089 41.8617,-91.8896 41.8616,-91.8753 41.8616,-91.8752 41.8617,-91.8702 41.8618,-91.8508 41.8617,-91.8506 41.8617,-91.8315 41.8618,-91.8313 41.8473))	(-92.3001,41.5098,-91.827,41.8629)
19135	Monroe	7863	POLYGON ((-92.6401 41.1475,-92.6398 41.1329,-92.6403 41.1184,-92.6401 41.104,-92.6401 41.0895,-92.6401 41.0751,-92.6401 41.075,-92.64 41.0595,-92.6399 41.0451,-92.6399 41.0306,-92.6399 41.0305,-92.6397 41.0163,-92.6397 41.0162,-92.6396 41.0016,-92.6396 41.0015,-92.6397 40.9872,-92.6396 40.9714,-92.6397 40.957,-92.6397 40.9425,-92.6395 40.928,-92.6395 40.9135,-92.6393 40.8991,-92.6393 40.899,-92.6394 40.899,-92.6585 40.8991,-92.6777 40.8989,-92.6967 40.899,-92.716 40.8989,-92.7349 40.8987,-92.7542 40.8986,-92.7731 40.8986,-92.7732 40.8986,-92.7922 40.8986,-92.7923 40.8986,-92.7924 40.8986,-92.8114 40.8987,-92.8116 40.8987,-92.8117 40.8987,-92.8307 40.8986,-92.8308 40.8986,-92.8495 40.8986,-92.8498 40.8986,-92.8689 40.8984,-92.869 40.8984,-92.8751 40.8984,-92.8757 40.8984,-92.8877 40.8984,-92.888 40.8984,-92.9067 40.8985,-92.926 40.8984,-92.945 40.8985,-92.9452 40.8985,-92.9454 40.8985,-92.9642 40.8984,-92.9644 40.8984,-92.9645 40.8984,-92.9834 40.8984,-93.0023 40.8983,-93.0215 40.8985,-93.0406 40.8984,-93.0407 40.8984,-93.0598 40.8984,-93.079 40.8984,-93.0976 40.8982,-93.0976 40.8983,-93.0979 40.9128,-93.098 40.9273,-93.0981 40.9418,-93.0982 40.9564,-93.0982 40.9708,-93.0984 40.9864,-93.0985 41.0011,-93.0986 41.0154,-93.0986 41.0155,-93.0987 41.03,-93.0988 41.0446,-93.0988 41.0448,-93.0988 41.0592,-93.0987 41.0743,-93.0987 41.0888,-93.0989 41.1034,-93.0989 41.1035,-93.0989 41.1179,-93.0989 41.1321,-93.0989 41.1323,-93.0992 41.1468,-93.0993 41.1607,-93.0809 41.1607,-93.0615 41.1608,-93.0424 41.1608,-93.0422 41.1608,-93.0233 41.1608,-93.0041 41.1608,-93.004 41.1608,-93.0007 41.1608,-93.0002 41.1608,-92.9849 41.1608,-92.9662 41.1608,-92.9469 41.1608,-92.9279 41.1609,-92.9277 41.1609,-92.9087 41.161,-92.8894 41.1611,-92.87 41.1609,-92.8508 41.1609,-92.8318 41.161,-92.8316 41.161,-92.8126 41.1611,-92.8124 41.1611,-92.7934 41.1612,-92.7742 41.161,-92.774 41.161,-92.755 41.1612,-92.736 41.1612,-92.7172 41.1612,-92.717 41.1612,-92.7169 41.1612,-92.6979 41.1613,-92.6978 41.1613,-92.6787 41.1613,-92.6593 41.1612,-92.6591 41.1612,-92.6403 41.1613,-92.6401 41.1613,-92.6401 41.1475))	(-93.0993,40.8982,-92.6393,41.1613)
19099	Jasper	36891	POLYGON ((-93.3285 41.8631,-93.3093 41.8631,-93.2898 41.863,-93.2704 41.8629,-93.2511 41.8627,-93.251 41.8627,-93.25 41.8628,-93.232 41.8627,-93.2318 41.8627,-93.213 41.8628,-93.2129 41.8628,-93.1937 41.8629,-93.1935 41.8629,-93.1742 41.8627,-93.174 41.8627,-93.1549 41.8627,-93.1548 41.8627,-93.1356 41.8626,-93.1255 41.8627,-93.1252 41.8627,-93.1162 41.8627,-93.0969 41.8626,-93.0775 41.8626,-93.0581 41.8626,-93.0387 41.8626,-93.0193 41.8627,-93.0191 41.8627,-93.0 41.8625,-92.9999 41.8625,-92.9806 41.8625,-92.9802 41.8625,-92.9612 41.8624,-92.9419 41.8625,-92.9418 41.8625,-92.9413 41.8625,-92.9224 41.8625,-92.903 41.8624,-92.9028 41.8624,-92.8834 41.8623,-92.8634 41.8623,-92.844 41.8623,-92.8439 41.8623,-92.8246 41.8622,-92.8245 41.8622,-92.8051 41.8623,-92.7859 41.8624,-92.7858 41.8624,-92.7664 41.8624,-92.7665 41.8486,-92.7665 41.8485,-92.7664 41.834,-92.7667 41.8196,-92.7668 41.805,-92.7667 41.7905,-92.7667 41.776,-92.7666 41.7607,-92.7662 41.7464,-92.7664 41.7319,-92.7665 41.7174,-92.7665 41.7173,-92.7665 41.7029,-92.7665 41.6882,-92.7665 41.6881,-92.7664 41.6741,-92.7665 41.6595,-92.7665 41.6451,-92.7665 41.6449,-92.7665 41.6304,-92.7667 41.6159,-92.7668 41.6015,-92.7573 41.6015,-92.7571 41.5813,-92.7571 41.5669,-92.757 41.5523,-92.7569 41.5378,-92.7569 41.5234,-92.7566 41.5088,-92.7568 41.5088,-92.776 41.5088,-92.7762 41.5088,-92.7953 41.5088,-92.7954 41.5088,-92.8145 41.5088,-92.8148 41.5088,-92.8341 41.5085,-92.8342 41.5085,-92.8343 41.5085,-92.8531 41.5085,-92.8534 41.5085,-92.8717 41.5085,-92.8718 41.5085,-92.8752 41.5085,-92.8757 41.5084,-92.8909 41.5085,-92.91 41.5084,-92.9294 41.5084,-92.9487 41.5083,-92.9678 41.5083,-92.9866 41.5082,-93.0001 41.5082,-93.0006 41.5082,-93.0055 41.5083,-93.0248 41.5083,-93.0249 41.5083,-93.0442 41.5084,-93.0444 41.5084,-93.0635 41.5083,-93.0828 41.5082,-93.1009 41.5081,-93.1011 41.5081,-93.1204 41.5081,-93.1395 41.5082,-93.1588 41.5082,-93.1589 41.5082,-93.1781 41.5081,-93.1974 41.508,-93.1976 41.508,-93.2147 41.5079,-93.234 41.508,-93.2533 41.5083,-93.2724 41.5082,-93.2916 41.5082,-93.3108 41.5082,-93.3286 41.5079,-93.3286 41.508,-93.3288 41.5224,-93.3288 41.5225,-93.3291 41.537,-93.329 41.5515,-93.329 41.5517,-93.329 41.5662,-93.329 41.5663,-93.3289 41.5807,-93.3287 41.601,-93.3481 41.6009,-93.3487 41.6008,-93.3488 41.6008,-93.3486 41.6153,-93.3486 41.6298,-93.3486 41.6443,-93.3486 41.6587,-93.3484 41.6731,-93.3484 41.6732,-93.3486 41.6883,-93.3486 41.7028,-93.3484 41.7173,-93.3482 41.7317,-93.3482 41.7318,-93.3482 41.7462,-93.348 41.7607,-93.3482 41.7766,-93.3481 41.7911,-93.3481 41.8056,-93.3482 41.82,-93.3481 41.8344,-93.3481 41.8489,-93.3479 41.8631,-93.3285 41.8631))	(-93.3488,41.5079,-92.7566,41.8631)
19021	Buena Vista	20260	POLYGON ((-95.3679 42.9098,-95.3485 42.9098,-95.3483 42.9098,-95.3287 42.91,-95.309 42.9102,-95.2891 42.91,-95.2698 42.9099,-95.2495 42.9099,-95.2301 42.9099,-95.2106 42.9099,-95.1908 42.91,-95.1712 42.91,-95.1515 42.9101,-95.1324 42.91,-95.1126 42.9099,-95.0928 42.9098,-95.0731 42.9097,-95.0534 42.9098,-95.0337 42.9098,-95.0124 42.9098,-94.9928 42.9098,-94.973 42.9097,-94.9728 42.9097,-94.9533 42.9097,-94.9335 42.9098,-94.9139 42.9098,-94.9138 42.8944,-94.9139 42.88,-94.9139 42.8799,-94.9137 42.8653,-94.9137 42.8508,-94.9137 42.8507,-94.9137 42.8363,-94.9139 42.8219,-94.9139 42.8218,-94.9139 42.807,-94.9139 42.8069,-94.9138 42.7924,-94.9139 42.7779,-94.9138 42.7634,-94.9139 42.7489,-94.9139 42.7345,-94.9139 42.7344,-94.9144 42.7197,-94.9148 42.7053,-94.915 42.6907,-94.9153 42.6762,-94.9157 42.6617,-94.9159 42.6473,-94.9156 42.6329,-94.9153 42.6183,-94.9152 42.6039,-94.9152 42.6038,-94.9151 42.5894,-94.9151 42.5892,-94.915 42.5747,-94.9146 42.5603,-94.9344 42.5606,-94.954 42.5605,-94.9735 42.5608,-94.9933 42.5608,-95.0129 42.5609,-95.0327 42.561,-95.0523 42.5611,-95.0718 42.5612,-95.0914 42.5611,-95.0916 42.5611,-95.1111 42.5611,-95.1308 42.561,-95.1511 42.5609,-95.1706 42.5611,-95.1707 42.5611,-95.1904 42.561,-95.21 42.561,-95.2295 42.5609,-95.2492 42.561,-95.2699 42.5609,-95.2895 42.5608,-95.309 42.5609,-95.3286 42.5609,-95.3482 42.5611,-95.3677 42.5615,-95.388 42.5617,-95.388 42.5618,-95.388 42.5764,-95.3881 42.5909,-95.388 42.6054,-95.3882 42.6198,-95.3883 42.6343,-95.3882 42.6478,-95.3882 42.6623,-95.388 42.6767,-95.388 42.6768,-95.388 42.6912,-95.388 42.6913,-95.3879 42.7058,-95.3877 42.7202,-95.3877 42.7203,-95.3875 42.7343,-95.3875 42.7487,-95.388 42.7632,-95.388 42.7633,-95.388 42.7777,-95.3879 42.7921,-95.3879 42.7922,-95.388 42.8068,-95.388 42.822,-95.3881 42.8364,-95.3881 42.8509,-95.3881 42.851,-95.3882 42.8654,-95.3882 42.8655,-95.3879 42.88,-95.3879 42.88,-95.388 42.8944,-95.3882 42.9099,-95.3679 42.9098))	(-95.3883,42.5603,-94.9137,42.9102)
19023	Butler	14735	POLYGON ((-92.5542 42.8912,-92.5542 42.891,-92.5542 42.8766,-92.5542 42.8765,-92.5542 42.8752,-92.5542 42.875,-92.5542 42.8619,-92.5544 42.8474,-92.5544 42.8328,-92.5544 42.8182,-92.5543 42.8028,-92.5543 42.8027,-92.5546 42.7882,-92.5546 42.7881,-92.5545 42.7737,-92.5543 42.7592,-92.5545 42.7447,-92.5545 42.7301,-92.5545 42.715,-92.5544 42.7005,-92.5545 42.6858,-92.5544 42.6714,-92.5546 42.6568,-92.5544 42.6424,-92.5544 42.6283,-92.5544 42.6137,-92.5545 42.5993,-92.5545 42.5847,-92.5547 42.5703,-92.5544 42.5558,-92.5544 42.5557,-92.5741 42.5558,-92.5936 42.5558,-92.5938 42.5558,-92.6133 42.5559,-92.633 42.5559,-92.6529 42.5559,-92.673 42.5559,-92.6927 42.5558,-92.7124 42.5558,-92.7319 42.5558,-92.7499 42.5559,-92.7504 42.5558,-92.7514 42.5558,-92.7713 42.5556,-92.7907 42.5558,-92.8104 42.5558,-92.8105 42.5558,-92.8299 42.5558,-92.83 42.5558,-92.8496 42.5559,-92.8693 42.556,-92.8749 42.556,-92.8755 42.5559,-92.8888 42.5558,-92.9079 42.5561,-92.9275 42.5563,-92.9472 42.5563,-92.967 42.5564,-92.9865 42.5566,-93.0061 42.5568,-93.027 42.5568,-93.027 42.5713,-93.0268 42.5859,-93.0268 42.586,-93.0268 42.6004,-93.0266 42.6152,-93.0266 42.6293,-93.0266 42.6294,-93.0265 42.6433,-93.0266 42.6578,-93.0266 42.6579,-93.0265 42.6723,-93.0266 42.6869,-93.0263 42.7013,-93.0262 42.7157,-93.0262 42.7158,-93.0261 42.7306,-93.0259 42.745,-93.0257 42.7597,-93.0258 42.7741,-93.0259 42.7886,-93.0258 42.8029,-93.0256 42.8186,-93.0256 42.8187,-93.0256 42.8332,-93.0255 42.8477,-93.0255 42.8621,-93.0253 42.8767,-93.0253 42.8912,-93.0253 42.9076,-93.0054 42.9076,-92.9857 42.9075,-92.966 42.9074,-92.9659 42.9074,-92.9462 42.9075,-92.9266 42.9074,-92.9264 42.9074,-92.9069 42.9074,-92.9068 42.9074,-92.8891 42.9072,-92.8693 42.9073,-92.8498 42.9073,-92.8497 42.9073,-92.83 42.9074,-92.8298 42.9074,-92.8103 42.9073,-92.8101 42.9073,-92.7906 42.9073,-92.7722 42.9073,-92.7523 42.9072,-92.7325 42.907,-92.7126 42.9071,-92.7125 42.9071,-92.6926 42.9071,-92.6728 42.9071,-92.6533 42.9071,-92.6336 42.907,-92.6138 42.907,-92.5941 42.9071,-92.5939 42.9071,-92.5741 42.9072,-92.5543 42.9071,-92.5542 42.8912))	(-93.027,42.5556,-92.5542,42.9076)
19025	Calhoun	9780	POLYGON ((-94.8948 42.5603,-94.8947 42.5603,-94.8756 42.5602,-94.8752 42.5602,-94.8555 42.56,-94.8359 42.5599,-94.8161 42.5597,-94.7966 42.5598,-94.7759 42.5595,-94.7565 42.5595,-94.7504 42.5596,-94.7503 42.5596,-94.7366 42.5592,-94.7171 42.5592,-94.6975 42.5594,-94.6781 42.5592,-94.6579 42.5597,-94.6385 42.5598,-94.6255 42.5599,-94.6252 42.5599,-94.6191 42.5599,-94.619 42.5599,-94.5992 42.5595,-94.5799 42.5595,-94.5603 42.5592,-94.5397 42.5588,-94.5201 42.5588,-94.5007 42.5589,-94.4811 42.5589,-94.481 42.5588,-94.4627 42.5589,-94.443 42.5589,-94.443 42.5457,-94.4431 42.5313,-94.4431 42.5169,-94.4431 42.5023,-94.443 42.4878,-94.4429 42.4734,-94.4365 42.4734,-94.4231 42.4734,-94.417 42.4734,-94.4035 42.4734,-94.3976 42.4734,-94.3976 42.4563,-94.3976 42.4562,-94.3975 42.4418,-94.3975 42.4417,-94.3974 42.4274,-94.3974 42.4273,-94.3974 42.4129,-94.3974 42.4128,-94.3974 42.3983,-94.3971 42.3838,-94.3973 42.3751,-94.3973 42.375,-94.3974 42.3687,-94.3973 42.3542,-94.3973 42.3541,-94.3974 42.3397,-94.3975 42.3253,-94.3975 42.3108,-94.3976 42.2964,-94.3976 42.2962,-94.3977 42.2817,-94.3978 42.2673,-94.3977 42.2528,-94.3976 42.2381,-94.3975 42.2238,-94.3975 42.2237,-94.3974 42.2092,-94.4171 42.2093,-94.4363 42.2094,-94.4558 42.2093,-94.4751 42.2093,-94.4946 42.2093,-94.5001 42.2093,-94.5004 42.2093,-94.5131 42.2094,-94.5324 42.2093,-94.5518 42.2095,-94.5712 42.2095,-94.5906 42.2094,-94.6101 42.2094,-94.6251 42.2096,-94.6253 42.2096,-94.6287 42.2095,-94.6288 42.2095,-94.6483 42.2096,-94.6676 42.2096,-94.6869 42.2096,-94.7063 42.2094,-94.7258 42.2094,-94.745 42.2094,-94.7501 42.2095,-94.7504 42.2095,-94.7645 42.2096,-94.7839 42.2097,-94.7841 42.2097,-94.8033 42.2098,-94.8229 42.2098,-94.8423 42.2097,-94.8586 42.2097,-94.8586 42.2241,-94.8584 42.2385,-94.8582 42.2499,-94.8582 42.25,-94.8582 42.2532,-94.8582 42.2533,-94.8583 42.2676,-94.8584 42.282,-94.8583 42.2963,-94.8581 42.3108,-94.8579 42.3252,-94.8577 42.3399,-94.8575 42.3543,-94.8572 42.3689,-94.8573 42.3849,-94.8573 42.3994,-94.8572 42.4138,-94.8572 42.414,-94.857 42.4283,-94.857 42.4284,-94.857 42.4429,-94.8568 42.4573,-94.8566 42.474,-94.8746 42.4741,-94.8764 42.4741,-94.8943 42.4742,-94.8958 42.4742,-94.9145 42.4743,-94.9147 42.4743,-94.9146 42.4888,-94.9144 42.5033,-94.9144 42.5179,-94.9145 42.5324,-94.9146 42.5468,-94.9146 42.5469,-94.9146 42.5603,-94.8948 42.5603))	(-94.9147,42.2092,-94.3971,42.5603)

In [15]:

df4.shape

Out[15]:

(99, 5)

In [16]:

df4.dtypes

Out[16]:

COLUMN NAME	TYPE
county_id	int
county_name	str
population	int
geometry	str
geo_mbr	str

3. Service Center Analysis

Measure service center workload based on customer demand and location. For this we Calculated workload as an attribution score per service center using geo functions based on distances to customer locations.
Identify problem areas or service blind spots based on population, customer demand, and distances between customers and centres. For this we calculated an aggregated performance score per area unit based on multiple metrics.

Benefits from this kind of analysis is that clients can allocate resources to even out workload among service centers based on the attribution scores also this can help them to locate new store location.

Distance-based Attribution

The typical approach to attribution is mainly as follows Attribution Function in Vantage

Row-wise time and transaction-based data
Credit is distributed to events based on time
- FIRST_CLICK
- LAST_CLICK
- UNIFORM
- EXPONENTIAL

In our case we have used distance based attribution where we distribute attribution to service centres within X km of a complaint. If none, give full credit to the nearest service center.

Distance based attribution.

We have used ST_Geometry data type in Vantage to load and used the geographic data. The coordinates (latitude and longitude) of the service centers and complaint locations are loaded as ST_Point; also the county information of the IOWA state is loaded as ST_POLYGON is loaded to get the country boundary.
We have used ST_SphericalDistance function to find the nearest service center to each complaint location and the respective distance between points and ST_Within in a WHERE clause to determine which county (ST_Polygon) a certain complaint (ST_Point) belongs to.

In [17]:

eng.execute('''
REPLACE VIEW sc_attribution AS
 -- distances between service centers and complaints
WITH distances as (
    SELECT b.service_center_id as sc_id,
        b.geometry as sc_geom,
        a.cmplid as comp_id,
        b.start_yearmonth,
        b.end_yearmonth,
        a.geometry.ST_SphericalDistance(b.geometry)/1000 as dist --distance in kilometers
    FROM DEMO_CAR.complaint_locations a, DEMO_CAR.service_centers b),
-- nearest service centers to each complaint
nearest as (
    SELECT sc_id,
        sc_geom,
        comp_id, 
        start_yearmonth,
        end_yearmonth,
        dist
    FROM distances
    QUALIFY ROW_NUMBER() OVER(PARTITION BY comp_id ORDER BY DIST) = 1)
-- table of service centers and aggregated attribution scores 

    SELECT sc_id service_center_id,
        'Station '||CAST(sc_id AS CHAR(3)) as service_center,
        sc_geom.ST_Y() as lat,
        sc_geom.ST_X() as "long",
        start_yearmonth,
        end_yearmonth,
        SUM(attrib_score) as attribution_score, -- total attribution score is the sum of attribution scores across complaints 
        RANK() OVER (ORDER BY attribution_score DESC) as attribution_score_rank
    FROM (
        SELECT comp_id, 
            sc_id,
            sc_geom,
            start_yearmonth,
            end_yearmonth,
            1.000/ (COUNT(sc_id) OVER (PARTITION BY comp_id)) as attrib_score
        FROM distances
        WHERE dist < 15
    
        UNION ALL
    
        SELECT comp_id, 
            sc_id,
            sc_geom,
            start_yearmonth,
            end_yearmonth,
            1
        FROM nearest
        WHERE dist > 15
    
        UNION ALL
    
        SELECT comp_id, 
            sc_id,
            sc_geom,
            start_yearmonth,
            end_yearmonth,
            0
        FROM distances
        WHERE dist > 15
        ) AS attrib_scores
    GROUP BY 1,2,3,4,5,6
; ''')    

Out[17]:

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f67b5c11580>

Let's see how the data in this view looks like

In [18]:

sc = DataFrame("sc_attribution")
sc

Out[18]:

service_center_id	service_center	lat	long	start_yearmonth	end_yearmonth	attribution_score	attribution_score_rank
83	Station 83	42.01404665	-91.74025638	201904	202006	48.000	3
9	Station 9	40.86672153	-92.1412393	201803	201809	3.500	72
7	Station 7	40.89607245	-91.74730629	201806	201912	3.500	72
136	Station 136	43.0478577	-95.86271827	201907	202010	7.500	36
16	Station 16	40.88838056	-95.72498183	201801	201809	7.000	38
33	Station 33	41.17413379	-91.09308906	201808	201904	1.500	106
42	Station 42	41.32922594	-94.69766427	201803	201912	1.500	106
104	Station 104	42.55418528	-91.47810358	201901	202011	1.500	106
30	Station 30	41.06751052	-92.10050404	201802	201809	7.166	37
2	Station 2	40.52181917	-91.47394521	201907	202004	3.500	72

Now let us plot this data to see how the service center attribution changes over the years.
*Please click on the play button to start animation.

In [19]:

qry = ''' select service_center_id,service_center,lat,"long",start_yearmonth,attribution_score from sc_attribution group by 1,2,3,4,5,6 ; ''' df = pd.read_sql(qry, eng) fig1 = px.scatter_mapbox(df,lat="lat", lon="long", hover_name="service_center",size="attribution_score", color="attribution_score", size_max=70, zoom=6, animation_frame="start_yearmonth",color_continuous_scale=px.colors.sequential.Bluered, height = 600 ) fig1.update_layout(mapbox_style="open-street-map") fig1.update_layout(margin={"r":0,"t":0,"l":0,"b":0}) fig1.update_layout(title_text = 'Service_center ranks over the years' ,title_y=1) fig1.show()

© OpenStreetMap contributors

In the map above we can see the year wise attribution score based on the calculations we have done in the view earlier.

Area Rankings
Each area is ranked according by multiple metrics, and these rankings are combined to score the area.

Population

No. Of Customers/ Complaints

No. Of Non-Covered Customers/ Complaints

Percent of Non-Covered Customers/ Complaints

We have created a view on the service center information and the IOWA county data to get the ranks

In [20]:

eng.execute(''' REPLACE VIEW county_accessibility AS -- table of service centers nearest to each complaint WITH nearest as ( SELECT a.cmplid, a.geometry as cmpl_geom, b.service_center_id as sc_id, b.geometry as sc_geom, a.geometry.ST_SphericalDistance(b.geometry)/1000 as dist FROM demo_car.complaint_locations as a, demo_car.service_centers as b QUALIFY ROW_NUMBER() OVER(PARTITION BY cmplid ORDER BY dist) = 1 ) SELECT county_id, county_name, population, RANK() OVER(ORDER BY population DESC) as population_rank, -- number of complaints per county whose nearest service center is more than x distance away COUNT( CASE WHEN (dist > 15) AND a.cmpl_geom.ST_Within(b.geometry) = 1 THEN cmplid ELSE NULL END) as noncovered_customers, RANK() OVER(ORDER BY noncovered_customers DESC) as noncovered_customers_rank, COUNT( CASE WHEN a.cmpl_geom.ST_Within(b.geometry) = 1 THEN cmplid ELSE NULL END) as customers, RANK() OVER(ORDER BY customers DESC) as customers_rank, CASE WHEN customers = 0 THEN NULL ELSE (noncovered_customers*1.0000)/(customers*1.0000) END as noncovered_customers_pct, COALESCE(noncovered_customers_pct,0) AS noncovered_customers_pct2, CASE WHEN noncovered_customers_pct IS NULL THEN NULL ELSE RANK() OVER(ORDER BY noncovered_customers_pct DESC) END as noncovered_customers_pct_rank FROM nearest a, demo_car.counties b where a.cmpl_geom.st_x() between geo_mbr.xmin() and geo_mbr.xmax() and a.cmpl_geom.st_y() between geo_mbr.ymin() and geo_mbr.ymax() GROUP BY 1,2,3 ; ''')

Out[20]:

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f67b437c100>

* The view query has complex geometry calculation, the below step takes approx 1min to run.

In [21]:

df = DataFrame.from_query('''select county_id, county_name, population_rank, noncovered_customers_rank, customers_rank, noncovered_customers_pct_rank from county_accessibility;''') ca=df.to_pandas() ca.head(5)

Out[21]:

county_id county_name population_rank noncovered_customers_rank customers_rank noncovered_customers_pct_rank

0 19183 Washington 28 11 21 1.0

1 19103 Johnson 4 3 3 28.0

2 19181 Warren 11 17 13 56.0

3 19157 Poweshiek 37 63 26 80.0

4 19109 Kossuth 52 34 62 1.0

Let us visualize this data

In [22]:

#read the geojson files to get boundry information of the counties with open ("./data/Iowa_County_Boundaries.geojson",'r') as infile: counties = json.load(infile)

In [23]:

def plot_map(candidate): plt.figure(figsize=(8, 6)) fig = px.choropleth_mapbox( ca, geojson=counties, color=candidate, locations="county_id", featureidkey="properties.FIPS", center={"lat": 42.032974, "lon": -93.581543}, mapbox_style="open-street-map", zoom=5) fig.update_geos(fitbounds="locations", visible=False) fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0}) return fig # Create the dropdown widget candidates = ["population_rank","noncovered_customers_rank", "customers_rank","noncovered_customers_pct_rank"] candidate_dropdown = widgets.Dropdown(options=candidates, description='Candidate:', value='population_rank') # Call the plot_clusters function with the selected dropdown options def update_plot(candidate): plot_map(candidate).show() widgets.interact(update_plot, candidate=candidate_dropdown)

interactive(children=(Dropdown(description='Candidate:', options=('population_rank', 'noncovered_customers_ran…

Out[23]:

<function __main__.update_plot(candidate)>

*Please note that the map takes few seconds to refresh after dropdown change
As we can see the map above shows the counties based on the various ranks we have calculated

4. Defect Analysis

In our demo we will find complaints where car parts had early defects from the date of purchase compared to other complaints.We will try to find if there is any outliers or anomalies in the complaint data. In simple words an outlier is a data point that differs significantly from other observations.
The main benefits from this type of analysis is that

Car insurance providers or manufacturers can detect suspicious car defect complaints.

Helps car companies in determining faulty car models and car parts.

Car manufacturers can use this to know where to improve in models with numerous early defect complaints.

We will use Vantage's TD_OutlierFit and Transforms functions to find the outliers in data and analyse them.

In [24]:

#we have pulled the complaints table earlier we'll use that dataframe com=df3.to_pandas() com.head()

Out[24]:

complaint_date purchase_date manufacture_date days_to_defect months_to_defect complaint_desc manufacturer model model_year car_part

complaint_id

1577051 2019-06-20 2016-08-30 2014-11-21 1024 34 TL* THE CONTACT OWNS A 2015 FORD F-150. WHILE ... ford motor company F-150 2015 VEHICLE SPEED CONTROL

1578519 2019-07-03 2016-06-13 2015-02-03 1115 37 REPORTED MY ISSUE ALREADY 11229377 BUT WANTED ... ford motor company F-150 2016 FUEL/PROPULSION SYSTEM

1547809 2019-03-19 2011-03-23 2009-05-25 2918 96 TL* THE CONTACT OWNS A 2010 FORD ESCAPE. WHILE... ford motor company ESCAPE 2010 STEERING

1584698 2019-07-22 2018-08-12 2015-11-28 344 11 TL* THE CONTACT OWNS A 2016 FORD F-150. WHILE ... ford motor company F-150 2016 ENGINE

1563164 2019-02-05 2013-02-09 2012-06-18 2187 72 DOWNSHIFTS INTO 1ST... STILL...AFTER THE RECAL... ford motor company F-150 2013 POWER TRAIN

Let us plot a boxplot on the complaints data. A box plot is a graphical rendition of statistical data based on the minimum, first quartile, median, third quartile, and maximum. The term box plot comes from the fact that the graph looks like a rectangle with lines extending from the top and bottom.

In [25]:

plt.figure(figsize=(20,8)) #plt.xlabel('Sales Date', fontsize=16, rotation=45)--- ye rotation waala parameter try karo #plt.xlabel('car_part', fontsize=16, rotation=45) plt.tick_params(axis='x', which='major', labelsize=14, rotation=90) plt.xlabel('car_part', fontsize=16); plt.ylabel('days_to_defect', fontsize=16); plt.title('Box plot of the data by car part', fontsize=20) ax = sns.boxplot(x = 'car_part', y = 'days_to_defect', data = com)

The widths of the box plot indicate the size of the samples. The wider the box, the larger the sample. As we have many car parts in our dataset let us select few and plot them again to get a better visual.

In [26]:

options = ['STEERING' ,'ENGINE','STRUCTURE','POWER TRAIN'] #com[com['car_part']=='STEERING'] plt.figure(figsize=(20,8)) #plt.xlabel('Sales Date', fontsize=16, rotation=45)--- ye rotation waala parameter try karo #plt.xlabel('car_part', fontsize=16, rotation=45) plt.tick_params(axis='x', which='major', labelsize=14, rotation=90) plt.xlabel('car_part', fontsize=16); plt.ylabel('days_to_defect', fontsize=16); plt.title('Box plot of the data by car part', fontsize=20) ax = sns.boxplot(x = 'car_part', y = 'days_to_defect', data = com[com['car_part'].isin(options)])

The mid line inside the box is the median(Q2) of data and lower(Q1) and top lines(Q3) of the box is 25% and 75% of the data. The lowest limit value equals Q1 – 1.5 * (Q3-Q1) and the upper limit value equals Q3 + 1.5 * (Q3-Q1). Any points that lie beyond the limit points are considered outliers.

Now let us try to find the outliers in the data. OutlierFilterFit from teradataml library calculates the lower_percentile, upper_percentile, count of rows and median for the specified input table columns. The calculated values are passed to OutlierFilterTransform function to filter out the outliers from the dataset. We are using Tukey method ([Q1 - k*(Q3-Q1), Q1 + k*(Q3-Q1)] where k is interquantile range multiplier) for outlier detection, other methods availble are Percentile and Carling. Please refer to documentation for a full listing of parameters and return values.

In [27]:

from teradataml import OutlierFilterFit, OutlierFilterTransform fit_obj = OutlierFilterFit(data=df3, target_columns="days_to_defect", outlier_method="TUKEY", replacement_value="DELETE", iqr_multiplier=0.1, remove_tail="LOWER", group_columns="car_part")

In [28]:

fit_obj.result.sample(n = 5)

Out[28]:

TD_OUTLIERMETHOD_OFTFIT car_part TD_IQRMULTIPLIER_OFTFIT TD_REMOVETAIL_OFTFIT TD_REPLACEMENTVALUE_OFTFIT TD_MINTHRESHOLD_OFTFIT TD_MAXTHRESHOLD_OFTFIT TD_ATTRIBUTEVALUE_OFTFIT TD_COUNTVALUE_OFTFIT TD_MEDIANVALUE_OFTFIT TD_LOWERPERCENTILE_OFTFIT TD_UPPERPERCENTILE_OFTFIT sampleid

TUKEY FUEL/PROPULSION SYSTEM 0.1 LOWER DELETE 0.25 0.75 days_to_defect 65 1094.0 706.0 1558.0 1

TUKEY AIR BAGS 0.1 LOWER DELETE 0.25 0.75 days_to_defect 38 1447.0 766.0 2274.0 1

TUKEY SUSPENSION 0.1 LOWER DELETE 0.25 0.75 days_to_defect 32 888.5 448.0 1889.0 1

TUKEY EXTERIOR LIGHTING 0.1 LOWER DELETE 0.25 0.75 days_to_defect 6 526.0 143.0 2436.0 1

TUKEY STRUCTURE 0.1 LOWER DELETE 0.25 0.75 days_to_defect 51 847.0 327.0 1599.0 1

In [29]:

obj = OutlierFilterTransform(data=df3,data_partition_column="car_part", object=fit_obj.result, object_partition_column="car_part")

In [30]:

df5=(obj.result).to_pandas() df5.head(5)

Out[30]:

complaint_id complaint_date purchase_date manufacture_date days_to_defect months_to_defect complaint_desc manufacturer model model_year car_part

0 1576533 2019-06-27 2002-01-24 1998-04-03 6363 209 I LIVE IN CONN. AND AS YOU KNOW, WE HAVE THE B... ford motor company SERVICE FUEL TANK 9999 EQUIPMENT

1 1533335 2019-01-09 2015-07-05 2006-06-08 1284 42 TAKATA RECALL - FOR THREE WEEKS NOW, IVE BEEN ... ford motor company MKX 2007 AIR BAGS

2 1568475 2019-05-15 2015-02-26 2014-11-21 1539 51 TL* THE CONTACT OWNS A 2015 FORD MUSTANG. THE ... ford motor company MUSTANG 2015 BACK OVER PREVENTION

3 1588264 2019-05-12 2017-04-22 2016-07-22 750 25 TL* THE CONTACT OWNS A 2016 FORD EXPLORER. THE... ford motor company EXPLORER 2016 ENGINE AND ENGINE COOLING

4 1543242 2019-01-10 2018-11-17 2003-03-17 54 2 CPSC: HEATED SEAT COVER WAS PLUGGED INTO 2004 ... ford motor company F-250 SD 2004 EQUIPMENT

Our source input dataframe had 1000 records where as the transformed dataframe has 829 records. Let us minus the two dataframes to get only the records which are marked as outliers based on our input parameters.

In [31]:

from teradataml.dataframe.setop import td_minus

In [32]:

idf = td_minus([df3, obj.result], allow_duplicates=False) df6=idf.to_pandas()

Let us plot this data to see which car parts have the higher number of complaints.

In [33]:

plt.figure(figsize=(20,8)) plt.tick_params(axis='x', which='major', labelsize=14, rotation=90) ax = sns.countplot(x="car_part",data=df6)

As we can see from the plot above the steering and power train have the most number of complaints. We can do similar analysis on car models also.

5. Clean up

The following code will clean up tables and databases created above.

In [34]:

%run -i ../run_procedure.py "call remove_data('DEMO_Car');" #Takes 10 seconds

Removed objects related to DEMO_Car. That ran for 0:00:03.66

6. Conclusion

As we have seen in this demo that we can get great insights from our data if we augment the data with its geographical parameters. We have also seen that the anomalies happen in the data which may or may not be cause of concern but analysis on them can lead to better insights on how business can enhance thier processes or divert the resources where needed.

Reference Links:

Teradata Package for Python Function Reference: https://docs.teradata.com/r/Enterprise/Teradata-Package-for-Python-Function-Reference-17.20/Teradata-Package-for-Python-Function-Reference

©2023 Teradata. All Rights Reserved

	county_id	county_name	population_rank	noncovered_customers_rank	customers_rank	noncovered_customers_pct_rank
0	19183	Washington	28	11	21	1.0
1	19103	Johnson	4	3	3	28.0
2	19181	Warren	11	17	13	56.0
3	19157	Poweshiek	37	63	26	80.0
4	19109	Kossuth	52	34	62	1.0

	complaint_date	purchase_date	manufacture_date	days_to_defect	months_to_defect	complaint_desc	manufacturer	model	model_year	car_part
complaint_id
1577051	2019-06-20	2016-08-30	2014-11-21	1024	34	TL* THE CONTACT OWNS A 2015 FORD F-150. WHILE ...	ford motor company	F-150	2015	VEHICLE SPEED CONTROL
1578519	2019-07-03	2016-06-13	2015-02-03	1115	37	REPORTED MY ISSUE ALREADY 11229377 BUT WANTED ...	ford motor company	F-150	2016	FUEL/PROPULSION SYSTEM
1547809	2019-03-19	2011-03-23	2009-05-25	2918	96	TL* THE CONTACT OWNS A 2010 FORD ESCAPE. WHILE...	ford motor company	ESCAPE	2010	STEERING
1584698	2019-07-22	2018-08-12	2015-11-28	344	11	TL* THE CONTACT OWNS A 2016 FORD F-150. WHILE ...	ford motor company	F-150	2016	ENGINE
1563164	2019-02-05	2013-02-09	2012-06-18	2187	72	DOWNSHIFTS INTO 1ST... STILL...AFTER THE RECAL...	ford motor company	F-150	2013	POWER TRAIN

TD_OUTLIERMETHOD_OFTFIT	car_part	TD_IQRMULTIPLIER_OFTFIT	TD_REMOVETAIL_OFTFIT	TD_REPLACEMENTVALUE_OFTFIT	TD_MINTHRESHOLD_OFTFIT	TD_MAXTHRESHOLD_OFTFIT	TD_ATTRIBUTEVALUE_OFTFIT	TD_COUNTVALUE_OFTFIT	TD_MEDIANVALUE_OFTFIT	TD_LOWERPERCENTILE_OFTFIT	TD_UPPERPERCENTILE_OFTFIT	sampleid
TUKEY	FUEL/PROPULSION SYSTEM	0.1	LOWER	DELETE	0.25	0.75	days_to_defect	65	1094.0	706.0	1558.0	1
TUKEY	AIR BAGS	0.1	LOWER	DELETE	0.25	0.75	days_to_defect	38	1447.0	766.0	2274.0	1
TUKEY	SUSPENSION	0.1	LOWER	DELETE	0.25	0.75	days_to_defect	32	888.5	448.0	1889.0	1
TUKEY	EXTERIOR LIGHTING	0.1	LOWER	DELETE	0.25	0.75	days_to_defect	6	526.0	143.0	2436.0	1
TUKEY	STRUCTURE	0.1	LOWER	DELETE	0.25	0.75	days_to_defect	51	847.0	327.0	1599.0	1

	complaint_id	complaint_date	purchase_date	manufacture_date	days_to_defect	months_to_defect	complaint_desc	manufacturer	model	model_year	car_part
0	1576533	2019-06-27	2002-01-24	1998-04-03	6363	209	I LIVE IN CONN. AND AS YOU KNOW, WE HAVE THE B...	ford motor company	SERVICE FUEL TANK	9999	EQUIPMENT
1	1533335	2019-01-09	2015-07-05	2006-06-08	1284	42	TAKATA RECALL - FOR THREE WEEKS NOW, IVE BEEN ...	ford motor company	MKX	2007	AIR BAGS
2	1568475	2019-05-15	2015-02-26	2014-11-21	1539	51	TL* THE CONTACT OWNS A 2015 FORD MUSTANG. THE ...	ford motor company	MUSTANG	2015	BACK OVER PREVENTION
3	1588264	2019-05-12	2017-04-22	2016-07-22	750	25	TL* THE CONTACT OWNS A 2016 FORD EXPLORER. THE...	ford motor company	EXPLORER	2016	ENGINE AND ENGINE COOLING
4	1543242	2019-01-10	2018-11-17	2003-03-17	54	2	CPSC: HEATED SEAT COVER WAS PLUGGED INTO 2004 ...	ford motor company	F-250 SD	2004	EQUIPMENT