Activity 1-3

Importing CSV Files

In this lab, you will be learning how to import CSV files from online sources and performing computations on the data. A CSV (comma-separated values) file stores numbers and text, where each line of the file is a data record. Each field is separated with a comma.

Task 1

Click here to download the CSV file we will be using in this lab.

For each Pokemon, we will first find its highest numerical stat. Make a spreadsheet and go to File > Import > Upload. Select your data file from where you saved it. You will be prompted to choose how the file will be imported. Select Insert new sheet(s) as your Import action, comma as the Separator character, and click Import. A new sheet should open up containing data from your CSV file.

The file contains data about different species of Pokemon. Each Pokemon has a type under the heading Type 1. Some Pokemon also have a secondary type under the heading Type 2, but if they do not, it is represented with a blank cell. Each Pokemon also has a HP, Attack, Defense, Sp. Atk (Special Attack), Sp. Def (Special Defense), and Speed stat, with higher numbers indicating strength in that stat.

Create a new column and name it "Highest_Stat". In the following row, enter the equation =MAX(F2:K2), which will find the highest stat for the first Pokemon, a "Bulbasaur". Fill down this equation for the rest of the rows.

Note: there are several different ways you can fill down. One is by dragging the bottom-right corner of a cell, but this could take a while for large data sets. Another way you can do it is to copy the cell in question and paste its formula into the last cell you want filled, such that the first and last cell now contain the formula and the cells in between are empty. On Windows, command refers to Ctrl. You go back to the first cell and press command + shift + arrow key on your keyboard, where arrow key indicates the direction you want to fill. This should highlight all the cells from the first to the last. Then you press command + D on Mac to fill down or command + R to fill to the right, which should then fill the formula into all of the cells.

Task 2

Select the entire table and select Data > Pivot Table Report from the toolbar. This will open up a new sheet for a pivot table based on the information you selected.

Navigate to the pivot table sheet. There should be a Report Editor on the right-hand side. Click Add field in Rows and select Type 1. Do the same for Columns, selecting Type 2. This will create a table where each cell represents the aggregate of all Pokemon of that type. For example, the first cell in B3 represents the pure Bug type, while a cell in E4 represents the Dark/Dragon type.

We will now populate the table with values. Add a field to the Values section and select Highest_Stat. Change the value in the cells to be the maximum rather than the sum of the highest stats by selecting MAX from the Summarize by: menu. Now each cell represents the highest stat of all Pokemon of that type.

Now change the value in Summarize by: to AVERAGE. Now each cell represents the average highest stat of all Pokemon of that type.

Task 3

Answer the following questions in a separate sheet.

  1. What type of Pokemon includes a member with the highest stat of all Pokemon? As a hint, you can look at the Grand Total row to see the highest stat for each column (secondary type).
  2. What is the average highest stat of all Pokemon, regardless of type?
  3. What is the highest stat among legendary pokemon?
  4. What's your favorite pokemon? For those unfamiliar with the franchise, Pikachu is an acceptable answer.

Once you're done, please check off your lab with a TA or share your file with by midnight, 2/2.