Analysis of Virginia Court Data: R Markdown Template

The goal is to answer questions for the Legal Aid Justice Center using the data compiled by Ben Schoenfeld. We need to supply the LAJC with easy-to-read documents in HTML format that has the following sections:

• Question: describes the question we are trying to answer,
• Data: the relevant datasets,
• Data Wrangling: all the code and steps to load the data and prepare it for analysis,
• Results: the results,
• Conclusion: and a succinct description of the answer.

If you would like to use R, please create a document with all these sections using R Markdown. If you are using Python, please create a Jupyter notebook.

There are three ways to access the data, and you can whichever method you feel more comfortable with:

1. You can download CSV files one at a time from https://virginiacourtdata.org

2. You can use the user-interface for the makeshift API we’ve set up. Here, you can enter SQL queries into a text box, execute the code, and download the output in either JSON or CSV format: http://132.145.211.20:8001/criminal-court

3. You can work entirely within an R or Python environment by issuing SQL queries from the following API endpoint:

http://132.145.211.20:8001/criminal-court.csv?

with one API parameter:

• sql set equal to the specific SQL query you want to pass to the API

For example, to extract all the data from the Virginia circuit criminal courts in the year 2000, the SQL code is

select * from circuit_criminal_2000_anon_00

We will need the following libraries:

library(httr)
library(tidyverse)
library(DT)

To pull this CSV directly into R, use the following code:

endpoint <- "http://132.145.211.20:8001/criminal-court.csv?"
my_query <- "select * from circuit_criminal_2000_anon_00"
request <- GET(url = endpoint,
            query = list(
              sql = my_query
            ))
data <- read_csv(content(request, as = "text", encoding = "UTF-8"))

To pull a different selection of the data into R, simply change the SQL code stored in my_query.

The data are now stored as a data frame:

datatable(data)