Now that we have our crime data, lets work on gathering the other data I wrote down during the previous Planning post. Part 1 of Data Search will focus on our Map Data branch in the project plan. Map Data Boundary Data Using the same Open Data website, I just queried for DC boundary and…
Searching for Criminal Activity Data My initial search begins with querying for “Washington DC” and “Crime Data”. This is very broad but it lets me see what is available. I can always get more specific. https://duckduckgo.com/?q=Washington+DC+crime+data&t=lm&ia=web My results yielded a couple pages of interest. Check out the sites that seem interesting or could provide the…
UPDATE (12/6/20): I have removed the original content in this post concerning the Data Search work for the crime data. You can now find that portion of the original post here. In this series, I will be exploring the criminal activity in Washington DC over the past decade. I have explored the data in previous…
As a continuation of the previous article on Creating Distinct Lists, this post will demonstrate how both macros compliment each other. Both of these processes fit into my data conditioning workflow in Microsoft Excel. Below is the original example data and the resultant merged data. Access Macro from MS Excel If you are currently in…
Have you ever wanted to way to create distinct lists for your data to help you clean and transform values? Normally, you are relegated to copying a column of data, pasting the data in an unused range then running remove duplicates. This process can add up in time spent to accomplish this task for one…
Over the years of cleaning, transforming and analyzing data, I have created some useful tools to help expedite tidying and processing. This post will go over a tool designed to gather horizontally tabulated data to transform into a more key:value pair structure. This tool is very similar to R’s tidyr::gather function. For those that don’t…
In this article I will cover the general workflow I have used in my work experiences to condition data in Microsoft Excel. This post references my previous work in the article on Creating Your Own Ribbon Bar of Tools (MS Excel). General Workflow The following image depicts a generalized workflow for data conditioning in MS…
In this tutorial, we’ll walk-through how to create your own customized ribbon bar. Customized ribbon bars can bring tools you have created in Microsoft VBA to an accessible format and ease of use. They also allow you to create toolboxes of programs that can be easily used by other users, who may not be technically…
Now we will be working with and visualizing COVID-19 data on top of our current spatial plots. To see catch up, check out these associated shapefile and GeoJSON posts. The output used used in this post comes from the GeoJSON post since it is in the desired coordinate system. The R script was simplified from…
If you have worked in spreadsheets, undoubtedly you will come across data tables where the column and row headers are merged. However stylistic and visually appealing these merged cells are, they pose issues when you want to run a pivot table on the data or create lookup tables to clean and transform the data. If…
