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

CS 105 Lecture 10: Excel + Dictionaries Craig Zilles (Computer Science) https://go.illinois.edu/cs105fa19 November 1, 2019

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 of 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 of each string 12

An Annou ounce 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 ogeth 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