Data Journalism Backup Site

Here is my optional tagline

Day 23, Nov. 9

by | Nov 9, 2017 | Student_Work

VA Officials Here Thursday, Nov. 9

Follow Up Interview With Wanda Shull and Dr Mark Worley, chief of staff of the Veterans Health Care System of the Ozarks, 3 pm Thursday Nov 9, here in Kimpel Hall.

Background on Worley:

https://www.fayettevillear.va.gov/about/leadership.asp

Mark Worley

Chief of Staff

Mark A. Worley, MD, PhD serves as Chief of Staff at the Veterans Health Care System of the Ozarks in Fayetteville, Arkansas. Before coming to Fayetteville, Dr. Worley worked at the Central Arkansas Veterans Healthcare System (CAVHS) as a staff psychiatrist, Deputy ACOS for Mental Health Service, ACOS for Education, and eventually as Deputy Chief of Staff. He also served two extended periods as Acting Chief of Staff, at the Alexandria Veterans Health Care System for 3 months (2010-2011) and 8 months at CAVHS (2011-2012).

Dr. Worley is a native of Oklahoma but spent the majority of his adolescence in Olympia, WA. He and his wife of over 30 years have two children. Dr. Worley received his Doctorate in Microbiology and Immunology in 1989 and his Medical Doctorate in 1992, both from the University of Oklahoma. He completed his residence training in Psychiatry in June 1996 where he trained at the University of Arkansas for Medical Science in Little Rock, AR. Dr. Worley has served in several roles in the Arkansas Psychiatric Society including President from 2006-2007. He was also the assistant Scout Master (Troop 604 Little Rock, AR) from 1996 until 2003.

Commander Rohan Briefing: New Time, 8:30 am Tuesday

Briefing will be two tours with the commanders, one at the hospital and one at the veterans home.

Washington County Veteran Service Office building at 8:20 (they have parking their also, it is on North Ave.) and and could meet up with the first group at Parking Lot 4 and walk them over.

Students would be divided into two groups.
First group of six students would need to be at the VA by 8:30 a.m. for an 8:40 tour of the hospital. Students would have the chance to ask questions at stops during the tour and after it.
Second group of six students would be at a certain meeting place (TBA) at the VA at a roughly estimated time to switch with group one and begin the second part of the tour. This tour will be of the veterans home. Same deal on questions.

 

SQL – Importing Data, Creating a Database

For this exercise, we will be using an open source database manager called SQLite that runs as an extension to Firefox. It basically turns your browser into a database program. Firefox Add-On: https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/

This will suffice for our purposes this semester, but if you are going to deal with Big Data, you will need Big Boy and Big Girl data software. A discussion for another day.
Firefox: Install add-on, relaunch browser. Find Add-on in Menu Bar: Tools

Create a database

I wanted to do this test drill to show you the process of creating a database. At the end of the class, you can download the databases I created and work on the queries.
1) Create a database:
Menu: Database | New Database
Call it anything, HMDA1 or Subprime or Donkey … Put it in the same folder as your data.
2) Import Data into SQL. (SQLite only imports .csv data)
Click box that says First row contains column names
3) Define data types
This can be tedious, and here’s why:
We have to tell SQL about each column, whether it is text, numbers or some variation in between.
There are multiple data types:
For this, we are just going to define the columns as either “text” or “numeric” –– more on the other data types later. So you need to separately define 15 different columns as text or numeric. (When you buy Big Boy or Big Girl software, the import sequence is less tedious)
As you import the data, do yourself a favor and open it separately in a spreadsheet so you can see the various columns and what they contain. Check the number of columns in the spreadsheet versus the number of imported columns to ensure they match.
Table Names Are Important: Name It HMDA_2015
–SQL will return errors if you name it HMDA-2015. It doesn’t like dashes in table names
Headings
IMPORTANT NOTE: Eliminate Drudgery of renaming file names – CHECK the box import top row with file names
Here’s an example of what your finished import sequence should look like:
This is what you did in SQL language
CREATE TABLE “HMDA_2015” (“rate_spread” NUMERIC, “respondent_id” NUMERIC, “county_name” TEXT, “loan_amount_000s” NUMERIC, “applicant_income_000s” NUMERIC, “state_abbr” TEXT, “denial_reason_name_1” NUMERIC, “applicant_sex_name” TEXT, “applicant_ethnicity_name” TEXT, “applicant_race_name_1” TEXT, “Am_Indian” NUMERIC, “Asian” NUMERIC, “Black” NUMERIC, “Nat_Hawiian” NUMERIC, “White” NUMERIC, “Hispanic” NUMERIC, “Not_Provided” NUMERIC, “Not_Applicable” NUMERIC)
If you messed up, here is how you edit the headings
1) Structure
2) See columns, click on row and rename

EXERCISES FOR THURSDAY:

QUESTION #1:  Count all black borrowers by county.

QUESTION #2:  Count all white, asian and black borrowers by county, sort by white borrowers.

Question #3: What is the total number of white, Asian and black borrowers statewide?

Question #4: List the counties top order with more than 200 black borrowers

 

Quick Reference Sheet for SQL Commands

Major Commands:

SELECT

FROM

WHERE

GROUP BY

ORDER BY

—

 

SELECT: You tell the database what columns you want.

If you want everything, SELECT *

Example: SELECT * FROM Customers;

–Retrieves EVERYTHING (*) from the database called Customers

SELECT CustomerName FROM Customers;

–Retrieves CustmerName from the database called Customers

 

FROM: The basic command that just identifies which table you’re pulling data from.

Example: SELECT * FROM Customers;

–Retrieves EVERYTHING (*) from the database called Customers

 

WHERE: A filter.

Allows you to grab only certain records do numerical and text string, or a horizontal slice of your data; you set criteria that has to be true in order for rows to be returned

SELECT * FROM Customers WHERE ContactName = “Maria Anders”;

–retrieves all records with Maria Anders as contact name

SELECT * FROM Customers WHERE ContactName LIKE “Maria%”;
–retrieves all records with Maria in ContactName. Note the addition of Like and the wildcard %

SELECT * FROM Customers WHERE ContactName LIKE “M%”;
–retrieves all records with M leads ContactName.

SELECT * FROM Customers WHERE ContactName LIKE “%M”;
–retrieves all records with M ends ContactName.

Here’s a list of operators:

GROUP BY – this groups rows together by a certain field or fields so that you can do math on those groups, such as counting the number of records in each group or summing up the numbers in a field.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
–Here we are counting the number of countries in the database
Remember this command is paired with putting an aggregate function in your SELECT statement, such as COUNT(*) or SUM(noncitizen)
ORDER BY – this sorts your results by a field or fields, ascending (default) or descending (DESC)
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
–Produces a list ordered by country, alphabetized
DESC ASC – List of countries in descending order:
SELECT * FROM Customers
ORDER BY Country DESC;
Add a column
ALTER TABLE “main”.”FL_nativity” ADD COLUMN “Immigrant” NUMERIC
Fill it with stuff
UPDATE FL_nativity
SET immigrant = (naturalized + BornAbroad)

 

Exercise #1: GROUP BY
– This groups rows together by a certain field or fields so that you can do math on those groups, such as counting the number of records in each group or summing up the numbers in a field.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

–Here we are counting the number of countries in the database

Remember this command is paired with putting an aggregate function in your SELECT statement, such as COUNT(*) or SUM(noncitizen)

Exercise #2: ORDER BY
– This sorts your results by a field or fields, ascending (default) or descending (DESC)

SELECT * FROM Customers
ORDER BY Country;
–This sorts all of your data by country

SELECT Country, City FROM Customers
ORDER BY Country;
–This displays just the cities and countries, ordered by country

Exercise #3: DESC

–List of countries in descending order

SELECT * FROM Customers
ORDER BY Country DESC;
–List of countries in descending order

SELECT * FROM Customers
ORDER BY Country ASC;
–List of countries in ascending order

Exercise #4: Counting a List

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
Order by count (*) desc;
–This counts the countries and displays the list in descending order

Exercise #5: Having clause
You can’t use an aggregate directly in a WHERE clause; that’s what HAVING clauses are for.
https://www.w3schools.com/sql/sql_having.asp

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

–This filters all Countries with more than 5 records