This page describes the steps taken to prepare the data for analysis. 2009 to 2014 Data (unless marked with API) came from data CD vs API. Due to differences in data structure, slightly different steps were taken. Those highlighted in red only occurred for the 2015 data and those marked with a ** only occurred in the 2009 to 2014 data. Otherwise, data came from the API. and followed the same procedure through all years.

Addresses

Cleaning

• Clean address across the years. See here for more information.

Property Tables

Cleaning

• Create dataset
  • Pull data from tables together to create a single table with variables of interest **
  • Reduced table to variables of interest
• Remove rows that had inactive reasPropertyStatusCode" (T) – These are parcel IDs no longer in use and where there is an inactive, there is an active.

• City Name

  • Add variable (propertyCityName) or all "Arlington" **

  • Make all propertyCityName "Arlington"

• Make NA invalid propertyZipCode

• Make NA the BuildingYearBuild that are 0 or 1000

  • Combine the three columns of year built (each from a different table) into on variable **

• Change 0 to NAs in numberOfUnitsCnt ... no official unit residential properties

• Change 8 observations in 2013 from 200,000+ units to its previous year's value (found when checking across years)... checked in BKFS and found that this was same value as square footage

• Keep those that are residential properties based on classification.

• Keep all properties that have a 1 or were classified as multifamily.

• Remove vacant parcels

  • Required merging with that year's  ImprovementInterior table. 

  • Those that had a extension code of "VAC" were given a 1 (NonVacentParcel) **

  • Those that had a extension code ("can be thought of as a dwelling") were given a 1 (NonVacentParcel)

  • Multifamily properties do not have an extension code.
• Remove rest of parking lots (no land value)
  • Required merging in that year' assessments
  • Remove properties that had a one as they are parking lots (condos have a land value by VA law). Takes a portion of improvement value and places into land
• Remove Multiple Jurisdiction properties
  • Required merging in that year' sale history
  • Remove properties that were in multiple jurisdictions. These had Arlington address, but only a portion of the land was in the county. Varied on proportion in/out.

Transformation

• Recode propertyClassTypeDsc into ACS categories (PropertyTypeACS).
  • Created a dichotomous variables for mixed-used property---Property that were both mixed used and had a "NumberofUnitCnt".
  • Created an unknown Affordable Unit Category as no other information was given on type.
  • Remove commercial property based on this recode.
    
• Recode PropertyYearBuild in ACS categories (YearBuiltACSDwelling)
• Recode numberofUnitCnt in ACS categories
  • This required using PropertyTypeACS to code 1-attached, 1 detached, and Duplex.
  • For Condos, the data was aggregated up by their GIS code (MasterRealEstatePropertyCode) and that the total n for each code was merged with the other units counts.
  • Make both unit count variables NA of those property that had both Single Family based on 1 unit count and multifamily based on high rise apartment 
• Combine Property Year Built and Dwelling Year Built into All Year Buillt

Need to do

• Add Census Tract

Assessments (CD)

Cleaning

• Remove duplicated rows
• Take only that year's assessment
• Remove assessments list at 0
• Remove all non residential. This required matching ID's with that year's cleaned Property table.
• Remove assessments with $0 Improvement value (no building)

• Recode Total value into ACS categories
• Change amounts into constant 2013 dollars (Improvement.Value.Amount.Constant2013, Land.Value.Amount.Constant2013, Total.Value.Amount.Constant2013)
  • For 2015, used 2014 CPI as 2015 is not available yet

Assessments (API)

Cleaning

• Pulled out only variables of interest
• Remove duplicated rows
• Remove assessments list at 0
• Remove all non residential. This required matching ID's with that year's cleaned Property table.
• Remove assessments with $0 Improvement value (no building)
• For assessment classed as "New Construction", brought the following  land value back to replace the 0 land value and created new total value.
• For multiple assessments per a year, took the most recent one.

Transformation

• Recode "assessment.Date" into "assessment.Year"
• Recode Total value into ACS categories
• Change amounts into constant 2013 dollars (Improvement.Value.Amount.Constant2013, Land.Value.Amount.Constant2013, Total.Value.Amount.Constant2013)
  • For 2015, used 2014 CPI as 2015 is not available yet

Need to do

Improvement Dwelling (2015)

Cleaning

• Create dataset
  • Pull data from tables together to create a single table with variables of interest **
  • Reduced table to variables of interest
• Remove duplicated rows
• Make NA the DwellingYearBuild that are 0 or 1000
• Remove all non residential. This required matching ID's with that year's cleaned Property table.

Transformation

• Recode DwellingYearBuild in ACS categories (YearBuiltACS)
  • Compared to the YearBuiltACS in the Property table, there are 0 mismatches.
• Recode HeatingType in ACS categories (HeatingACS)
  • Due to difference in definition (Type of Heating Unit vs Heating Fuel), needed to create "unknown category". These are for heating unit types with unknown fuel source.

Need to do

Improvement Interior (2015)

Cleaning

• Create dataset
  • Pull data from tables together to create a single table with variables of interest **
  • Reduced table to variables of interest
• Removed duplicated rows
• Remove all non residential. This required matching ID's with that year's cleaned Property table.

Transformation

• Create Dwelling+Parcel ID (DwellParcel)
• Recode bathroom information into ACS Yes/No (Bath)
• Created new bathroom (BathACS) and bedroom (BedCount) variables based on aggregated information on DwellParcel. API data for this table had on observation per a floor.
• Recode bedroom count to ACS categories (BedTotalACS and BedCountACS)
  • This was done after restructuring to ensure proper coding of (9 or more)

Restructure

• Aggregated bathroom information and bedroom county up to the parcel.dwelling ID

Need to do

Payment History (API)

Cleaning

• Pulled out only variables of interest
• Removed duplicated rows
• Remove all non residential. This required matching ID's with that year's cleaned Property table.
• Removed Payments that occurred before 2009 and after 2014 (This was done after the transformation of data mentioned later)
• Removed all payments that do no exist in the cleaned Assessment table (which only has assessments on residential properties that have a building and that occuring after that building was constructed)

Transformation

• From TotalAmt, create to variables: Levied (what should have been paid--TaxesLeviedACS),  Paid (the actual amount paid-TaxesPaidACS), Deferral (the actual amount paid-TaxesDeferralACS), Paid (the actual amount Relief-TaxesReliefACS), Adjusted (the actual amount paid-TaxesAdjustedACS)
• Disaggregate "taxDueDate" into "TaxYearACS", "TaxMonthACS",
• Change amounts into constant 2013 dollars (assessedTaxAmt.Constant2013, totalAmt.Constant2013, TaxesLeviedACS.Constant2013, TaxesPaidACS.Constant2013)

Restructuring

• Aggregate data so that there is one observation per a levy/payment (this require the the previous TimetaxDueDate Recode).
  • Those without a value were given a 0 (not NA)
• Aggregate data again so that their is one observation a year to included the annual amount Paid and Levied

Need to do

Sales History (API)

Cleaning

• Pulled out only variables of interest
• Removed duplicated rows
• Remove Sales that have a saleAmt of 0.
• Remove all non residential. This required matching ID's with that year's cleaned Property table.

Transformation

• Recode "saleDate" into "Year" and MDY.
• Change amounts into constant 2013 dollars (saleAmt.Constant2013)
  • For 2015, used 2014 CPI as 2015 is not available yet
• Recode saleAmt into ACS categories

Need to do