knitr::opts_chunk$set(echo = TRUE)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(rio)
library(rmiscutils)##
## Attaching package: 'rmiscutils'## The following object is masked from 'package:dplyr':
##
## add_rowlibrary(lubridate)##
## Attaching package: 'lubridate'## The following object is masked from 'package:base':
##
## datelibrary(scales)
library(DT)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.
This is just for commerical properties with land use codes >199 and <500.
#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)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] 60829119That same number, but written as currency with commas so you can read:
scales::dollar(.Last.value)## character(0)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)