Pandas with CSV Files - Exercises

What you'll learn

In this activity, you will:

• Import and read CSV files into Pandas DataFrames
• Access and explore DataFrame information using basic Pandas methods
• Select specific columns to create new DataFrames
• Export DataFrames to CSV files
• Download files from Google Colab to your computer

:computer: Getting Started (5 minutes)

1. Click the link here
2. Make a copy of the Colab Notebook into your Google Drive
3. Import the required packages by running the following code:

import numpy as np
import pandas as pd

:bulb: Make sure to run the import cell first before starting the exercises. You'll see a green checkmark :emoji: when the cell runs successfully.

Question One: Student Scores Analysis (20 minutes)

Setup

1. Download the students_score.csv file here to your computer

Step One: Upload the CSV file

1. Copy and paste the following code into a new cell:

python

from google.colab import files
uploaded = files.upload()

2. Run the cell (press Shift+Enter)
3. Click the "Choose files" button that appears
4. Select the students_score.csv file from your computer tip After uploading, you should see a message showing the file name and size. If you don't see this, try uploading again.

Step 2: Read the CSV file

1. Read the CSV file using the following syntax:

students_score = pd.read_csv('students_score.csv')

Step 3: Explore the DataFrame

1. Display the first 5 rows using students_score.head()
2. Check the column names using students_score.columns
3. Check the index using students_score.index
4. Display DataFrame information using students_score.info()

Expected outputs:

  Students Gender  Mathematics  Science  English
0     Adam      M           87       78       90
1      Bob      M           42       52       66
2  Crystal      F           87       89       83
3    David      M           99       89       83
4   Edmund      M           53       70       91

Index(['Students', 'Gender', 'Mathematics', 'Science', 'English'], dtype='object')

RangeIndex(start=0, stop=7, step=1)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7 entries, 0 to 6
Data columns (total 5 columns):
 #   Column       Non-Null Count  Dtype
---  ------       --------------  -----
 0   Students     7 non-null      object
 1   Gender       7 non-null      object
 2   Mathematics  7 non-null      int64
 3   Science      7 non-null      int64
 4   English      7 non-null      int64
dtypes: int64(3), object(2)
memory usage: 408.0+ bytes

Step 4: Create a new DataFrame without Gender column

1. Select only the columns: Students, Mathematics, Science, and English
2. Store the result in a variable named new_students_score:

new_students_score = students_score[['Students', 'Mathematics', 'Science', 'English']]

3. Display the new DataFrame to verify it matches the expected output

Expected output:

  Students  Mathematics  Science  English
0     Adam           87       78       90
1      Bob           42       52       66
2  Crystal           87       89       83
3    David           99       89       83
4   Edmund           53       70       91
5    Fiona           95       89       90
6    Grace           89       90       90

Step 5: Export to CSV

1. Export the new_students_score DataFrame to a CSV file:

new_students_score.to_csv('new_students_score.csv', index=False)

:bulb: Tip The index=False parameter prevents the row numbers from being saved in the CSV file.

Step 6: Download the file

1. Download the CSV file to your computer:

files.download('new_students_score.csv')

2. Check your Downloads folder for the file

Question 2: Sports Medals Analysis (20 minutes)

Setup

1. Download the sports_medals.csv file here to your computer

Step One: Upload the CSV file

1. Run the upload code again:

from google.colab import files
uploaded = files.upload()

2. Click "Choose files" and select sports_medals.csv

Step 2: Read the CSV file

1. Read the CSV file:

sports_medals = pd.read_csv('sports_medals.csv')

Step 3: Explore the DataFrame

1. Display the entire DataFrame using sports_medals
2. Check the column names using sports_medals.columns
3. Check the index using sports_medals.index
4. Display DataFrame information using sports_medals.info()

Expected outputs:

      Houses  Gold  Silver  Bronze  Total
0  Gryffindor     5       4       5     14
1  Ravenclaw      7       5       3     15
2   Hufflepuff    6       5       4     15
3    Slytherin    2       6       8     16

Index(['Houses', 'Gold', 'Silver', 'Bronze', 'Total'], dtype='object')

RangeIndex(start=0, stop=4, step=1)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 5 columns):
 #   Column   Non-Null Count  Dtype
---  ------   --------------  -----
 0   Houses   4 non-null      object
 1   Gold     4 non-null      int64
 2   Silver   4 non-null      int64
 3   Bronze   4 non-null      int64
 4   Total    4 non-null      int64
dtypes: int64(4), object(1)
memory usage: 288.0+ bytes

Step 4: Create a summary DataFrame

1. Select only the Houses and Total columns
2. Store in a variable named total_sports_medals:

total_sports_medals = sports_medals[['Houses', 'Total']]

3. Display the new DataFrame to verify it matches the expected output

Expected output:

      Houses  Total
0  Gryffindor     14
1  Ravenclaw      15
2   Hufflepuff    15
3    Slytherin    16

Step 5: Export to CSV

1. Export the total_sports_medals DataFrame:

total_sports_medals.to_csv('total_sports_medals.csv', index=False)

Step 6: Download the file

1. Download the CSV file:

files.download('total_sports_medals.csv')

:memo: Submission Instructions

:warning: Warning Before submitting:

• Make sure all code cells have been run successfully
• Verify that both CSV files have been downloaded to your computer
• Check that your outputs match the expected outputs shown in the exercise

Submit your exercise here

:wrench: Troubleshooting

File upload not working?

• Make sure you've downloaded the CSV files from the provided links first
• Try refreshing the page and running the upload code again
• Check that the file names match exactly (including .csv extension)

Getting errors when reading CSV?

• Verify the file was uploaded successfully (you should see a progress bar)
• Make sure you're using the exact file name in pd.read_csv()
• Check that you've imported pandas at the beginning of your notebook

Download not starting?

• Some browsers may block automatic downloads - check for a popup blocker notification
• Try using a different browser if the download doesn't start
• The file will be saved to your default Downloads folder