CS 105 Lecture 10: Excel + Dictionaries Craig Zilles (Computer - - PowerPoint PPT Presentation

Lecture 10: Excel + Dictionaries

Craig Zilles (Computer Science) November 1, 2019 https://go.illinois.edu/cs105fa19

CS 105

Are you sitti ting next t to someone to talk to for th the clicker questi tions? To Today I'm using: a text editor

and the command line Also, I'll post today's code

2

To Today

• 1. Comma-separated values (CSV) files (cont.)
• Reading, splitting, filter & find best patterns
• csv library
• 2. List Comprehesions
• 3. Excel: (INDEX, MATCH, VLOOKUP)

3

Fi Find da nd date o

• f hi

f highe hest une unempl ploym yment r rate

• Example 3

4

Pa Pattern: Finding best in a collection

current_best = a value you know is worse than best for thing in collection: if thing is better than current_best: current_best = thing return / do something with current_best

5

Fi Finding info asso associated ed with bes est

current_best = a value you know is worse than best best_info = None for thing in collection: if thing is better than current_best: current_best = thing best_info = info about thing return / do something with current_best, best_info

6

Com Command line arguments ts

• Avoid hard coding things like file names

import sys sys.argv is a list of arguments

• First argument is the name of the Python script
• Give 'Usage' message to help user use your program

7

Nes Nested ed Loops

8

Pr Problemat atic CSVs

• Those that contain commas in the data
• 1,2,"3,4",5
• Really complicated to handle yourself; so don't
• Use a library that someone else wrote

import csv csv_data = csv.reader(csv_file)

• Returns a list of lists
• The csv is a list of rows. Each row is a list of values

9

Nes Nested ed Lists

my_list = [ ['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i'] ] x = my_list[1][2] A) 'b' B) 'd' C) 'f' D) 'h' E) Some other value

10

Mo More Lists

mylist = ['a', 'b', 'c'] mylist.append(['d', 'e'])

• What is the value of mylist after the above code

executes A) ['a', 'b', 'c'] B) ['a', 'b', 'c', 'd', 'e'] C) ['a', 'b', 'c', ['d', 'e']] D) ['d', 'e', 'a', 'b', 'c'] E) some other value

11

Lis List t Com Comprehension ions

• Doesn't enable any new functionality
• Syntactic sugar: write things more efficiently
• It is okay not to know this. J

[expression for value in sequence] [expression for value in sequence if condition]

• Example:
• given a list of strings, make list of uppercase versions
• f each string

12

An Annou

• unce

cements ts

• Exam 2 overall pretty good.
• A few questions harder than intended; will refund

some points (when upload scores to Compass)

• Average lower than expected. Might curve at end of

semester, but will wait to see remaining exams.

• Quiz 3 next week:
• Will heavily feature Excel
• As will Lab next week; Today: INDEX, MATCH,

VLOOKUP

• Rest of reading assignments up for rest of semester
• I mistakenly assigned 19 (HTML) instead of 18 (Web)

13

Ex Excel: Cell Rang nges s and nd Func unctions ns

• Colon-separated pair: upper-left:lower-right
• E.g., A6:A12 is part of column, B2:H2 is part of a row
• D4:H12 is a two-dimensional rectangle of cells
• Can use relative or absolute cell references
• E.g., $D$4:$H$12
• Functions generate values for assignment expressions
• =COUNTIF(A1:A30, MAX(A1:A30))
• Excel is case-insensitive:
• =MAX(A1:A30) is the same as =max(a1:a30)

14

Ho How ma many y cells in n regi gion n A1:B10

A) 9 B) 10 C) 18 D) 20 E) Some other value

15

IN INDEX X – ge get value at known position

• 1-dimensional lookup:
• =INDEX(cellrange, index)
• cellrange is 1-dimensional range
• index is 1-based, not 0-based like in Python
• =INDEX(B3:B11, 4) ---> B6
• 2-dimensional lookup:
• =INDEX(cellrange, index1, index2)
• cellrange is 2-dimensional range
• index1 specifies the row, index2 the column
• =INDEX(B3:D11, 4, 2) ---> C6

16

IN INDEX

• Which cell does =INDEX(C10:F20, 3, 2) read from?

A) D12 B) E11 C) E12 D) E13 E) F12

17

MA MATCH H – fi find a a position in a a list

• =MATCH(value, cell-range, match-type)
• value is the value we're looking for
• cell-range is a row or a column (not 2D)
• match-type is:
• 0 = Exact match
• 1 = Approximate (largest less than or equal, values

must be sorted in increasing order)

• -1 = Approximate (smallest value greater than or

equal, values must be sorted in descending order)

• returns 1-based index into the cell range for a cell

containing the value

• Return #N/A if no match

18

Us Using ng INDEX EX with h MA MATCH

• =INDEX(B1:B20, MATCH('Craig', A1:A20, 0))
• Search A1:A20 for a cell holding 'Craig'
• Read the corresponding element of the B column

19

VL VLOOKUP UP – IN INDEX X + MATCH CH tog

• geth

ther

• Handles a common case of INDEX + MATCH
• Less flexible, but only 1 function
• =VLOOKUP (value, table, col_index, [range_lookup])
• value - value to look for in the first column of table.
• table - table from which to retrieve a value.
• col_index - The column in the table from which to

retrieve a value.

• range_lookup - [optional] TRUE = approximate match

(default). FALSE = exact match.

• Value searched for must be to the left of value to return
• HLOOKUP does for rows, what VLOOKUP does for cols

20

Di Dictionaries es

• Why do we care? Tracking relationships between things.
• Canonical example: computing histograms
• Counting up how many times we've seen each thing

for thing in collection: count[thing] += 1 # logically..

21

22

23

Fi Filter ering a a collec ection (p (patter ern)

newlist = [] for thing in collection: if thing meets criteria: newlist.append(thing)

24