library(dplyr)We can get property tax delinquency data from Philadelphia’s Open Data site:
csv <- "https://data.phila.gov/api/views/4v5p-qi2q/rows.csv?accessType=DOWNLOAD&bom=true"
taxData <- read.csv(csv, stringsAsFactors = FALSE)Remove columns that don’t interest us:
taxDataCleaned <- taxData %>% select(-c(Unit,Lien.Number))Remove rows that won’t help us because they list no owner:
taxDataCleaned <- taxDataCleaned %>% filter(Owner != "")Clean out the $ found in the money fields and convert to numeric
taxDataCleaned$Principal <- as.numeric(gsub('\\$','',taxDataCleaned$Principal))
taxDataCleaned$Interest <- as.numeric(gsub('\\$','',taxDataCleaned$Interest))
taxDataCleaned$Penalty <- as.numeric(gsub('\\$','',taxDataCleaned$Penalty))
taxDataCleaned$Other <- as.numeric(gsub('\\$','',taxDataCleaned$Other))
taxDataCleaned$Total <- as.numeric(gsub('\\$','',taxDataCleaned$Total))Get just 2000-2015 tax amount owed. 2016 represents bills that may not truly be in delinquency yet, and prior to 2000 is too far back for us to be interested.
lastFifteenData <- taxDataCleaned %>% filter(Tax.Period < 2016, Tax.Period >= 2000)As a first exploration into our data, we’ll want to see who owes the most taxes, in order to understand a bit more about the delinquency landscape in Philadelphia. Let’s limit our investigation to the last year - 2015. We’ll take only those properties that have overdue tax and do some summarizing.
lastFifteenData <- taxDataCleaned %>% filter(Tax.Period < 2016, Tax.Period >= 2000)
totalOwedByOwner <- lastFifteenData %>%
filter(Tax.Period == 2015, Total >0) %>%
group_by(Owner) %>%
summarise(TotalOwed = sum(Total),
NumOverdueProperties = n())Who are the worst offenders?
worstOffenders <- totalOwedByOwner %>% arrange(desc(TotalOwed))
as.data.frame(worstOffenders[1:100,])## Owner TotalOwed NumOverdueProperties
## 1 MARRIOTT CORP 1015073.78 1
## 2 TENET HEALTH SYSTEM 867819.28 2
## 3 NEWCOURTLAND ELDER SERVIC 734112.50 1
## 4 TEMPLE UNIVERSITY-OF THE 321104.26 2
## 5 TRUSTEES OF THE UNIVERSIT 259885.55 1
## 6 FIRST DISTRICT 237718.04 1
## 7 10551 DECATUR ROAD 208566.89 1
## 8 JRE ELEVEN LLC 195444.71 2
## 9 NORTHERN LIBERTIES CHARTE 153515.11 1
## 10 WILLIAMSON OWEN W 115492.71 1
## 11 BEECH LLC 113230.31 3
## 12 FRONT AND MARKET LLC 96200.48 1
## 13 OYR REALTY GROUP 94934.59 1
## 14 PHILA HOUSING AUTHORITY 87513.42 498
## 15 CITY OF PHILADELPHIA 68593.49 23
## 16 EFBEE ASSOCIATES 58853.03 1
## 17 MILLER J T 56481.85 2
## 18 SALTER MEWS LP 56457.51 12
## 19 PHILADELPHIA FILM SOCIETY 55377.12 1
## 20 G&E HC REIT II CARE PAVIL 53445.56 1
## 21 WM CAPITAL PARTNERS XXXV 53033.14 1
## 22 1623 WILLINGTON STREET LL 51817.20 1
## 23 ELECTRA 137 LLC 51661.90 1
## 24 714 MARKET LLC 49886.77 1
## 25 KELSA L P 48183.12 1
## 26 GARDINER ANTOINE 47971.35 21
## 27 PHILADELPHIA MOTORWORLD A 45164.60 1
## 28 INKY INDIANA LLC 44985.38 1
## 29 GREYLOCK HOLDINGS LLC 44158.93 1
## 30 PAN AM RACE ST 43434.05 2
## 31 121 WALNUT ST RESTAURANT 41118.59 1
## 32 CLASSIC CAPITAL GROUP LP 40033.66 19
## 33 2525 INVESTORS LP 39990.61 2
## 34 SAUNDERS GERALD W SR 38853.87 1
## 35 FRANKLIN VILLAGE 38424.50 1
## 36 BARISH MARVIN I 38307.92 1
## 37 D & E CAPITAL HOLDINGS IN 38264.62 1
## 38 FRIENDS OF WEST OAK LANE 37528.54 1
## 39 FOX GREYHOUND INVESTMENTS 36190.05 1
## 40 SIMMONS YOUTH DEVELOPMENT 35570.80 4
## 41 MACRILLO JOHN 34768.10 13
## 42 KEENAN PATRICK J 34523.54 1
## 43 WALSH JAMES 34039.28 79
## 44 NORTHERN LIBERTIES 33533.74 1
## 45 BIZNESS AS USUAL INC 33365.51 16
## 46 MCCANN PHILADELPHIA INVES 32675.12 38
## 47 LARAGIONE JOSEPH V 32275.13 1
## 48 SRAMPICKAL DEVELOPERS LLC 31282.20 1
## 49 SALTER MEWS L P 31076.24 7
## 50 AZAD HOLDINGS LLC 30499.06 1
## 51 PHILA AUTH FOR INDUSTRIAL 29851.12 1
## 52 8609-11 GERMANTOWN AVE LP 29212.51 1
## 53 PRICE JOHN M A JR 28862.76 2
## 54 UNIVERSITY OF THE SCIENCE 28585.44 1
## 55 FOX STREET REALTY,LLC. 28330.34 1
## 56 101 ARCH ASSOCIATES LLC 28100.43 4
## 57 TRUSTEES OF THE ROMAN CAT 28039.79 1
## 58 PHQ LLC 27968.26 1
## 59 SPRING VALLEY REAL ESTATE 27803.45 21
## 60 ABCDE 3001 CASTOR HOLDING 26312.64 1
## 61 JALILVAND MARY LOUISE 26235.64 1
## 62 SHERMAN MARK E 26056.04 2
## 63 SMITH ROBIN L 25126.86 1
## 64 6600 BUSTLETON ASSOCIATES 24865.73 2
## 65 CLIVEDEN-MAPLEWOOD CONVAL 24777.25 1
## 66 PHUNG LLC 24719.53 1
## 67 DU VICTOR 24712.90 9
## 68 R R L LIBERATORE LIMITED 24636.53 1
## 69 GLANZMAN ROBERT 24609.21 1
## 70 PHILADELPHIA DEMOCRATIC C 24565.22 1
## 71 NEW DEVELOPMENT VISTAS 24555.34 2
## 72 CHURCH OF CHRISTIAN COMPA 24388.95 7
## 73 EDUCATIONAL CHARTER DEVEL 24288.48 1
## 74 GREEN PARK PROPERTIES INC 24040.96 6
## 75 BALLARD ALDA 23915.99 7
## 76 ADMIN OF VET AFFAIRS 23575.62 22
## 77 RB3 THOMPSON LLC 23408.28 4
## 78 GONZALEZ GP LLC 22994.40 1
## 79 NORTH HOWARD STREET LP 22680.77 1
## 80 ALLEGHENY REAL ESTATE ASS 22603.24 2
## 81 CHESIRE EQUITY LLC 22463.97 1
## 82 NATIONAL PUBLISHING COMPA 22338.55 1
## 83 ALSTON ANTHONY T 21541.51 17
## 84 KIM SUNG SOO 21507.60 1
## 85 PHILADELPHIA REI GROUP LL 21376.36 23
## 86 ZIG ZAG LLC 21364.18 3
## 87 CENTOFANTI DOMENICO 21308.92 5
## 88 BLAKE KWAMINA E 21176.25 1
## 89 KEYSTONE HOMES LLC 21110.51 8
## 90 NPCH ASSOCIATES 20822.43 23
## 91 TR-JACKSON LP 20400.98 1
## 92 FOUR FOUTHS LLP 20359.58 1
## 93 KEITA SIAKA 20151.45 4
## 94 DIAMOND INDUSTRIES 20062.13 1
## 95 JONES WILLIAM 19847.57 11
## 96 ZING PROPERTIES LLC 19835.63 18
## 97 SHERWOOD DAY CARE 19613.11 1
## 98 KENT FOOD INC 19530.52 10
## 99 FRANCIS JOHANNA M L 19369.35 1
## 100 BIZ AS USUAL LLC 19349.71 6Let’s remove governmental properties. We want to focus on private owners!
worstOffenders <- worstOffenders %>% filter(Owner != "PHILA HOUSING AUTHORITY",
Owner != "CITY OF PHILADELPHIA",
Owner != "ADMIN OF VET AFFAIRS")Let’s set some data up for some fun visualizations. We’ll create a CSV and download it, then load it up in Joy’s Google Drive. That makes it easy to use Google Charts to create our “2015 Hall of Shame” for major delinquents.
write.csv(worstOffenders[1:100,],"worstOffenders.csv", row.names = FALSE)Where did our data end up?
I stashed it on Github and in a Google spreadsheet, and there’s a Google Charts visualization on CodePen that uses the Google Spreadsheet copy. You can see the code, or just look at the visualization.