Agenda for Monday, Oct. 8
–Context!
–Common Errors
Context #1
Add the Quick Facts for city population, demographics.
Little Rock: African American comprise 42 percent of Little Rock’s population. https://www.census.gov/quickfacts/fact/table/littlerockcityarkansas,US/PST045217
Add typical salary from Occupational Employment Statistics database for Arkansas
https://www.bls.gov/oes/current/oes_ar.htm
Common Errors – Math
Percent vs Percentage Point
At Lyon College, 67 percent of non-first-generation students paid back their loans within five years, while only 53 percent of first-generation students did the same, which results in a 14 percent POINT difference. The median debt for both types of students was the same though, at $12,000.
You mean “percentage point.” 14 percent of 67 is 9.4.
Steve Doig – MathCrib-Doig
Common Errors – AP Style on Numbers
AP Style on Numerals:
Numerals – AP Stylebook-2avrxtn
Common Error – Divi Library
Divi Builder. Do Not Save to Library.
Context #2: Build Charts for Context
First row: The overall median debt for Arkansas students; for men, for women.
Second row: The overall median debt for first generation students. And non-first generation
Third row: The overall statewide repayment rate, and the rate for men, for women
Fourth row: The overall median debt for white, black, asian, hispanic
Post on WordPress with the category Context
Research – Data Question
The Financial Aid department does not report loan repayment info to the Department of Education. “Once the students leave us we don’t track their information anymore,” he said.
Question: Look at data dictionary for source of this information. All 1,826 columns explained here.
https://collegescorecard.ed.gov/assets/FullDataDocumentation.pdf
https://collegescorecard.ed.gov/assets/CollegeScorecardDataDictionary.xlsx
Homework
#1: Read this report and compare to your work on context. Prepare to discuss it Wednesday
https://ticas.org/sites/default/files/pub_files/classof2016.pdf
#2: By 11:59 p.m. Tuesday, fix the issues with your charts and stories from Assignment #2. Post on WordPress, use the Context category for a tag
Sept. 10 Day #6
Introduction to Tableau
Read Ch. 6, Chapter6TheTruthfulArt
–Video
http://www.tableau.com/learn/tutorials/on-demand/getting-started
Tutorial
Dataset for the Tutorial:
global-superstore-orders-2016
getting_started_transcript
Key Points
Tableau: Arkansas 2014 Crime Rates
Build a crime chart.
ARK 2010-2014 Crime Data-Tableau-Feb22
Format chart, Export PDF, Post to WordPress
Super Ninjas:
Examine Student Loan Data: Tuition costs at Arkansas colleges and universities. Chart Debt by Graduates and Dropouts
————————————————————————————————
Homework
Read Ch. 6, Chapter6TheTruthfulArt
Post two discussion questions on Blackboard in a Word file.
Construct chart from student loan data: Median Debt by Graduated Students. Post a .jpeg file and two sentences with key findings or questions for further reporting. Due 11:59 pm Tuesday Sept 11 on Blackboard
————————————————————————————————
Tableau Desktop Software
Thank you for your interest in the Tableau for Teaching program. Below is a website (landing page link) for your upcoming class. Each student should go to the landing page to download Tableau Desktop and enter the product key noted below. This key will allow your entire class to activate Tableau Desktop for the duration of the course.
Please forward these instructions to your students:
- Download the latest version of Tableau Desktop here
- Click on the link above and select Get Started. On the form, enter your school email address for Business E-mail and enter the name of your school for Organization.
- Activate with your product key: TCN8-F401-A980-11F5-79E7
- Already have a copy of Tableau Desktop installed? Update your license in the application: Help menu -> Manage Product Keys
Are your students new to Tableau? Share our free Data Analytics for University Students guide to help them get started.
Students can continue using Tableau after the class is over by individually requesting their own one-year license through the Tableau for Students program here
Need help? Find answers to frequently asked questions here.
Best,
Academic Programs Team
Tableau Software
tft@tableau.com
Sept. 5 Day #5
Agenda for Wednesday Sept. 5
Review Readings
Review Homework
Pivot Tables
Pivot Table Class Exercise
Univ of Arkansas, Fayetteville financials
1) Watch Video On Gathering Financials, Pivot Table and Making A Chart
2. Open Adobe Acrobat Pro.
3. Download this document and open in Adobe Acrobat Pro. University of Arkansas financial report June 2016
(Data Note: Data in this exercise represents revenues pledged for repayment of various bond issues. They do not represent the full budget. If you are curious about that, see: http://financial-affairs.uark.edu/PDFs/1415annualrept.pdf)
4. Find “UAF Various Facility Pledged Revenues” from Pg. 3.
Highlight the table.
Cntl + click to bring up Menu. Select “Export Selection As…”
Export it as an Excel table.
You can only do this in Adobe Acrobat Pro
5. Check and verify your data was imported correctly. Check the totals against the original file.
Copy your data into a new tab.
Create a tab with a data biography, write down the source of your information.
6. Format into dollars, no decimals. Delete Totals Row.
7. Insert New Column A to Categorize Revenues
Title Column “Category”
Code the “Tuition and Fees” and “Bookstore” as “School” in the new Category Column
Everything else is categorized as “Other”
8. Create Pivot table.
Select just the data you downloaded, not the whole sheet
From the Main Menu: Data | Summarize with Pivot Table
A box appears with the select Table and Range and New Worksheet filled in. Select OK
9. Design Pivot Table
Drag Category field into rows
Drag 2012 and 2016 into Values
You have created a Table with two Columns and Two Rows Summarizing the Revenues from 2012 to 2016
10. In Column D, Create a Percentage Change Calculation, 2012-2016
Cell D4 Type formula =(C4-B4)/B4 for Other
Cell D5 Type formula =(C5-B5)/B5 for School
Cell D6 Type formula =(C6-B6)/B6 for School
Format as Percentage
11. Write a short news lead describing your main conclusions from this data
Part 2: Chart
5. Copy Data and “Paste as Values” into a new Tab
6. Delete “Sum 2012” and “Sum 2016” columns. That leaves two columns, Row Labels and Pct Change
Highlight Source and % change,
On the ribbon bar in Excel, Click Insert and Select Clustered Chart.
Homework: Analyze Student Loan Data
Due Saturday, Sept. 8, 11:59 pm. POST on Blackboard
Examine The Data Dictionary and the Data
Add a new column and call it Beauty. Use this categorize Beauty / Cosemotology / Barber / Salon schools versus others.
Create a Pivot Table.
- Answer the following questions with a short answer and by posting a table in a tab on the Excel workbook.
–Number of Schools by City? Write a sentence identifying the top five cities and the number of colleges. Post a table with the total number of cities, ranked in descending order.
–Use your Beauty column as a filter in the pivot table. Write a sentence identifying the top five cities with beauty schools and the number of beauty schools. Post a table with the total number of cities, ranked in descending order.
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.
Caitlin Lane – World Bank Pivot Table 9/1
Caitlin Lane
9/1/18
The most common violation was the Procurement Guidelines 1.15 which occurred 391times.
Pivot Table Assignment-Samantha Van Dyke
The most common violation is the Procurement Guideline 1.5 which occurred 133 times.
Haley Ruiz – World Bank Pivot Table 9/1
Haley Ruiz
9/1/18
WorldBank Pivot Table
WorldBank Pivot Table HW -2l93479
3. Most common violation: Consult Guidelines 1.22 (a)(i)-(ii); Proc. Guidelines 1.15 (a)(i)-(ii) ; Occurred: 200 times
Halie Brown – WordBlank
Halie Brown
Data Journalism
9/1/18
WorldBlank – Halie Brown-18s2uk2
3. The most common violation was of Procurement Guidelines 1.15(a)(ii), with 331 violations.
Megan Wilson World Bank 9/1
The most common violation is a violation of the Procurement Guidelines 1.15(a)(ii). It occurs 418 times.
Kris Smith- Pivot Table 9.1.18
The most common violation was the Procurement Guidelines 1.15. This violation was made either by itself or in conjunction with another violation 391 times.
Elizabeth Green Pivot Table HW 9/1
The most common violation fell under Procurement Guidelines 1.15 and occurred 384 times.
Aug. 29, Day #4
Agenda for Wednesday, August 29
- Excel Quiz in Blackboard. 45 minutes max.
- Pivot Table Exercises. Work on Your Own Once You Finish the Quiz.
- Class discussion Pivot Tables
Calculations and Pivot Tables
Introduction to Pivot Tables
Follow this NICAR tutorial: PivotTables-rjty0y
Use the data: MLB2015-1-1w6gc5m
World Bank listing: worldbank
Homework
Due 11:59 pm Saturday, Sept. 1
*Updated* Post on Blackboard with your spreadsheet to this link.
Using the WorldBank data in the previous example, build a Pivot Table.
- 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?