Exercise for Assignment 2: Sorting and Filtering in Excel

Next, we will be doing an exercise around sorting and filtering through data, then interviewing data to find stories. We will start the exercise in class. By Friday you will need to answer some questions in a quiz that will be posted in Blackboard after class ends.

Data Viz – Sorting and Filtering Exercise
Using New York State Cardiac Arrests

New York State has an open-data policy. You can get a lot of data from New York State-funded programs at https://data.ny.gov/.

In this exercise, we will be using data on cardiac surgeries and “percutaneous coronary interventions” (PCI) by hospitals across the state of New York. You can get the data set here: https://health.data.ny.gov/Health/Cardiac-Surgery-and-Percutaneous-Coronary-Interven/jtip-2ccj

Click the Export button and choose Excel, then save to your desktop.

Let’s find the hospitals with the highest observed mortality rate.

  • Go to the Data menu and choose Sort.
  • Make sure the box labeled “My list has headers” is checked!
  • Click in “Sort By” and choose “Observed Mortality Rate.” Under “Order” choose “Largest to Smallest,” then click OK.

Now, let’s use filters to create lists of hospitals by regions.

  • Go to the Data menu and choose Filter. You should see downward-facing arrows above each column header. If you only see an arrow above one column but not all of them, select all of the rows and columns by clicking the icon at the very upper left of the data set, and then choose filter again.
  • Click the arrow in the Region column.
  • Uncheck the box next to “(Select All).”
  • Check “Central NY,” “Western NY-Buffalo” and “Western NY-Rochester.”
  • Close the filter box to see the filtered list. There should be 134 rows.

Now clear the filter by region and selecting the filter arrow in the Region column and click “(Select All.”) Replace the filter with a filtered list of every region except for Central and Western New York.

Finally, clear the filter and select only New York State to get aggregates figures for each region of the state.

Now you should be familiar with sorting and filtering. Let’s summarize some figures from these lists. With the New York State filter applied, get the sum total of total deaths, and total cases across all types of procedures.

  • Click your mouse under Number of Deaths, then click the SUM button in the toolbar.
  • Make sure the selected area goes across all five rows. A number will appear. Write that down.
  • Do the same thing under the Number of Cases column, and write that number down.
  • Finally, to get an aggregate observe mortality rate, divide the total you got in column H by the total in column G. To do this, click in any empty cell, then type the = sign. Without hitting any other key, click once in the total under column H, then type / (for divided by), then once under the total in column G, then Enter.
  • The number you get will be a very long decimal — not exactly easy to remember. Turn it into a percentage by clicking it once, then clicking the % sign in the toolbar.
  • The percentage that appears will be rounded up quite a bit, and we don’t want that. You need to tell Excel to reveal more decimals. Select the percentage you got once, then click the button in the toolbar that adds decimals. Click this button twice to get a percentage that has two numbers after the decimals.
  • Find some trends in the data and create another spreadsheet that has just those numbers: for example, cardiac deaths in Rochester in 2008, 2009, 2010 and 2011.

When you’re ready to complete the assignment, go to Infogr.am and create  a chart with your findings. Be sure to cite the data source in the chart. Paste the URL to that chart into Assignment 2 in Blackboard.

 

 

Leave a Reply