Using the Zoom H4N Recorder https://snapguide.com/guides/use-a-zoom-h4n-handy-recorder/ Editing audio Not so free software: Adobe Audition ______________________________________________________________________________________________________________________ Adobe Audition Editing Drill File | Import | Filename .. xxx.wav Play audio. Controls at bottom. Expand view — Magnifying glass: Zoom In – Time Define / Highlight section to cut. Edit | Cut Save As New File – Adobe Audition File Export as an .wav file. Sample Rate: 44100. Stereo. 24 bits. Assign correct folder Fade in and Fade out Audio https://helpx.adobe.com/audition/how-to/audition-add-audio-fades-cc.html
Assignment #xxx: Interviews Due xxx. Post on WordPress Write a 500-word news story based on an interview with a person classified as working poor in Northwest Arkansas. Consider diversity in race and occupations when selecting your interview subject. Illustrate this post with the following: A photo of your interview subject. Pay attention to background, lighting and composition. Use a professional Zoom recorder to gather and produce a 30-second – maximum 2 minute audio clip from your interview. Introduce your subject and the context of the interview, like you would hear on a podcast. Gather background or ambient sound of the workplace. Mix this and embed this in the blog post.
Additional Material for Graduate Students: Interview a second person, meaning you are interviewing two people in total. The second interview has a photo and an audio clip, as described above. No graphics needed for the second interview. Everyone: Post the interview, graphic and photo in a single blog post, weaving them together to tell the story of your interview subject. Follow a news story format and follow AP style. This is due XXXX. Post on WordPress
2) Topics | Race and Ethnicity | White –Note that the “White” filter displays below
3) Geography | County | Arkansas | All Counties in Arkansas –Note that the “All counties in Arkansas” filter displays
4) Search!
5) Select Table Named RACE American Community Survey Total Population TableID: B02001
6) Switch to 2016: ACS 5-Year Estimates Detailed Tables
7) Customize Table. Download. Make Sure to Download 2016: ACS 5-Year Estimates Detailed Tables
Clean Census Data 1) Create Data Dictionary 2) Duplicate Sheet 3) Four corners select and copy 4) New Sheet. Paste Special | Transpose –the races are now the rows –Filter by Estimate: Contains Estimate, Delete 5) Edit Headers: White, Black, Hispanic 6) Check totals – do they add up? 7) Two races including Some other race. Two races excluding Some other race, and three or more races (delete) 8) Save and Load to Tableau 9) Build a Arkansas Population Map by Race
Build Arkansas Population Map By Race --Clean the County Field ", Arkansas" --Create Calculations for Percentage Population by Race: Calculated Fields
Advanced Search Filters | Geography Counties | Arkansas | All counties Filters | Topics | Income and Poverty Filters | Topics | Race and Ethnicity Filters | Years | 2016 Filters | Text Search in Find a Filter: “Income” | Select “Income (Households, Families, Individuals)
Search
Download White Only, Black Only, Hispanic or Latino Householder
Your tables will say this: HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2016 INFLATION-ADJUSTED DOLLARS) (WHITE ALONE HOUSEHOLDER) Survey/Program: American Community Survey Product: 2016: ACS 1-Year Estimates Detailed Tables
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 Geographies | County | Arkansas | All Counties Within 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
Selected Economic Characteristics DP03 2012-2016 American Community Survey 5-Year Estimates
Standard Data Cleaning
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)
–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
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).
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
–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).
Cleaned and download 2011-2015 estimates with detailed poverty metrics Ark Counties full income search 5-10-17 ACS_15_5YR_DP03
Students assigned geographical location for Census data.
Questions:
–Number and Percentage of Minimum Wage Households?
–Compare to National, State Averages
–Produce basic Tableau chart
Data Cleaning and Joining Exercise
--You will join the CollegeScorecard data with 2017 Census Data with the average household income
The goal is to compare the average student debt in a college to the income in the surrounding town.
This in-class task will stretch over two class sessions, unless you are a DATA KING OR QUEEN
This exercise builds on the data analysis, cleaning and visualization skills you learned this semester.
Task 1: Retrieve the Census Data
Use Excel
--Examine the data dictionary.
--Examine the data, the range of incomes and number of cities, towns and places
--Create a copy of the Census sheet for the data cleaning
Task 2: Data Cleaning
You will need to match the town in the Census to the city in College Scorecard.
Look at the "city" column in the College Scorecard data: ARDebt17_10_23.csv
--Tip: Data cleaning tools in Excel: Text to columns and find and replace
Task 3: Joining
--Join the Census data to the ARDebt17_10_23.csv in Tableau
--Chart the Income and the Grad debt by the 10 largest public schools
Task 4: Analysis
--Construct a Ratio of Grad Debt to Per Capita Income. Map it
This module addresses: –Best practices in data management –Organizational tips for files –Data documentation skills
Staying organized is a key problem for beginning data students. You can’t find files. You have duplicate files and struggle to find the latest version. Your data software fails because it can’t find your files. You can’t remember where you got the source data or what the headers mean. You waste hours with this stuff when you really should be reporting.
I want to put an end to this nightmare. These organizational tools below are essential.
Storage
Create a folder on your hard drive, call it Dataspring21, and put all class materials in that folder.
--Within Dataspring21, create the following folders.
1) Final_Work
2) Older_Files
Other people will have these standard folders in their projects. Up to you if you want to do this
--Data
--Scripts
--Storage
--Output
BACK IT UP. Early, often, always, constantly.
Purchase a 2 TB USB external storage drive, such as this
Organize Your Data: Finder
Finder, not always up for the job
1. Sort by grid, by date. –This allows you to see the latest version of your files.
2. Path name. –Follow this convention: Description of File With Some Detail, Date. If you are editing something, put your initials at the end. –i.e.: Covid_Master_File_Jan_11_2021-rsw
3. Copying File Paths from the Mac Finder. Navigate to the file or folder you wish to copy. Right-click (or Control+Click, or a Two-Finger click on trackpads) on the file or folder in the Mac Finder While in the right-click menu, hold down the OPTION key to reveal the “Copy (item name) as Pathname” option, it replaces the standard Copy option Once selected, the file or folders path is now in the clipboard, ready to be pasted anywhere
For all of your spreadsheets, create a separate tab and include the following:
--Full name data set
--Full URL
--Date of data
--Any code book to define the column or row headings
For your Tableau and R scripts, note the same in a Data Dictionary file that you can access
easily in cloud storage. Suggestion: Evernote (free version, storage limits); Notepad in iOS;
Note in Gmail; Google Doc; Notes in iOS; Stickies in iOS; etc.
Data Diary Examples The following material was posted on NICAR-L, a listserv for data journalists. There are some great examples of how the pros use data diaries / data dictionaries in their workflow.
1) Geoff This is a great question, and I’m finding as I think through my response that it’s helpful to remind myself of good practices.
I use Jupyter notebooks for when I’m doing analysis or exploration in Python or SQL and R Markdown for when I’m doing it in R. However, I would stress that any data diary you keep and keep in a detailed way that is useful to you and others later, regardless of format, is better than the one you don’t.
A few things that I try (but don’t always succeed) to do:
– Link to the source data, summary reports and codebooks near the top of my notebook. This is both a convenience to me, because I refer to these often, and especially to others who may not have seen those things before. – Put a high level summary of why I’m interested in the data and what I’m trying to find at the top of the notebook. This keeps me focused as I’m doing my exploration and also is helpful for others who might be skimming. – Keep a parking lot of questions (or potential concerns about validity or cleanliness of data) near the top of the notebook. That way I can quickly capture things I think about as I’m exploring or analyzing the data, while still staying focused. – Near the end of my day (or the first thing the next morning), do a quick pass over a notebook I worked on during the day. Do my notes still make sense? Are they as clear as they could be? If not, try to clean them up. If I don’t have time at the moment, I at least leave a “TODO” note to flag the section as needing some love. – Share the notebook with someone else as early as possible, even if you’re still in-progress. This is the most helpful way to know if I’m capturing your process with enough granularity. Or maybe I’m getting too granular. If so, is there a way to summarize process and findings at the top of a section? – If using code, don’t give a play-by-play of the code in text. Instead, describe what I’m trying to find out, why it’s important and why I’m taking a particular approach. Also note any assumptions my code is making.
Hopefully this is helpful.
Best, Geoff 2) Christian McDonald Oh, do I have feelings about this one… I keep a data diary for myself that has everything from notes about public information requests, notes about where I got data, descriptions of what I did, sql queries and all kinds of things. I sometimes also make a data report that is really RESULTS of what I learned, as opposed to how I got there in the data diary. The data report is more for other reporters, editors and maybe sources, but the diary is for me, so less formal. These days I’m trying to script more of my work using Jupyter Notebooks, which then tends to be a mix of the two. It has info about where the data came from and the code that made the result. Sometimes it is written for future me, sometimes for the public. Generally, I’ll still keep a personal data diary just for my future self, ‘cause I can’t remember what I did yesterday much less last week. Data diaries I tend to write in markdown files on my machine so code doesn’t get wigged with curly-quote translations. Data reports are typically Google Docs or Jupyter Notebooks on Github.
Fall 2018: Student Loans
https://wordpressua.uark.edu/razorback-reporter/
Katie Beth Nichols, left, discussed the Razorback Reporter student loan project at the 2019 National Institute for Computer Assisted Reporting. With her are veteran journalists Holly Hacker of the Dallas Morning News and Annie Waldman of ProPublicaLow-Wage Workers: Spring 2018
Excel Exercise: Transit Data and Calculating a Rate
Review: Mac OSX Basics
Basics of Data Analysis
Transparency Reliability: How sure are we that we got the right answer? That we’ve done everything correctly? Replicability: If we had to do it all again, would we get the same answer? If someone else did it, would they? Transparency: If our results are challenged, can we show exactly what we’ve done to defend it? –Matt Waite
Data Analysis
— Review methodology with one or more other data people — Check results to other available comparable data — Ensure all record counts are consistent across stages — Check averages — Examine outputs to ensure logical consistency (do things that should add up to 100% add up to 100%?) — Recheck all coding line by line if possible or in aggregate if not — Re-read all programs/scripts — Re-run entire analysis from scratch — Check each number against analysis or source material prior to publication — Recheck each number against analysis or source material on each draft
Credit: Daniel Lathrop. Dallas Morning News
AP Stylebook Entry on Data Journalism
Data sources used in stories should be vetted for integrity and validity. When evaluating a data set, consider the following questions: –What is the original source for the data? How reliable is it? Can we get answers to questions about it? – Is this the most current version of the data set? How often is the data updated? How many years of data have been collected? –Why was the data collected? Was it for purposes of advocacy? Might that affect the data’s reliability or completeness? Does the data make intuitive sense? Are there anomalies (outliers, blank values, different types of data in the same field) that would invalidate the analysis? –What rules and regulations affect the gathering (and interpretation) of the data? –Is there an alternative source for comparison? Does the data for a parallel industry, organization or region look similar? If not, what could explain the discrepancy? –Is there a data dictionary or record layout document for the data set? This document would describe the fields, the types of data they contain and details such as the meaning of codes in the data and how missing data is indicated. If the data collectors used a data entry form, is the form available to review? For example, if the data entry was performed by inspectors, is it possible to see the form they used to collect the data and any directions they received about how to enter the data? Data and the results of analysis must be represented accurately in stories and visualizations. Any limitations of the data must also be conveyed. If one point in the analysis is drawn from a subset of the data or a different data set altogether, explain why this was done. Use statistics that include a meaningful base for comparison (per capita, per dollar). Data should reflect the appropriate population for the topic: for example, use voting-age population as a base for stories on demographic voting patterns. Avoid percentage and percent change comparisons from a small base. Rankings should include raw numbers to provide a sense of relative importance. When comparing dollar amounts across time, be sure to adjust for inflation. When using averages (that is, adding together a group of numbers and dividing the sum by the quantity of numbers in the group), be wary of extreme, outlier values that may unfairly skew the result. It may be better to use the median (the middle number among all the numbers being considered) if there is a large difference between the average (mean) and the median. Correlations should not be treated as a causal relationship. Where possible, control for outside factors that may be affecting both variables in the correlation. Use round numbers where possible, particularly to avoid a false appearance of precision. Be clear about limitations of sample size in reporting on data sets. See the polls and surveys section for more specific guidance on margin of error. Try not to include too many numbers in a single sentence or paragraph.
Write a minimum two paragraphs on the Basics of Data analysis readings. Discuss two items that impressed you the most and explain why.
Due XXXX, 11:59 pm on Blackboard.
Sarah Cohen’s “Numbers in the Newsroom” is a classic in journalism numeracy. She is a Pulitzer-winning journalist at The Washington Post, a former Duke University professor, a data journalist at The New York Times., now a professor at Arizona State University. That’s why we read her book.
* Limit yourself to 8- 12 digits, including dates such as 2012, in a single paragraph. –This allows us to stress the most important numbers
–Simplify your story using rates, ratios or percentages. “One in four” = ratio or rate. “Forty percent” = ratio or rate. 235 deaths per 100,000 is another. See pg. 11
*Round off! Unless you’re dealing with really small numbers, decimal points may not be meaningful. “I’m a big fan of rounding,” Cohen said.* To make a very small number more understandable, divide it into 1. For example, .0081 is the proportion of the U.S. population who die every year. 1/.0081 translates to 1 in every 124 Americans die each year.* If you have a story filled with numbers – and not people — it needs to be really, really short.
* Portion of whole – For example, at the time of the Million Man March in 1995, a turnout of 1 million black men would have represented 1/12th of all the black men in the country at the time.
–In the spirit of “memorizing numbers on your beat,” find three statistics about poverty in this dataset
–Construct a rate or ratio about the number of households earning $15,000 to $24,999 for the U.S., Arkansas, and the counties with the highest and lowest percentages in this category. Remember – “percents are Fractions. Fractions are percents”
Notes: –Create data dictionary, backup, do four corners test –Be very careful about copying different block of data to a new sheet: mixups –Copy labels over and then delete them just to be sure all is aligned –Class walkthrough with 2008 – 2009 derailments –Be very specific about the headers: Total Derailments 2009, Vehicle Revenue Miles –Word Wrap for headers –We are constructing two derail rates, one in 2009 and another in 2008. –Results are 0? Wait, check the decimal tool –Results to two decimals. Rarely more than that –Copy of acronym definitions to data dictionary
Exercise #1: –Calculate derailment rates for 2008-2013, determine the average rate, which agency had the highest average rate?
Exercise #2: –Calculate the rate of fatalities (excluding suicides) by total miles (vehicle revenue miles) –Copy all of the Total Heavy Rail Fatality Sum, excluding suicides and all of the Vehicle Revenue Miles (VRM) –Create rates for each year, then average them
Which city has the highest rate of fatalities (excluding suicides) over the last six years and where does Chicago rank?
Exercise #3: Over the six years, did Chicago transit have more derailments than other major city transit systems? Is it getting better or worse?
Which year was the worst for all major transit in terms of fatalities (excluding suicides)?
How many suicides happened at CTA in 2013?
What questions should I ask the DOT data clerks regarding the data? What other data might be useful to mine after this story runs?
Make Two Folders: Original. Working. Duplicate Spreadsheet: Right Click | Duplicate Data Dictionary: Who are you and where did you come from Copy sheets, Rename Tabs Copying Formulas: The Black + Sign Sorting Brain Storm: Story Ideas from Sorting Difference Formatting Data in Dollars Percentage Change: NOO! Part of Whole: Anchoring Values - i.e. $C$17 Basic Chart
Click here for the data: UrbanPop Click here for assignment: Exercise #1 Answer these questions: Sorting –Which urban agglomeration was the largest in 1950? –Which is expected to be the largest in 2030? Percentage Change Formula: (New number-Old Number)/Old Number * 100 and use % symbol Create column What is difference. —copy forumula What is percentage change —copy formula Percentage Change –Which had greatest rate of change between 1950-2015? –Are any urban areas expected to lose population from 2010 to 2030? –If so, how many and which one is expected to lose the most? –Which United States urban area is expected to have the largest percent increase from 2015 to 2030?
Refresher on Mac OSX operating system Here is a short video course that you can skim through and get up to speed on how to use the Apple operating system, OSX. https://www.linkedin.com/learning/macos-mojave-essential-training/understand-macos-the-foundation-of-working-with-a-mac?u=50849081 I would hammer through the following as soon as possible. Chs. 1, 3 are important Chapter 2: Finder will be crucial. Ch. 5 on downloading from the web is important Ch. 4, 13 should be skimmed Chs 6-11 aren’t important for our class
To Add:
Quiz
Excel Quiz Due Sept. 7, 11:59 p.m.
https://learn.uark.edu/webapps/assessment/take/launchAssessment.jsp?course_id=_244039_1&content_id=_7327798_1&mode=cpview
Quiz: Basic Excel. See Blackboard. Quiz due Saturday, Sept. 7
Excel Exercises:
NICAR Coursepack
Intro Excel w Exercise #1
Exercise Filtering: Crime Rates and Ratios
--Find Average Crime Rate Statewide
--Filter above and below average
--Find Average Population
--Filter above and below average
NICAR coursepack: Pivot Tables
In class exercise: MLB Salaries
QUESTIONS
1) Did the National League or the American League pay more in salaries? Who has the higher average salary?
2) Which division pays the most in salaries? The least?
3) Which team had the most players on the roster?
In-class exercise WorldBankUsing the WorldBank data , build a Pivot Table.
--Trick: Shift+Ctrl+8
--Produce a list ranking the countries with the most companies disbarred, sorted descending. Copy the results and paste into a new tab.
--Produce a list of the firms that have more than one disbarment, sorted descending. Copy the results and paste into a new tab.
What is the most common violation, and how many times did it occur?
Pivot Table
Class Exercise: Student Loans - Pivot Table
Analyze Student Loan Data
Sort the Data:
-Sort by Schools with Largest Enrollment. Write a text answer with the top five schools by enrollment.
-Sort by Schools with Highest Median Debt for Graduates. Write a text answer with the top five schools by Median Debt for Graduates.
-Sort by Schools with Highest Median Debt for Student Who Withdrew. Write a text answer with the top five schools byHighest Median Debt for Student Who Withdrew.
Megan Putney, Mike's Hard Lemonadehttps://training.uark.edu/professional-development/courses/tableau.php
* Import data. Headers have to be right * Strings and types in the data view
Tableau: Arkansas 2014 Crime Rates
Build a crime chart.
FBI Crime Data for 2017 1) Import spreadsheet into Tableau 2) Original Data 2017 3) Data Interpreter --Headers become visible --Rename State to City-Town 4) Are Words as ABC? Are Numbers as #? 5) New Worksheet 6) City-Town to rows. 7) Violent crime to columns. Check that measure is SUM 8) Add violent crime to colors. --Click colors. Green-Red diverging. Reverse colors 9) Add violent crime to labels 10) Labels. Formatting. --Headline: Click "Sheet 1" 11) Sort ascending --This chart is ridiculously long! 12) Filter: 50 or more violent crimes --Drag Violent Crime to Filter. 50 minimum. Your end product should look like this:
Part 2:
1) Export a jpg version of your chart --Worksheet | Export | Image 2) Engage in excessive self-congratulatory behavior.
Power Users: Once you have done that, add a violent crime rate 1) Duplicate Sheet 1 --This copies your whole work to a new sheet 2) Calculate a Violent Crime Rate Per 1,000 --Click on Violent Crime - down arrow | Create | Calculated Field --Name the Calculation "Violent Crime Rate Per 1,000" --Divide Violent Crime into Population: --Add a Division Symbol after [Violent Crime]/[Population] --Enclose in parentheses: ([Violent Crime]/[Population])*1000 3) Drag Violent Crime Rate Per 1,000 to Columns. --Remove Violent Crime --Sort 4) Check your colors and labels. --Drag Violent Crime Rate Per 1,000 to Colors --Drag Violent Crime Rate Per 1,000 to labels --Drag Violent Crime Rate Per 1,000 to filter
4) Headline, Format Legends, Export jpg
Tableau Ninjas: Once you have done that, chart a stacked bar chart with violent and property crime.
Table Calculation: Add a new column divides graduate debt into enrollment –In Measures Data Tab, Left Click on GRAD_DEBT_MDN, Create Calculated Field –Put in this formula: sum[GRAD_DEBT_MDN]/[UGDS] –Rename This as: Grad Debt Per Capita Enrollment Drag INSTNM to Rows #Grad Debt Per Capita Enrollment to Columns Format with a Title, add appropriate annotations. Color the graphic, include labels.
Here’s a start: –Download and clean the data: US Ark Counties Poverty ACS_16_5YR_DP03_with_ann-1w6iwss We have a lot of data in this spreadsheet. Let’s simplify things. We will visualize just one column- Column J –: “Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999” –Copy the “Edited Poverty” tab and rename it Min Wage Only –Delete all of the columns except for the Percent; INCOME AND BENEFITS (IN 2016 INFLATION-ADJUSTED DOLLARS) – Total households – $15,000 to $24,999″ –Fix the column headings so it plays nice with Tableau. Delete the Row 1 “GEO.display-label…” –Simplify your new column B: % Households – $15k-$25k –Save, backup etc and open Tableau. –Import your data –Open new tab –Geography to Columns. % Households $15k- $25k to rows –Sort by largest-smallest –Filter by Geography – drag Geography to Filter. Filter to keep the following: Arkansas, Benton,Carroll, Crawford, Madison, Sebastian, United States, Washington Formatting. This is the standard formatting you will apply to all of your charts in some fashion. We are simplifying and clearly marking the labels –Drag to Label in Marks box: % Households $15k- $25k to rows –Format Label to % – Format | Pane | Default | Numbers | Numbers: Custom | 1 decimal. % as suffix. –Format Y Axis to %. – Format | Axis | Scale | Numbers | Number: Custom | no decimal, % as suffix. –Color the bars. Drag to colors in Marks box: % Households $15k- $25k to rows. Edit colors to blue – red scale, reverse colors so red = highest. –Label graph: Double Click on “Sheet 1” above the chart. Text box emerges with <Sheet Name> Type in name of chart: Minimum Wage Households Percentage of Households Earning $15,000-$24,999 in 2016 Source: U.S. Census –Fix X axis labels (squished) . Drag the right chart out to expand the visualization. Export image –Worksheet | Export | Image –Click off color legend option –Title Minimum Wage Arkasas. Save as .jpeg
Institutions to Rows Graduate Debt to Columns Withdrawal Debt to Columns Dual Axis Chart for the two debts Display in Side by Side Bar Chart.
Edit Chart Title Edit Labels – left click, edit alias Rotate School Labels – left click, rotate label Edit Y Axis Legend – left click, format. Scale. Numbers, Custom. No decimals
Part 2: 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
Assessment: Build a chart with a new calculated field dividing graduate debt into enrollment Visualize by school Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx), Provide a headline and source. 2. Build a chart with a new calculated field dividing dropout debt into enrollment Visualize by school Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx), Provide a headline and source. 3. Build a Dual Axis Chart the Calculated Fields, Graduate Debt / Enrollment and Dropout Debt / Enrollment Filter by Beauty Schools Visualize by school Format the X and Y axes, Label the rows with specific debt to enrollment (UofA is $xxxx), Provide a headline and source. Export and upload the three image files and load to Blackboard. Write two story ideas to come from this data work in a Word file.
Clean Zip Codes In Excel –Copy data into new sheet: Call it “Fixed Zip” –Insert dashes into the errant zip codes –Save and import Fixed Zip New sheet, begin map: double click on zip codes
Problem #2: Associate Map Data to State in Tableau Tableau – Go to: Map | Edit Locations. A bunch zip codes in red. Not displaying. Further edit the zip codes. It doesn’t like the mix of short and long zip code. Fix: Tableau. Data. Zip Code. Split. –New Column has only five-digit zips. Call it Zip1 – delete the other. It keeps the original. Nice.
Dual Mapping
Using ARDebt9-17 New sheet, begin map: double click on ZIP Marks Card | Map Drag White students to Color box, Convert to Average.
Click on Longitude pill in Columns. Press Command. Drag to Right. Release mouse –Creates two Longitude pills and two maps –Marks Card Now Has Controls for Two Maps
Marks Card Has Two Maps. Lower Map, Drag UGDS Black to color. Marks Card, switch to Circle. Change Color to Orange. Upper Map, Drag UGDS White to color, Change Color scheme to Green In Columns, Select Down Arrow on Longitude | Dual Axis –Maps are combined
Drag City to Marks Card | Tooltip
Edit Tooltips so data displays properly –Select Worksheet > Tooltip. Rename the items so they are in English! –UGDS_BLACK = Black Enrollment. –Format | Pane | Fields AVG(UGDS _ Black) | Default | Numbers | Percentage to one decimal
Class Assignment and Homework: Dual Maps: – First Gen, Non-first Gen Debt all schools: Ramirez, Hennigan, Fracchia – Male, Female Median Debt: Baird, Bonner, Zimmardi – Debt of Students Who Withdrew, and Graduated: Ross, Tillson – Low-Income, High-Income Students: Ellington, Neubaum, Duby – Trend in Debt, 2015-16 vs 2016-17: Borgognoni and Thompson
–Post on WordPress. Create a new post Use Divi Builder, the Purple box above the formatting bar Insert columns, pick a full row Insert module, pick </> Code Paste your Tableau Public embed code in the Content box Scroll down, change the Admin Label to My Mind-Blowing Tableau Graphic (or something more humble). Save and Exit Change Page Layout (upper right corner) to Fullwidth. Publish Revel in your nerd powers
Tableau Interactive Graphics Dashboards with Poverty Data. Sizing and Formatting. Navigation from Dashboard to Actual Graphic. Simplicity in Design for Dashboard, Space for Central Element. Filters: Basic Filters and Filter as Sheet
Basic Filter: Applies to One Worksheet –Click upper righthand menu on graphic, see Filters, select the appropriate data source. Here it would be total incomes under $25,000. –Filter is created and placed at the bottom of graphic. You can format and move the filter. –Drag by tab in upper center of filter object. Move it to the top of the filtered graphic. “Super Filter”: Add a Filter to Control All Cities on Worksheet Filters on Dashboards When we click through a filter, we see that by default, it only applies to the view it came out with. We can make this filter apply to the entire dashboard. –Click on the filter (down triangle icon), open the menu. –Apply to Worksheets, select “all related data sources” Now when we make a selection, it applies to the whole dashboard Similarly, we can make a view act as a filter for the dashboard. Click on the view to bring up the header bar, and click the filter icon. Now if we select a header such as Second Class, all the relevant views are filtered.
Exercise: Format Workbook for A Phone A majority of our readers will view these stories on their phones. For the Fall 2017 Veterans Health Care project, some 70 percent of the readership was on a mobile device. Schleuss said the Los Angeles Times receives about 55 percent of its traffic on mobile devices. Let’s format this workbook for mobile. The design choices will be radically different. –Duplicate your workbook –Design it with the mobile template on the left: See Upper Left Tab: Dashboard: Device Preview. Switch to Phone –Before doing any radical surgery to your graphics, consider duplicating them and bringing in the new versions into the mobile template.
Here is some detailed guidance on your final assignment graphics and dashboards. I think these are good ideas. But if you have something better, let me know and edit this Google Doc. And we will discuss. We will review some of Megan’s super Tableau tricks in class and then you will use a chunk of the remaining time to hammer out these dashboards and ask questions. Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm Format Filters – See video below Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Here is some detailed guidance on your final assignment graphics and dashboards. I think these are good ideas. But if you have something better, let me know and edit this Google Doc. And we will discuss. We will review some of Megan’s super Tableau tricks in class and then you will use a chunk of the remaining time to hammer out these dashboards and ask questions. Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm Format Filters – See video below Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Dashboard Design Concepts
DashBoard Design Version 1 Created by ravindra kumar on May 10, 2013 10:35 AM. Last modified by ravindra kumar on May 10, 2013 10:41 AM. Role :- 1)Strategic 2)Analytical 3)Operational
1.Dashboards for strategic purposes The primary use of dashboards today is for strategic purposes. The popular “executive dashboard,” and most of the dashboards that support managers at any level in an organization, are strategic in nature. They provide the quick overview that decision makers need to monitor the health and opportunities of the business. Dashboards of this type focus on high‐level measures of performance, including forecasts to light the path into the future. Although these measures can benefit from contextual information to clarify the meaning, such as comparisons to targets and brief histories, along with simple evaluators of performance (for example, good and bad), too much information of this type or too many subtle gradations can distract from the primary and immediate goals of the strategic decision maker.
2. Dashboards for analytical purposes Dashboards that support data analysis require a different design approach. In these cases the information often demands greater context, such as rich comparisons, more extensive history, and subtler performance evaluators. Like strategic dashboards, analytical dashboards also benefit from static snapshots of data that are not constantly changing from one moment to the next. However, more sophisticated display media are often useful for the analyst who must examine complex data and relationships and is willing to invest the time needed to learn how they work. Analytical dashboards should support interactions with the data, such as drilling down into the underlying details, to enable the exploration needed to make sense of itthat is, not just to see what is going on but to examine the causes. For example, it isn’t enough to see that sales are decreasing; when your purpose is analysis, you must be made aware of such patterns so that you can then explore them to discover what is causing the decrease and how it might be corrected. The dashboard itself, as a monitoring device that tells the analyst what to investigate, need not support all the subsequent interactions directly, but it should link as seamlessly as possible to the means to analyze the data.
3.Dashboards for operational purposes When dashboards are used to monitor operations, they must be designed differently from those that support strategic decision making or data analysis. The characteristic of operations that uniquely influences the design of dashboards most is their dynamic and immediate nature. When you monitor operations, you must maintain awareness of activities and events that are constantly changing and might require attention and response at a moment’s notice. If the robotic arm on the manufacturing assembly line that attaches the car door to the chassis runs out of bolts, you can’t wait until the next day to become aware of the problem and take action. Likewise, if traffic on your web site suddenly drops to half its normal level, you want to be notified immediately.
As with strategic dashboards, the display media on operational dashboards must be very simple. In the stressful event of an emergency that requires an immediate response, the meaning of the situation and the appropriate responses must be extremely clear and simple, or mistakes will be made. In contrast to strategic dashboards, operational dashboards must have the means to grab your attention immediately if an operation falls outside the acceptable threshold of performance. Also, the information that appears on operational dashboards is often more specific, providing a deeper level of detail. If a critical shipment is at risk of missing its deadline, a high‐level statistic won’t do; you need to know the order number, who’s handling it, and where it is in the warehouse. Details like these might appear automatically on an operational dashboard, or they might be accessed by drilling down on or hovering the mouse over higher‐ level data, so interactivity is often useful. The ways that dashboard design must take different forms in response to different roles are clearly worth your attention. We’ll examine some of these differences in more detail in Chapter 8, Putting It All Together, when we review several examples of what works and what doesn’t for various purposes. https://public.tableau.com/profile/ravi2917#!/vizhome/LorealDemo/StrategicDashboard
Format Numbers
Format the numbers once in the Measures: Right Click – Default Properties | Number Format | Aggregation | Median Right Click – Default Properties | Number Format | Aggregation | Percentage (or Currency …etc)
Build a graph with two measures, one by bar and the other by line. Two measures. One Debt, one Default. Switch one to line —Drag 3-year default rate to sheet —Drag College Name to columns —Drag Median Debt to far right hand column —You have dots on view. Change median debt from Automatic to a line in Marks Card —Change 3-year default rate from Automatic to bars in Marks Card
Format Legend Titles Correctly – Drag Legends to Bottom, Resize and Rename so words fit correctly
Relational Databases: How They Work
Link on Common Fields
Inner, Left, Right joins
http://www.tableau.com/learn/tutorials/on-demand/join-types-union
Joining the 2015-16 data to the 2016-17 data
1) Open Tableau Workbook ARDebt9-17.twbx
2) New Sheet
3) Data | New Data Source
4) Import ARDebt2015-16.xlsx
5) Add New
6) Import ARDebt9-17
7) Inner Join on Unitid
8) Rename as AR2015-17MERGED
9) New sheet, CONVERT TO measures. CAREFUL! DO THIS ONE AT A TIME. CONVERT 2015-16, THEN CONVERT 2016-17
You now have two sets of measures. One for 2015-16 and one for 2016-17
Build a chart contrasting Grad Debt Mdn changes
1) from 2016-17, Instm to Rows
2) Find Grad_Debt_Mdn for 2015-16. First Rename it GRADDEBT15-16. Then drag to columns
3) Find Grad_Debt_Mdn for 2016-17. Rename it GRADDEBT16-17. Drag to columns.
4) Sort by GRADDEBT16-17. Which ones increased?
Build a chart with calculated field Grad Debt Mdn changes
subtract GRADDEBT16-17 from GRADDEBT15-16
Tableau Can Be Dangerous, Part 1
Inner Joins. Outer Joins.
--The evils of the inner-join and how it can mess up your math.
Relational Databases: How They Work
Link on Common Fields
Inner, Left, Right joins
http://www.tableau.com/learn/tutorials/on-demand/join-types-union
https://help.tableau.com/current/pro/desktop/en-us/joining_tables.htm
Exercise on Inner Joins.
Spreadsheet at this link
1) Do the four corners test. How Many in Violent 2013? In Violent 2017?
2) Load data into Tableau
3) Inner join Violent 2013 and Violent 2017 on zip code
4) Build bar graph that visualizes 2017 Violent crime by City.
6) From Analytics, drag total and average line to view
7) Display the totals with labels
Question:
Totals and Average for Inner Join?
Totals and Average for Right Join?
Why are we getting different numbers?
Grouping:
--Group on sheet for specific view
--Grouping in dimensions pane is problematic
Exercise: Show the average graduate debt for HBCUs vs Public, Private Non-Profit, Private For-Profit
1) Build basic schools and Grad Debt Mdn graphic. Set to Average. Add Control to Filter
2) Group HBCUs
3) Filter Public schools, group them
4) Unfilter
5) Filter Private non profits, group them
6) Unfilter
7) Filter Private for profits, group them
8) Unfilter
Solution to Exporting Low Quality Images from Tableau
1) Worksheet | Copy | Image
2) Select Title & View
3) Open Preview
4) File | New From Clipboard
--Your Tableau graphic has been pasted into Preview.
5) Export. Change Format to JPEG. Increase Quality to Best. Option: Boost Resolution to 1000 pixels/inch
Bins and Groups
Bins and Groups
Create Bins: https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_bins.htm
Other options besides bins:
Use parameters to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/parameters_create.htm
Use sets to organize data: https://onlinehelp.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#Use
Assignment
Create graphics. Propose a story idea. Each student interviews one person for the assignment.
Refresher on how to create calculated fields:
https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_formulas.htm
Each person produces one graphic. The graphics should be different. Tip: Use the filtering to create different versions of the topic, i.e. top 10 or bottom 10.
Jour 5283: Each student produces three separate graphics.
Each team submits a 400-word draft story. Spell out the individual team members' contributions at the bottom of the story, ie, Wells interviewed Trump for the story, Jordan interviewed Bush.
Memo and graphics due 11:59 p.m. Friday, Sept. 27
Another Assignment
Create the following charts in Tableau:
2. Top 10 schools with the most low income debtors and high income debtors. Create jpg, upload to Blackboard
3. Top 10 schools with highest median debt for females, highest for males. Create jpg, upload to Blackboard
4. Write 250 words with your analysis of this data and propose one story idea.
Answers due 11:59 pm Sept. 21 on Blackboard
Another Assignment on Race
Create Tableau chart, top 10 schools by student race/ethnicity:
Chart 1: White schools
Chart 2: African-American schools
Chart 3: Hispanic schools
Chart 4: White, black, hispanic, asian
--Columns SUM(Ugds White) SUM(Ugds Black) etc
--Sort by individual columns
Issues:
Format Labels from "Sum of Ugds White" to White
Ditto for Black, Hispanic, Asian
Wrap the labels: Mouse between rows, expand the spacing - make the rows fatter
Format | Wrap On
Create Tableau chart, create ratio Grad_Debt_Mdn/Enrollment per school
Another Assignment with Graphics
Create the following charts in Tableau:
Top 10 schools with the most low income debtors and high income debtors. Create jpg, upload to Blackboard
Top 10 schools with highest median debt for females, highest for males. Create jpg, upload to Blackboard
Write 250 words with your analysis of this data and propose one story idea.
Answers due 11:59 pm Sept. 21 on Blackboard
Another Assignment with Calculations
Calculations in Tableau
See Video, Getting Started With Tableau Calculations
Follow the exercises in the sample workbook
See the transcript for guidance.
Homework Question #1:
Create a calculated field that divides the profits into sales, which we will call profit-sales ratio.
Build a chart that displays the profits-sales ratio by Sub-Category, or tables, machines, fasteners etc.
Sort that chart with the highest profit items on top. Format the axis in dollars, label the bars in dollars.
Write a headline.
Upload a .jpeg into Blackboard.
Homework Question #2:
Use ARDebt9-17 data
Create a histogram plotting the median debt for Arkansas colleges
Label the colleges (don't worry, only a portion of the colleges will display. That's ok)
Write a headline.
Upload a .jpeg into Blackboard.
Introduction to Dashboards
Telling a Story With Data: Interactive Dashboards.
Search for Dashboard:
https://wordpressua.uark.edu/datareporting/tableau-license/
Wells dashboard
https://public.tableau.com/profile/rob.wells#!/vizhome/IRE19_GDPVisualizingtheStateLocalEconomy/ArkansasEconomyinDetail
Class Exercise:
--Build A Dashboard Using Existing Maps and Graphics
Pane #1: Map
Pane #2: Graphic
Pane #3: Graphic with a slider to let readers interact with data (sort by most low-income student debt. sort by highest enrollment by race. sort by highest median debt. etc)
Dashboards
Create a Dashboard With The Following Elements
Pane #1: Map of Default rates for colleges with more than statewide average of White enrollment.
Pane #2: Map of Default rates for colleges with more than statewide average of Black enrollment.
Pane #3: Map of Default rates for colleges with more than statewide average of Hispanic enrollment.
--
Megan Putney, Mike's Hard Lemonade
Solution to Exporting Low Quality Images from Tableau
1) Worksheet | Copy | Image
2) Select Title & View
3) Open Preview
4) File | New From Clipboard
--Your Tableau graphic has been pasted into Preview.
5) Export. Change Format to JPEG. Increase Quality to Best. Option: Boost Resolution to 1000 pixels/inch
Bins and Groups
Bins and Groups
Create Bins: 
