Tornado

Claims Data Cleanup Actions Taken 

Sort Ascending by Value; delete all 0 and negative (-) values in “Gross Estimate Amount” column (10924 rows had Zero for the amount 22.5% of total data) 

Many suspect values remain ($1, etc.  basically anything less than a deductible (typically $500 or  

$1000) is suspicious to me and seems like that can’t be accurate (another 3320 have values under $500 and 6110 less than $1k) 

Look at numeric “Type of Loss”  able to ID that 104 – Wind and 150 – Water… need other numeric codes to ID type of hazard and make those rows useful… (Deleted all other numeric values (26 rows) for now, can restore from clean dataset later if desired) 

Assumed GL69 = General Liability  

Deleted “Type of Loss” 111-Theft(1); “All Other/ All Other Peril Not Enumerated/All other Perils/ All Other Physical Damage/ AOPD/APLWTR-APL (assumed this is appliance?)” (276) because doesn’t help our purposes if not associated with a NATURAL hazard; “Backup of sewer or drain (Sump Pump/Faulty Drain)”(10) 

Skipped to Cause of Loss column because those details may be more helpful at initial elimination of non-weather related claims: Deleted “pipe burst …..”(1); “accidental breakage by insured”(1); “all other(FIRE)” because no wildfires that year; “animals/ insects” (1); “appliance”(12); “auto driven by nonresident”(1); “Back-up sewer drain/ bathroom sink clogged/ bathtub related/ broken pipe”(131); “Theft/ Burglary” (11); “candle” (6); “car ran through and destroyed(impact by vehicle)”(1); “cigarette smoking” (2); “fire sub loss” no wildfires in 2017 (1); “water damage toilet malfun”(1); “COL: water Sub Loss: escape from ap” (sounds like appliance?)(1); “contamination/pollution” (1) doesn’t specify source so  unsure if natural hazard related; “cooking(FIRE)” (35); “Damage from vehicle – insured owned”(1); “Damage to Dwelling(Vandalism)”(5); “Damage to bathroom flooring(1)”; “damages to Claimant vehicle(CPL?)”(1); “Defective Appliances”(6); “Drain Bck (Drain backup??)”(4); “Dryer(FIRE)”(1); “Electric Baseboard Heater”(1); “Electrical Fire”(8); “Equipment(WATER)” (5); “Falling Objects”(1) unspecified source?; “Faulty Drain”(3); “Faulty Wiring”(20); “Fire”(20) verified date/location didn’t match wildfire data; “Fire-All other/Fireplace/Grease Fire”(3); “Fire Caused by Appliance”(5); “Fire Electrical” (1); “Fire External Source”(1)”; “Fire Total” (1);  “Fireplace/SolidFuel/Chimney” (21); “Fire Erupted From Brush Hazard” (4) verified does not coincide with wildfire dates; “FNOL….. (Water/ Wind)” (4); “Frozen Pipe (Weather Related – No/ Non-weather)” (4); “Fungi/Bacteria”(1); “Furnace”(3); “Grease Fire in Kitchen”(13); “HVAC” (1); “int and ext damage (VANDALISM)” (1); “Jewelry Missing from home” (2); “Kitchen/ other than grease (FIRE)” (7); “leaking pipe…”(1); “Light(OTHER)” (2); “Light(WATER DAMAGE – APPLIANCE)”(1); “Moderate(OTHER/UNKNOWN)(1); “mold general/in kitchen(MOLD/OTHER)” (2); “Nearby Fire (SMOKE)”(2); “None(FIRE)”(2); “off Premises/ on premises (THEFT)”(11); “Other(BACKUP OF SEWER OR DRAIN)”(1); “Other(BURG/BURGLARY THEFT)”(92); “Other(COLLAPSE)”(1); “Other (COLL-VEH)”(5); “Other(DAMAGE BY VEHICLE WATERCRAFT)”(1); “Other(DRAIN BCK)”(8); “Other(FALLING OBJECT)” (9) Verified not on same date as a quake; “other(FIRE)”(85) Verified not on/near date/location of wildfire, (1) entry excluded from deletion because close to date/location of a wildfire; “Other (FIRE-APPL/COOK/ELEC/FIRPL/GASLK/OTHER/PORT/SMOKE/VEH/UNDT”(43); “Other(HURRICANE)(1)”; “Other(LIABILITY)”(1); “Other(MISCHIEF)”(1); “Other(MOLD/FUNGI)”(1); “Other(MYSTERIOUS DISAPPEARANCE)”(1); “Other(NONPF/SURG)”(1); “Other(NONWIND)”(5); “Other (OTH-AOC)”??? (55); “Other(OTHER)”(155); “Other (OTH-LOST/MOV/STC/VMM)”(9); “Other(PFSG-oth/pf/surge)”(4) Power failure?; “Other(PLBWTR-PLB/ PLUMBING)”(10); “Other(POWER OUTAGE)”(1); “Other(PRODUCT LIABILITY CLAIM)”(1); “Other (PROPERTY DAMAGE- All others)”(1); “Other (Sewage/drain/backup/sump pump)”(31); “Other(Smoke)”(4); “Other(Smpwtr-sum)”(1); “Other(STCOTH-STC)”(2); “Other (THEFT/Auto/nonauto/onp)”(44); “Other (Unknown LCI)”(4); “Other(Vandalism/malicious mischief)”(19); “other(VEHICLE)”(32); “Other(Vehicle damage)”(2); “Other (Wat/WATER/WATER DAMAGE/ NONWEATHER)” (370) left entries that specified “weather”; “Other(WATER/PLUMBING/Appliance/Other/Overflow)”(107); “Other(Weather related-no)”(9); “Other(WTR-APL/DAM)”(119): “Other(WTR-PLB)”(353); “Other(WTR-SUM)sump pump?(86); “Other- Narrative Required (FIRE) verified not coinciding location/date of wildfire (20); “Other – Narr Req (Mysterious Disappearance/ Sewer Backup/Vandalism/Water)” (17); “Other Non-Weather Related (WATER)” (23); “Other-Physical Damage”(1); “…foyer ceiling (WATER)”(1); “Pipe Burst / Pipe Burst– Non Weather Related(WATER)” Kept “weather related” entries (80); “Place of Business(THEFT)”(1); “Plmbg – xxx (WATER)”(26); “Plug-in Electric Heater”(1); “Plumbing/Leak (Water/ weather related-no/Water damage/Water non-weather)”(241); “Power Surge”(1); “…slow leak…”(1); “Roof Leak (Weather Related-No)” (1); “Roof Leak – Poor Cond.” (8); “Roof/Exterior Leak (Weather related – no)” (2); “Settlement Related Damage”(1); “Sewage”(1); “Sewer Back-Up from Appliance/Sink/etc.”(12); “Shower Pan”(2); “Sink Related”(9); “Sink/Tub/Toilet Overflow”(1); “Smoke(SMOKE)”(2); “Smoking (FIRE)” (6); “sub Loss: (MOLD)” (1); “escape from appli (WATER)”(3); “Sub Type lost…(THEFT)”(1); “sump pump (ALL)” (41); “water pipe broke and flooded…(WATER)”(2); “Theft (ALL)” (49); “Toilet… (WATER)”(99); “Undetermined (FIRE)”(2); “Unoccupied Dwelling (Vandalism)”(1); “Vandalism/ Mischief (all)”(18); “Vehicle”(20); “Washer Hose/ Washing Machine”(40); “Water (Liability P.D./ WATER)” unclear if natural hazard related (95); “Water – Appliance/ Other/Backup/External /Internal/Other/Plumbing”(26); “water escape from washer”(3); “Water Heater”(148); “Water Leak from damaged wall”(1); “Water Mold”(2); “Water Pipes”(); “Waterbed Burst”(1); “Weatheproofing Problems”(16) 

Frozen Pipe entries left…but should we try to determine if they were during periods of “Extreme Cold”? Same with “Ice Dam” 

Left “WTR-FLD and WTR-FRZ” on assumption those are flood and freeze related; left wtr-rof assuming that was a leak from the roof and those are usually related to a hazard (hail, etc) 

Left “Rain Damage” and “Roof Leak” water claims for now….  

Left “Weather Related” (Even though they said Drain Backup…) 

Left entries related to weight of ice and snow 

 TONS of Hail entries…TONS of Wind 

Continue “Type of Loss” because all remaining “Cause of Loss” are blank: Delete “*Extended”(99); “150 – Water” deleted due to no info about whether related to weather(11); “Backup of sewer or drain” (2); “Boardup/-up”(2); “Breakage”(1); “Burglary/Theft”(3); “Collapse”(19); “Collision”(18); “Damage by Vehicle”(8); “Drain Bck”(197); “Dwelling”(2); “Ext_wdr”??(1); “Falling Object” no idea what kind or why? (21); “FIRE” checked against wildand fire date/location before deleting (872); “fire or removal” (3); “general Liability/ GL69” (6); “Glass Breakage”(4); “Hurricane”(4) checked against hurricane records and left ones that coincided with dates storms reached KY; “Insured Travel Trailer…”(1); “Liability”(8); “Mischief”(2); “Mold/Fungi”(5); “Motor Vehicles”(1); “Mysterious Disappearance”(38); “Other”(535) kept weather related; “Plumbing”(17); “Powersurge”(1); “Product Liability Claim”(3); “Property/ Damage/All others/water”(8); “Renovation”(1); “Sewage/Sewer/Backup”(63); “Smoke/Damage”(39); “Tarping”(1); “Theft/OtherThan/FromAuto/OnP” (620);  “Unknown/LCI”(234); “Vandalism/Mischief”(140); “Vehicle”(171); “Water/Damage/ApplianceRelated/non-weather”(3761) too many records to look up flood/severe cold events and compare date/ location for possibly flood/cold related…left weather-related and freezing records; “Water Non-Weather”(114); “Water Plum/bing”(10) kept water weather/flood;  “Water/Plumbing”(18); “Water-Appliance”(15); Water-Other”(70); “Water-Overflow”(10); “Water-Plumbing”(42); “Weather Related – no”(3); “Wtr-apl”(1); “Wtroth”(1); “Wtr-sum”(1);  

 27000 of 48541 original records remain after cleanup