Getting the Data

This is downloaded from the County Auditor and Recorder’s Data. This can be any spreadsheet of county data as long as it has the parcel number, mortgage amount, tax district, effective rate, owner name, sale date, and (Auditor’s) market value.

Note: the Warren County Auditor’s website lists 4,606 properties with a land use code > 199 and < 500 whereas our data pulled up 4860 so we need to check on this.

This is just for commerical properties with land use codes >199 and <500. Should 800 and above be considered? We need to check on this.

#importing data
Commercial_Properties <- rio::import("Warren County Properties Last Verified 7-21-19.csv") #OR each County
#R doesn't like commas in the data, and won't read that as a number so this changes it. 
Commercial_Properties$Amount_Owed_to_County <- rmiscutils::number_with_commas(Commercial_Properties$Amount_Owed_to_County)
Commercial_Properties$mortgage_amount <- rmiscutils::number_with_commas(Commercial_Properties$mortgage_amount)
Commercial_Properties$True_Value <- rmiscutils::number_with_commas(Commercial_Properties$True_Value)
Commercial_Properties$Mortgage_Share <- rmiscutils::number_with_commas(Commercial_Properties$Mortgage_Share)
Commercial_Properties$New_Taxable_Value <- rmiscutils::number_with_commas(Commercial_Properties$New_Taxable_Value)
Commercial_Properties$Auditors_Total_Value_of_Parcels <- rmiscutils::number_with_commas(Commercial_Properties$Auditors_Total_Value_of_Parcels)
#As per the auditor checkint our math on this, we have to take out the exempt properties
Commercial_Properties <- filter(Commercial_Properties, taxable_value>0)

Calculating Baseline

What is the amount owed to each county?

note: in theory we would just add all the taxes_paid and there’s always going to be a certain percentage of people behind on their taxes, and we’re not interested in that. So here we are calculating the total amount to taxes collected in theory

Commercial_Properties <- mutate(Commercial_Properties, Amount_Owed_to_County=market_value*`Effective Rate`*.35*.001)

How much does each district raise?

tax_district Total_Per_District
01 - CLEARCREEK TWP-SPRINGBORO CSD 276407.0498
02 - CLEARCREEK TWP-LEBANON CSD 44785.4039
03 - CLEARCREEK TWP-WAYNE LSD 18493.4568
04 - CLEARCREEK TWP LCSD - FLD 33938.7748
05 - CLEARCREEK TWP-SPRINGBORO CORP 3959707.3200
06 - CLEARCREEK TWP-SPRBORO CSD FLD 159238.0339
07 - CLEARCREEK TWP-SPRINGBORO CORP 2139102.9752
08 - DEERFIELD TWP-MASON CSD 9045914.4227
09 - DEERFIELD TWP-KINGS LSD 7143797.0826
11 - MASON CORP - MASON CSD 9866944.1058
12 - MASON CORP - KINGS LSD 3959231.7295
14 - FRANKLIN TWP-FRANKLIN CSD 139402.9400
15 - FRANKLIN TWP-MIDDLETOWN CSD 14397.5473
16 - FRANKLIN TWP-CARLISLE LSD 89167.2141
18 - FRANKLIN TWP-CARLISLE CORP 650142.8949
19 - FRANKLIN TWP-CARLISLE CORP-FSD 51110.6924
21 - FRANKLIN CITY FCSD 4736595.7417
23 - MIDDLETOWN CORP-MIDDLETOWN CSD 2901139.5129
24 - FRANKLIN TWP-MIDDLETOWN CO-FSD 667895.7600
26 - HAMILTON TWP-LITTLE MIAMI LSD 1898174.8362
27 - HAMILTON TWP-LOVELAND CSD 2265.1534
28 - HAMILTON TWP-KINGS LSD 53151.8932
30 - LOVELAND CORP-LOVELAND CSD 34574.4000
31 - HAMILTON TWP-SO. LEBANON CORP 173150.7301
32 - HAMILTON TWP-MAINEVILLE CORP 167734.4306
33 - LOVELAND CORP-LITTLE MIAMI LSD 33791.1165
35 - HARLAN TWP-LITTLE MIAMI LSD 19712.9285
36 - HARLAN TWP-BLANCHESTER LSD 5014.6633
37 - HARLAN TWP-GOSHEN LSD 107115.2722
39 - HARLAN TWP-BUTLERVILLE CORP 2319.9960
40 - HARLAN TWP-PLEASANT PLAIN CORP 9614.4231
41 - HARLAN TWP-BLANCHESTER CORP. 3373.3819
43 - MASSIE TWP-CLINTON MASSIE LSD 1472.3685
45 - MASSIE TWP-HARVEYSBURG CORP 59377.6666
48 - SALEM TWP-LITTLE MIAMI LSD 110467.8748
50 - SALEM TWP-MORROW CORP 337565.7531
51 - TURTLECRK-MIDDLETOWN CORP-MCSD 173880.3189
52 - TURTLECRK-MIDDLETOWN CORP-LCSD 80608.0845
53 - TURTLECREEK TWP-LEBANON CSD 1029819.3867
55 - TURTLECREEK TWP-MIDDLETOWN CSD 85484.4984
56 - TURTLECREEK TWP-LITTLE MIAMI 657.2912
58 - TRTLCRK TP-MNROE CP-MONROE LSD 1283883.5724
59 - TRTLCRK TP-MNROE CP-MONROE LSD 253106.4641
61 - LEBANON CORP-LEBANON C.S.D. 5454171.4840
62 - LEBANON CORP.-KINGS LSD 626218.0218
63 - UNION TWP-LEBANON CSD 6016.0728
64 - UNION TWP-LITTLE MIAMI LSD 34580.5948
65 - UNION TWP-MASON CSD 297.3953
66 - UNION TWP-KINGS LSD 130340.3836
67 - UNION TWP-LEB CORP-KINGS LSD 1899.0048
68 - UNION TWP-SOUTH LEBANON CORP 313147.2500
70 - WASHINGTON TWP-LMLSD- CWJ FIRE 10677.9250
71 - WASHINGTON TWP-LITTLE MIAMI LS 7545.5789
72 - WASHINGTON TWP-CLINTON MASSIE 8490.9766
73 - WASHINGTON TWP-LEBANON CSD 11952.9158
74 - WASHINGTON TWP-CMLSD- CWJ FIRE 830.8550
76 - WAYNE TWP-WAYNE LSD 198740.2914
80 - WAYNE TWP-CORWIN CORP-WAYNE LS 77237.1464
81 - WAYNE TWP-WAYNESVILLE CORP 551075.1352
85 - WASH TWP CMLSD CWJFD MLCLD 18240.8543
92 - WASH TWP LMLSD MLCLD 35854.4113
94 - DEERFIELD TWP- MASON CORP-KLSD 1494596.3995
95 - TURTLCRK TWP LEB CORP LEB CSD 23477.0782

How much is that altogether?

  sum(na.omit(Amount_Due_Per_Tax_District$Total_Per_District))
## [1] 60829119

That same number, but written as currency with commas so you can read:

scales::dollar(.Last.value)
## character(0)

Putting single purchases together

First, our data is in the wrong format. When the date, owner, and amount are the same, the sale amount is actually for ALL the properties together. But there are different parcel numbers because large properties are usually composed of several parcels next to each other.

So for example, parcels 123, 456, and 789 are are listed as sold to Company LLC on June 22, 2019 for 6 million dollars. But each is not worth 6 million. They are worth 1, million, 2 million, and 3 million dollars respectively. All the properties together are worth valued at 6 million dollars, BUT the mortgage amount field on each reads 6 million dollars. And we don’t want to accidentally log it as 18 million dollars.

So here we will group by owner, date of mortgage, and the mortgage amount. But then we run into another problem: Some of these have multiple tax districts:

Looking_For_Sort_of_dupulicates <- Commercial_Properties %>% 
  group_by(owner,date_of_mortgage,mortgage_amount) %>% 
  count(tax_district)
datatable(Looking_For_Sort_of_dupulicates)