In this post I will go over setting up a geospatial database using PostGIS, an extension to PostgreSQL. For information about setting up a PostgreSQL database please refer back to my previous Data Storage. Below is the project plan to ensure I load up all my downloaded map data. Installing and Setting up First you…
Category: blog
Criminal Analysis: Data Storage
Now that we have collected our data, lets work on building a database to store our project data. For this project I have decided to use a PostgreSQL database. The image below provides some planning details to help us implement the database. Installing and Setting up On a Linux machine, getting PostgreSQL installed and running…
Criminal Analysis: Data Search (part 3)
So far I have gathered half of my other data sources. The next up is to get employment and real estate data based on the project plan. Employment Data I generally searched for “Washington DC” and “employment data” but later modified it a little based on the results I got. https://duckduckgo.com/?t=lm&q=washington+dc+employment+data&ia=web https://duckduckgo.com/?t=lm&q=Washington+DC+unemployment+data+historical+data&ia=web DC Department of…
Criminal Analysis: Data Search (part 2)
Now that we have our crime and mapping data, lets work on gathering the other data I wrote down during the previous Planning post. Part 2 of Data Search will focus on our Other Data Sources branch in the project plan. Moon Phases On previous work projects, I have come across a couple of R…
Criminal Analysis: Data Search (part 1)
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…
Clean and Merge (MS Excel)
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…
Create Distinct Lists (MS Excel)
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…
Data Gathering (MS Excel)
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…
Data Conditioning Workflow (MS Excel)
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…
Creating Your Own Ribbon Bar of Tools (MS Excel)
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…
