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.
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.
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.
Answer the following questions in a separate sheet.
- 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).
- What is the average highest stat of all Pokemon, regardless of type?
- What is the highest stat among legendary pokemon?
- 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
email@example.com by midnight, 2/2.