Convert Tableau Prep process to R

[jonathanleek]

We ended up creating our dataset using Tableau Prep due to inexperience with R. Would still like to have the process replicated in an R script that can be run along side the rest of our code.

• Download Parcel.mdb from City Open Data Portal

• Join Prcl Table to BldgCom table on Handle, keeping all Handles that appear in BldgCom

• Join Prcl Table to BldgRes table on Handle, keeping all Handles that appear in BldgRes

• Join Prcl Table to BldgSect table on Handle, keeping all Handles that appear in BldgSect

• Union Results of above joins together. The resulting table should contain all Handles that appear in BldgCom, BldgRes, or BldgSect, but not all Handles that appear in Prcl. We'll call the resulting table 'Buildings' for now.

• Join Prcl to Buildings, keeping only Handles that appear in Prcl but not Buildings. The resulting dataset should be all lots without buildings in Saint Louis; we'll call this 'Lots'.

• Union Buildings and Lots

• Download and unzip nearby vacant parcels summary.zip. This csv dataset contains the number of vacant properties within set ranges of every handle in Saint Louis.

• Within that dataset, pivot rows to columns so that each range is a column containing the count of vacant properties within that range.

• Join the resulting dataset with the 'Buildings and Lots' dataset, keeping all parcels that are contained in 'Buildings and Lots'. The result should be our final working dataset.

[chris-prener]

When do you want the reproducible workflow in place?