Agenda

Discuss interview progress

Review the charts from homework

Task #3: Download Census Data with Demographic Information.

Discuss ReadingAssignment #1

Audio gear:
Interviewing

Histogram and Reference Line

Examine Occupations and Salaries in OES Database

Review the charts from homework

Data: Task #2, Poverty Charts
–Statewide trends for Arkansas.
–Northwest Arkansas counties
–Statewide trends for female households
–Northwest Arkansas counties female households.

Assignment #1

Due 11:59 p.m. Wednesday, Feb 14. Post on WordPress

Assignment #1: Census Data – Excel-Tableau.   

Graduate Version of Assignment #1

Rubric

Dual Axis

Compare single mothers in poverty to total poverty rates
Rows % Female Households and %Poverty All families
Click on Green Pill Sum (% Poverty All Families) Dual Axis
Unified Axis.
Left click on legend, synchronise axis

Marks Card – turn all poverty to bars.
Label individual cards

Tableau Dual Axis chart
http://kb.tableau.com/articles/howto/dual-axis-bar-chart-multiple-measures

Build the spreadsheet for question #1

Data is: ACS_16_5YR_DP03 DP03 SELECTED ECONOMIC CHARACTERISTICS   2012-2016 American Community Survey 5-Year Estimates

Copy main data sheet and call copy wages below $25k

Delete all data fields except headers and these columns
HC01_VC74 Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households
HC01_VC75 Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – Less than $10,000 HC03_VC75 Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – Less than $10,000 HC01_VC76 Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $10,000 to $14,999 HC03_VC76 Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $10,000 to $14,999 HC01_VC77 Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999 HC03_VC77 Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999 HC01_VC85 Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – Median household income (dollars)

Standard Data Cleaning
–Rotate header rows, wrap text.
Shrink verbiage from Estimate; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households to “Total households”
Total households %Total households Total households – >$10k %Total households – >$10k Total households – $10kto $14,999 %Total households – $10kto $14,999 Total households – $15,000 to $24,999 %Total households – $15,000 to $24,999 Median household income$
–Specify Arkansas-state
Then find/replace to eliminate “County, Arkansas” from geography labels.

Create Total Under $25 column.
Add Total households – >$10k + Total households – $10k to $14,999 + Total households – $15,000 to $24,999
Create % Under $25k column (total Under $25k / total households)
Copy formulas down
Check math
When satisfied, copy and paste values

Census Demographic data

Household income data for counties and state and national. Gender and demographics of low-wage workers

American FactFinder

https://factfinder.census.gov/faces/nav/jsf/pages/index.xhtml

Advanced Search | Show Me All

Topics | People | Poverty | Poverty (added to Your Selections)

Geographies | County | Arkansas | All Counties Within Arkansas

Geographies | United States

Geographies | Arkansas

Select Table S1701, Poverty Status in the Past 12 Months

Modify Table

—Select top Filter

—Total and Percent Below Poverty Level

—Select second Filter

—Keep Estimate, do not check margin of Error

Download

—Use the Data

Download Again

—View the Data

—Excel spreadsheet

Questions about categories and definitions:

See “Table Notes” to far right on factfinder website after you’ve generated a table.

https://www2.census.gov/programs-surveys/acs/tech_docs/subject_definitions/2016_ACSSubjectDefinitions.pdf

Read: “Poverty Status in the Past 12 Months”
“Poverty Status of Households”

Definitions. working Poor
–Poverty thresholds:
The actual poverty thresholds vary with the makeup of the family. In 2015, the weighted average poverty threshold for a family of four was $24,257; for a family of nine or more people, the threshold was $49,177; and for one person (see Unrelated individuals), it was $12,082. Poverty thresholds are updated each year to reflect changes in the Consumer Price Index for All Urban Consumers (CPI-U). Thresholds do not vary geographically. (For more information, see “Income and poverty in the United States: 2015.”)

https://www.bls.gov/opub/reports/working-poor/2015/home.htm#unrelatedindividual

Weighted Average Poverty
Thresholds in 2015 by Size of
Family
(Dollars)
One person 12,082
Two people 15,391
Three people 18,871
Four people 24,257
Five people 28,741
Six people 32,542
Seven people 36,998
Eight people 41,029
Nine people or more 49,177
Source: U.S. Census Bureau.
https://www.census.gov/content/dam/Census/library/publications/2016/demo/p60-256.pdf

https://www.census.gov/data/tables/time-series/demo/income-poverty/historical-poverty-thresholds.html

Part 2 – Clean This Sucker

–Duplicate main data to a second tab

–Keep just the race/ethnicity categories and percent workers in poverty

–Clean headers – titles for Tableau

Find/Replace

Percent below poverty level; Estimate; RACE AND HISPANIC OR LATINO ORIGIN –
Replace with %

Background: Video on Factfinder

30:00 shows how to use the fact finder
https://www.census.gov/data/training-workshops/recorded-webinars/measuring-america.html

More on Data Cleaning Census spreadsheets

–Download the view and the data versions of large spreadsheets. One to guide you. the other to do the work.

–Merge / unmerge cells

–Find-Replace

— =CONCATENATE(B3, B4).

Checkout of Audio Equipment

Checkout Roster (1)-2ms7q6k

Liability Form (1)-1vsfjgm

Review Reading

Meyer, “New Precision Journalism,” Ch. 1-2
Class Matters (Intro, Ch 1)

Meyer writes: “Precision journalism threatened the twin traditions of journalistic passivity and journalistic innocence.” What does he mean by that? What are the implications of this?

Material For Later….

Statistical terms

Statistical terms used in research studies_ A primer for media – Journalist’s Resource-125sc4a

Income and Poverty Background

DeNavas-Walt Income and Poverty in the United States- 2014 U.S. Census 2015-28iyb03

Adding a Cumulative Distribution to a Histogram

This class demonstration provides a detailed walk-through of how to add a cumulative distribution to a histogram in Tableau.  Students are encouraged to follow along and/or take notes on the provided Student Handout.  This topic uses the Global Superstore dataset.

• Build a histogram with % Female Households in Poverty
  1. Easiest method: click on% Female Households in Poverty and choose the histogram from Show Me
  2. Find out where this is happening: drag Geography to the pane. Now hover your mouse over the blocks and you will see the counties.

• Duplicate the CNT(Profit) pill on Rows by Ctrl+drag the pill next to itself on the Rows shelf
• This gives you a new marks card – now you can create 2 different types of mark for a single data field. On the new marks card “CNT(Profit)(2)”, change the mark type to a line
  1. Change the color as well, if desired
• Right click on the duplicated field on the Rows shelf and make it a dual axis
• Apply a Quick Table Calculation of Running Total to the duplicated field
• [[If students find this histogram to be uninteresting, add a filter on Profit to ignore outliers (maybe go from -500 to +500). Then right-click Profit(bin) dimension and Edit to make the bin size smaller, maybe 25.]]

Create a Reference Line for Poverty

Step 1 – Build the View

1. Drag % Female Households – Children 5 Years to the Rows shelf.
2. Drag Geography to the Columns shelf.

Step 2 – Create Parameters

1. Right-click in the Data pane and then select Create Parameter.
2. Name the parameter “Arkansas Average”.
3. Under Data Type select Integer.
4. Under Current Value, set to 55.8
5. Under Allowable values select All.
6. Click OK.

Step 3: Create the calculated field

1. Select Analysis > Create Calculated Field.
2. Name the calculated field “Reference Line”.
3. In the formula field, enter the following formula:
4. IF[% Female Households – Children 5 years younger]=[Arkansas Average] THEN [Arkansas Average] END
5. Click OK.

Step 4 – Use the calculated field as a Parameter Control

1. Drag the “Reference Line “calculated field to Details. This is the box below Color in the Marks Card
2. Click the arrow to change the measure from SUM to Minimum.
3. In the view, right-click on the Y axis and select Add Reference Line.
4. In the Value drop down menu, select Minimum(Reference Line).
5. In the Label drop-down menu, select Value.
6. Click OK.

Visualize the income distribution in a histogram

% of Female Households – Children 5 Years and Younger.
–Drag to Columns
–Show me: Select Histogram
–Distribution of the poverty level.