💻 Activity 3: Determining the Superbowl Proposition Winner
Contents
💻 Activity 3: Determining the Superbowl Proposition Winner#
We made a Superbowl proposition game for ENGR131 now it is time to determine the winner. The submission are in an excel file your tasks is to calculate the winner. We have provided you a list with the correct outcomes from the superbowl.
Step 1: Load the File#
The file is named “Super Bowl Propositions.xls” load the file into a Pandas DataFrame.
Save it as a variable df
!pip install openpyxl
Requirement already satisfied: openpyxl in /Users/sc3623/opt/miniconda3/envs/engr131/lib/python3.10/site-packages (3.1.1)
Requirement already satisfied: et-xmlfile in /Users/sc3623/opt/miniconda3/envs/engr131/lib/python3.10/site-packages (from openpyxl) (1.1.0)
# import pandas
import pandas as pd
# use the pd.read_excel to load the excel file
...
Ellipsis
# import pandas
import pandas as pd
# use the pd.read_excel to load the excel file
df = pd.read_excel('Super Bowl Propositions.xlsx')
Use df.head() to look at the first few lines of the DataFrame
...
Ellipsis
df.head()
| ID | Start time | Completion time | Name | How long will the National Anthem be? | What will the result of the coin toss be? | Will the team that calls the coin toss be correct? | Will the team that wins the coin toss win the game? | What will the first offensive play of the game be? | ... | Which team will kick the longest field goal? | How many total field goals will there be? | Will the Eagles win and cover the opening spread (i.e. win by 1.5 points or more)? | Will the game go into overtime? | What will the Jersey Number be from the player to score the last touchdown? | What color will the liquid be poured on the winning coach? | Will the total points scored be odd or even? | What will be the final combined score total? | Who will be the Super Bowl MVP? | Tiebreaker Question:\nHow many minutes will the game run? (from kickoff to final whistle, including half time). Numbers only. For example: 209. | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 2023-02-05 18:04:32 | 2023-02-05 18:07:49 | jca92@drexel.edu | Joshua Agar | Over 2 minutes 2 seconds (worth 1 point) | Heads (worth 1 point) | No (worth 1 point) | Yes (worth 1 point) | Pass (worth 2 points) | ... | Eagles (worth 2 points) | 3 (worth 2 points) | Yes (worth 3 points) | No (worth 1 point) | Odd (worth 1 point) | Clear/Water (worth 4 points) | Even (worth 2 points) | Over 50.5 (worth 3 points) | Jalen Hurts (worth 1 point) | 205 |
| 1 | 2 | 2023-02-05 18:10:53 | 2023-02-05 18:13:27 | sg3779@drexel.edu | Stojan Gradistanac | Under 2 minutes 2 seconds (worth 1 point) | Heads (worth 1 point) | Yes (worth 1 point) | Yes (worth 1 point) | Run (worth 2 points) | ... | Chiefs (worth 2 points) | 3 (worth 2 points) | Yes (worth 3 points) | No (worth 1 point) | Even (worth 1 point) | Clear/Water (worth 4 points) | Even (worth 2 points) | Under 50.5 (worth 3 points) | Jalen Hurts (worth 1 point) | 130 |
| 2 | 3 | 2023-02-05 18:11:15 | 2023-02-05 18:14:40 | jar632@drexel.edu | John Riley | Under 2 minutes 2 seconds (worth 1 point) | Heads (worth 1 point) | Yes (worth 1 point) | Yes (worth 1 point) | Pass (worth 2 points) | ... | Eagles (worth 2 points) | 2 (worth 2 points) | Yes (worth 3 points) | No (worth 1 point) | Odd (worth 1 point) | Blue (worth 4 points) | Even (worth 2 points) | Over 50.5 (worth 3 points) | Jalen Hurts (worth 1 point) | 186 |
| 3 | 4 | 2023-02-05 18:13:38 | 2023-02-05 18:16:51 | so545@drexel.edu | Samuel Orcutt | Over 2 minutes 2 seconds (worth 1 point) | Tails (worth 1 point) | No (worth 1 point) | Yes (worth 1 point) | Pass (worth 2 points) | ... | Eagles (worth 2 points) | 4 (worth 2 points) | No (worth 3 points) | No (worth 1 point) | Odd (worth 1 point) | Red/Pink (worth 6 points) | Even (worth 2 points) | Over 50.5 (worth 3 points) | Travis Kelce (worth 3 points) | 307 |
| 4 | 5 | 2023-02-05 18:16:07 | 2023-02-05 18:18:21 | bw666@drexel.edu | Benji Weckstein | Over 2 minutes 2 seconds (worth 1 point) | Heads (worth 1 point) | No (worth 1 point) | Yes (worth 1 point) | Run (worth 2 points) | ... | Eagles (worth 2 points) | 1 (worth 4 points) | Yes (worth 3 points) | Yes (worth 3 points) | Even (worth 1 point) | Lime/Green/Yellow (worth 2 points) | Even (worth 2 points) | Over 50.5 (worth 3 points) | Miles Sanders (worth 5 points) | 243 |
5 rows × 42 columns
Now we want to get the strings for each of the questions
# We want to extract the question names
# To do this we want all the column heading that have a ? in the string
# We can start by creating a empty list called questions
...
# now we want to loop around all of the row heading
# You can build a loop that loops around each column name
# The column name can be obtained from df.columns
# save each heading in a temporary variable column
for ... in ...:
# Now we want to check if the variable column is a questions
# To do this we want to use an if statement that sees if a "?" is in the string
if ... in ...:
# if this is true we want to add the heading to the list
# this can be done using the list method .append(obj)
# the obj is the local variable in the list
...
# if you want to check this you can print the columns
...
Cell In[6], line 11
for ... in ...:
^
SyntaxError: cannot assign to ellipsis
# We want to extract the question names
# To do this we want all the column heading that have a ? in the string
# We can start by creating a empty list called questions
questions = []
# now we want to loop around all of the row heading
# You can build a loop that loops around each column name
# The column name can be obtained from df.columns
# save each heading in a temporary variable column
for column in df.columns:
# Now we want to check if the variable column is a questions
# To do this we want to use an if statement that sees if a "?" is in the string
if "?" in column:
# if this is true we want to add the heading to the list
# this can be done using the list method .append(obj)
# the obj is the local variable in the list
questions.append(column)
# if you want to check this you can print the columns
print(questions)
Now we want to calculate the score. We have provided comments to help you complete this task.
Correct_Answers = ['Under 2 minutes 2 seconds (worth 1 point)', #1
'Tails (worth 1 point)', #2
'Yes (worth 1 point)', #3
'Yes (worth 1 point)', #4
'Run (worth 2 points)', #5
'Yes (worth 2 points)', #6
'Eagles (worth 2 points)', #7
'Chiefs (worth 2 points)', #8
'Jalen Hurts (worth 2 points)', #9
'Chiefs (worth 2 points)', #10
'Touchdown (worth 1 point)', #11
'Over 24.5 (worth 2 points)', #12
'Eagles (worth 2 points)', #13
'No (worth 2 points)', #14
'No (worth 2 points)', #15
'Eagles (worth 2 points)', #16
'Over 9.5 (worth 2 points)', #17
'Other (worth 2 points)', #18
'Under 2.5 (worth 1 point)', #19
'Under 281.5 (worth 2 points)', #20
'Over 241.5 (worth 2 points)', #21
'Over 78.5 (worth 2 points', #22
'Over 72.5 (worth 2 points)', #23
'Over 6.5 (worth 2 points)', #24
'Fourth Quarter (worth 2 points)', #25
'Third Quarter (worth 2 points)', #26
'Yes (worth 2 points)', #27
'Eagles (worth 2 points)', #28
'3 (worth 2 points)', #29
'No (worth 3 points)', #30
'No (worth 1 point)', #31
'Odd (worth 1 point)', #32
'Purple (worth 6 points)', #33
'Odd (worth 2 points)', #34
'Over 50.5 (worth 3 points)', #35
'Patrick Mahomes (worth 1 point)', #36
'xxx'] #37
points = [1, #1 'Under 2 minutes 2 seconds (worth 1 point)'
1, #2 'Tails (worth 1 point)'
1, #3 Yes (worth 1 point)
1, #4 Yes (worth 1 point)
2, #5 Run (worth 2 points)
2, #6 Yes (worth 2 points)
2, #7 Eagles (worth 2 points)
2, #8 Chiefs (worth 2 points)
2, #9 Jalen Hurts (worth 2 points)
2, # 10 Chiefs (worth 2 points)
1, # 11 Touchdown (worth 1 point)
2, #12 Over 24.5 (worth 2 points)
2, #13 Eagles (worth 2 points)
2, #14 No (worth 2 points)
2, #15 No (worth 2 points)
2, #16 Eagles (worth 2 points)
2, #17 Over 9.5 (worth 2 points)
2, #18 Other (worth 2 points)
1, #19 Under 2.5 (worth 1 point)
2, #20 Under 281.5 (worth 2 points)
2, #21 Over 241.5 (worth 2 points)
2, #22 Over 78.5 (worth 2 points
2, #23 Over 72.5 (worth 2 points)
2, #24 Over 6.5 (worth 2 points)
2, #25 Fourth Quarter (worth 2 points)
2, #26 Third Quarter (worth 2 points)
2, #27 Yes (worth 2 points)
2, #28 Eagles (worth 2 points)
2, #29 3 (worth 2 points)
3, #30 No (worth 3 points)
1, #31 No (worth 1 point)
1, #32 Odd (worth 1 point)
6, #33 Purple (worth 6 points)
2, #34 Odd (worth 2 points)
3, #35 Over 50.5 (worth 3 points)
1, # Patrick Mahomes (worth 1 point)
250]
# make a list to keep the scores
...
# create a loop that goes through each row
# to turn the df into a row iterator use the .iterrows() method
# You can select just the questions by indexing the dataframe with the list
# example: df[questions].iterrows()
# note iterrows returns a tuple with the first value being the index and the second being the row
for ..., ... in ...:
# initiate a score for the person
...
# Since we have the heading for all of the questions we can loop around the list
# The correct answers are order
# we also have an ordered list that shows how much each question is worth
# make a loop around the questions that uses enumerate
# enumerate will let you get the index number and the contents
for ..., ... in ...:
# check if the the person got the correct answer
if ... == ...:
# add the number of points they earned for the correct response
...
# append the score to the list of scores
...
# add the score to the original dataframe using df['scores'] = scores
...
# make a list to keep the scores
scores = []
# create a loop that goes through each row
# to turn the df into a row iterator use the .iterrows() method
# You can select just the questions by indexing the dataframe with the list
# example: df[questions].iterrows()
# note iterrows returns a tuple with the first value being the index and the second being the row
for ind, row in df[questions].iterrows():
# initiate a score for the person
score = 0
# Since we have the heading for all of the questions we can loop around the list
# The correct answers are order
# we also have an ordered list that shows how much each question is worth
# make a loop around the questions that uses enumerate
# enumerate will let you get the index number and the contents
for i, question in enumerate(row):
# check if the the person got the correct answer
if question == Correct_Answers[i]:
# add the number of points they earned for the correct response
score += points[i]
# append the score to the list of scores
scores.append(score)
# add the score to the original dataframe using df['scores'] = scores
df['scores'] = scores
Now we can find the winner.
# use df['scores'] and convert to numpy using .to_numpy() method
# use the .argsort() method to find the sorted index
# then flip the array using [::-1]
...
# Use df.iloc[ind[0]] to get the row of winner.
# Then index the dataframe to get the winners name
...
'Joshua Agar'
# use df['scores'] and convert to numpy using .to_numpy() method
# use the .argsort() method to find the sorted index
# then flip the array using [::-1]
ind = df['scores'].to_numpy().argsort()[::-1]
# Use df.iloc[ind[0]] to get the row of winner.
# Then index the dataframe to get the winners name
df.iloc[ind[0]]['Name']