{"id":1864,"date":"2022-08-29T08:05:29","date_gmt":"2022-08-29T15:05:29","guid":{"rendered":"https:\/\/murrowcourses.com\/graphics\/?p=1864"},"modified":"2022-08-28T21:13:07","modified_gmt":"2022-08-29T04:13:07","slug":"working-with-pivot-tables","status":"publish","type":"post","link":"https:\/\/murrowcourses.com\/graphics\/working-with-pivot-tables\/","title":{"rendered":"Working with Pivot Tables"},"content":{"rendered":"<p>In this assignment, you&#8217;ll use <strong>pivot tables<\/strong> to find trends and summarize information from large spreadsheets of raw data.<\/p>\n<p><span class=\"subhead\">Part 1: Survey Data Pivot Table<\/span><\/p>\n<p>For this and most assignments, you can use either Excel or Google Sheets to create spreadsheets. If you want to feel more comfortable with the basics of either program, it&#8217;s recommended that you go through <a href=\"http:\/\/drkblake.com\/spreadsheetbasics\/\" target=\"_blank\" rel=\"noopener noreferrer\">Part 1 of the &#8220;Spreadsheet Basics for Journalists&#8221; series<\/a>. (The whole three-part series is a great exercise if you want to get an advantage for upcoming assignments.)<\/p>\n<p>1. In previous semesters, students have used a survey to collect student demographic data. The survey asked these questions:<\/p>\n<ul>\n<li>In what month is your birthday? (Choose from list)<\/li>\n<li>Are you male or female? (Male, Female, Other\/Prefer Not to Say)<\/li>\n<li>What year are you in school? (Multiple choice or type-in other)<\/li>\n<li>What is your major at WSU? (Type any answer)<\/li>\n<li>Are you from Washington state? (Yes, No)<\/li>\n<li>How much do you like Washington State University? (Scale from 1-5)<\/li>\n<li>How much do you like the University of Washington? (Scale from 1-5)<\/li>\n<\/ul>\n<p>2. Open the <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1xdF_g1rK8EG72ri-bDqVxz-pXakOklyiHsg4Lzo8smA\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>survey results spreadsheet<\/strong><\/a>, and copy-paste all the data into an Excel file or a new Google Sheets file to create a copy of the data for yourself. Look through the data and assess whether it seems useful or not. Where are there inconsistencies? What categories might we be able to visualize?<\/p>\n<p>3. Most of the data collected is <strong>nominal<\/strong> or <strong>ordinal<\/strong> data, meaning we need to tally up the categories before we can draw conclusions or make any charts. To do this, we can use <strong>pivot tables<\/strong> in Excel or in Google Spreadsheets. To create a pivot table, it\u2019s important that each column of your spreadsheet has a descriptive header in the first row.<\/p>\n<p>Instructions for Excel: <a href=\"https:\/\/support.office.com\/en-IE\/article\/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/support.office.com\/en-IE\/article\/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576<\/a><\/p>\n<p>Instructions for Google Spreadsheets: <a href=\"https:\/\/support.google.com\/docs\/answer\/1272898\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/support.google.com\/docs\/answer\/1272898<\/a><\/p>\n<p><em>Most spreadsheet instructions in this course will show the steps using Google Sheets, because this is a free software program available to anyone with a Google account. It also has the benefit of saving your work to Google Drive, so you don&#8217;t risk losing files. However, using Excel is fine if that&#8217;s your preference, and the steps are usually very similar.<\/em><\/p>\n<p>4. Create a pivot table to tally up answers to the question \u201cAre you male or female?\u201d If done successfully, it should show you how many survey respondents selected <strong>Male<\/strong>, <strong>Female<\/strong> and <strong>Other\/Prefer Not to Say<\/strong>.<\/p>\n<div id=\"attachment_1869\" style=\"width: 510px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1869\" class=\"wp-image-1869\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/survey-pivot.png\" alt=\"\" width=\"500\" height=\"386\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/survey-pivot.png 686w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/survey-pivot-300x231.png 300w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><p id=\"caption-attachment-1869\" class=\"wp-caption-text\">The same column is put into &#8220;Rows&#8221; and &#8220;Values&#8221; to get a count of the number of responses in each category.<\/p><\/div>\n<p>This video shows all the steps:<\/p>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/EshRkPgy0Nk\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<p>5. Save this pivot table to submit later. If you are working in Google Sheets, save an exported file to submit by going to <strong>File &gt; Download As &gt; Microsoft Excel<\/strong>.<\/p>\n<p><span class=\"subhead\">Part 2: Major League Baseball Salaries<\/span><\/p>\n<p>1. Lahman\u2019s Baseball Database is a fantastic resource for Major League Baseball data. This is a project by reporter Sean Lahman, who started his website way back in 1995 and is known for bringing data into sports reporting. He has all his data formatted in clean, helpful ways, which makes it much easier to work with. Download this one salaries file from the 2018 baseball database: <a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/Salaries.csv\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>Salaries.csv<\/strong><\/a><\/p>\n<p>CSV stands for &#8220;comma-separated values&#8221; and is a popular data format since it can be imported into a lot of different software programs.<\/p>\n<p>2. Create a new file in Google Sheets or Excel and import the CSV file so we can create pivot tables. You&#8217;ll see there are a lot of rows \u2014 more than 25,000! For each record we have the year, the team, the league, the specific player and the salary amount.<\/p>\n<p>3. Highlight the entire sheet, then create a new pivot table that shows the <strong>average salary each year<\/strong> for the American League and National League. The pivot table options should look like this, except that you want to discover the <strong>average<\/strong> rather than the sum:<\/p>\n<div id=\"attachment_2967\" style=\"width: 2330px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2967\" class=\"wp-image-2967 size-full\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries.png\" alt=\"\" width=\"2320\" height=\"1242\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries.png 2320w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries-300x161.png 300w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries-1024x548.png 1024w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries-768x411.png 768w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries-1536x822.png 1536w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2021\/08\/pivot-table-salaries-2048x1096.png 2048w\" sizes=\"auto, (max-width: 2320px) 100vw, 2320px\" \/><\/a><p id=\"caption-attachment-2967\" class=\"wp-caption-text\">Your pivot table options should look very similar to this, except that this shows the sum of all salaries (to avoid giving away the answers) and you should display the average salary instead for each league and year.<\/p><\/div>\n<p>And this video shows the whole process:<\/p>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/FhSE9IgJR9I\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<p>That&#8217;s all you need to do, but there&#8217;s a lot more you can explore in this dataset! For instance, here&#8217;s a chart showing the total salaries paid by individual teams. (The Yankees are the green line way above everyone else, and the Dodgers are the orange.)<\/p>\n<div id=\"attachment_1871\" style=\"width: 610px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1871\" class=\"wp-image-1871 size-full\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/chart.png\" alt=\"\" width=\"600\" height=\"371\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/chart.png 600w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/chart-300x186.png 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><p id=\"caption-attachment-1871\" class=\"wp-caption-text\">This chart is not understandable as-is. It would need a title and other labels to make sense to anyone who wasn&#8217;t already looking at this data. But this can be a great way to quickly look for trends or outliers.<\/p><\/div>\n<p><span class=\"subhead\">Part 3: Starbucks customer survey in Malaysia<\/span><\/p>\n<p>1. This <a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/Starbucks-Malaysia-survey.csv\" target=\"_blank\" rel=\"noopener noreferrer\">dataset<\/a> is from a small 2019 market research survey of Starbucks customers in Malaysia. (This data is from <a href=\"https:\/\/www.kaggle.com\/mahirahmzh\/starbucks-customer-retention-malaysia-survey\/\" target=\"_blank\" rel=\"noopener noreferrer\">Kaggle<\/a>, a Google hub for sharing datasets.) It includes a variety of standard customer satisfaction questions. Download the file here: <strong><a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/Starbucks-Malaysia-survey.csv\" target=\"_blank\" rel=\"noopener noreferrer\">Starbucks-Malaysia-survey.csv<\/a><\/strong><\/p>\n<p>2. Like before, create a new file in Google Sheets or Excel and import the CSV file so we can create pivot tables. With 122 responses, it&#8217;s not a huge survey, and you <em>could<\/em> look through the raw data for insights \u2014 but we&#8217;ll be able to do it more quickly with pivot tables. Look at the header row to see what questions were included on the survey.<\/p>\n<p>3. Suppose you&#8217;re doing a quick analysis about what customers think of Starbucks amenities as part of a consulting project about store renovations. Highlight the entire sheet, then create a new pivot table that shows the <strong>average<\/strong> rating for Question 15 (&#8220;How would you rate the ambience?&#8221;) and Question 16 (&#8220;How would you rate the WiFi quality?&#8221;) broken out by gender. Here&#8217;s what it should look like:<\/p>\n<div id=\"attachment_2384\" style=\"width: 1210px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2384\" class=\"wp-image-2384 size-full\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1.png\" alt=\"\" width=\"1200\" height=\"534\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1.png 1200w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1-300x134.png 300w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1-1024x456.png 1024w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable1-768x342.png 768w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/a><p id=\"caption-attachment-2384\" class=\"wp-caption-text\">This is in Excel, just to show how the interface is slightly different.<\/p><\/div>\n<p>4. Within the pivot table, we can filter to only see responses from customers who frequently go to Starbucks. Question 5 asks about how often survey respondents go to Starbucks. Add Question 5 as a filter, then use the filter dropdown at the top of the spreadsheet to <em>only<\/em> include people who said they go to Starbucks daily, weekly or monthly (uncheck rarely and never).<\/p>\n<div id=\"attachment_2385\" style=\"width: 1210px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2385\" class=\"wp-image-2385 size-full\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2.png\" alt=\"\" width=\"1200\" height=\"500\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2.png 1200w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2-300x125.png 300w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2-1024x427.png 1024w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-pivottable2-768x320.png 768w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/a><p id=\"caption-attachment-2385\" class=\"wp-caption-text\">Here&#8217;s where to add the filter options in Excel.<\/p><\/div>\n<div id=\"attachment_2393\" style=\"width: 954px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-sheets-filter.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-2393\" class=\"wp-image-2393 size-full\" src=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-sheets-filter.png\" alt=\"\" width=\"944\" height=\"444\" srcset=\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-sheets-filter.png 944w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-sheets-filter-300x141.png 300w, https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2020\/05\/starbucks-sheets-filter-768x361.png 768w\" sizes=\"auto, (max-width: 944px) 100vw, 944px\" \/><\/a><p id=\"caption-attachment-2393\" class=\"wp-caption-text\">Here is the same thing in Google Sheets \u2014 note that the filter option is part of the editor sidebar instead of the top.<\/p><\/div>\n<p>Like before, save the file. Surveys don&#8217;t always give clear answers, and a pivot table like this might be used to write follow-up questions for a focus group.<\/p>\n<p><span class=\"subhead\">Submitting your assignment<\/span><\/p>\n<p>Include the following as attachments with your submission:<\/p>\n<ul>\n<li>Spreadsheet from Part 1 (survey data) showing pivot table<\/li>\n<li>Spreadsheet from Part 2 (baseball salaries) showing pivot table<\/li>\n<li>Spreadsheet from Part 3 (Starbucks survey) showing filtered pivot table<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In this assignment, you&#8217;ll use pivot tables to find trends and summarize information from large spreadsheets of raw data.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1864","post","type-post","status-publish","format-standard","hentry","category-assignments"],"_links":{"self":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/1864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/comments?post=1864"}],"version-history":[{"count":5,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/1864\/revisions"}],"predecessor-version":[{"id":2968,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/1864\/revisions\/2968"}],"wp:attachment":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/media?parent=1864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/categories?post=1864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/tags?post=1864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}