CS 105 Lecture 7: More on Functions + Excel Craig Zilles (Computer - - PowerPoint PPT Presentation

Lecture 7: More on Functions + Excel

Craig Zilles (Computer Science) March 9, 2020 https://go.illinois.edu/cs105sp20

CS 105

To Today

1. nested loops (indefinite loops) 2. break and continue

• 3. Dynamic Typing
• 4. Scope
• 5. Excel
• Cell ranges
• Functions: SUM, COUNTIF, COUNTIFS
• INDEX/MATCH, *LOOKUP

2

Loop Loop nestin ting

• I think nested loops were the most confusing part of the
• text. I find them incredibly difficult to read and

understand what is happening in them. The challenge problems based around them were pretty hard for me.

• Muddiest point is nesting loops and how to implement

continue and break commands

• I would love to go over more how to follow the code in

nested loops because sometimes I do not understand what gets read in what order.

3

Nes Nested ed Loops

• Print out the intersection of two lists

list1 = [‘lemon’, ‘orange’, ‘lime’] list2 = [‘banana’, ‘lemon’] for thing1 in list1: for thing2 in list2: if thing1 == thing2: print(thing1)

4

Br Break leaves a loop

• op early

How many chars are printed? for c in "sleepy": if c == "e": break print(c) A) 0 B) 1 C) 2 D) 3 E) 6

5

Br Break vs. Retu turn

def func(a_list): for item in a_list: if item == "": break print(item) print("done")

6

def func(a_list): for item in a_list: if item == "": return print(item) print("done")

• How are these different if we pass in: ["a", "b", "", "c"] ?

Con Conti tinue

• Used to skip over iterations (but not leave loop)

mixed_list = [ 'hi', 3, math.pi, 'there', ['CS', 105]] for item in mixed_list: if type(item) != str: continue print(item) How many items are printed? A) 0 B) 1 C) 2 D) 3 E) 5

7

Do Docstrings gs = Do Documen entation Strings gs

• I found docstrings to be confusing because I don't really

understand their function and why we would use them

• Help a user of your function (could be you) understand

how to use your function

• Read using the help() function (q to quit help)

def my_function(): "Docstrings are a string literal that are first thing in the function" return 32

8

Py Python is a dy dynam namical ally ty typed ped la lang.

• The + operator does (at least) three different things:
• Integer addition (when given two integers)
• String concatenation (when given two strings)
• Floating point addition (when given 1 float,1 number)
• How does Python know which to do?
• At run time it looks at the types of its operands
• This means that a single piece of code can do many

different things at different times (polymorphism)

• Means you can write less code
• Also means that it can be hard to find bugs

9 String 6 001000011 001010011

Type Number of characters Characters (Stored using Unicode encoding)

000100000 000110001 000110000 000110101 ‘CS 105’ C S 1 5

A A po polymorphi rphic fu function

for print_all(collection): for item in collection: print(item) print_all([1, 2, 3, 4]) print_all({ 'key': 'val', 'CS': 105' }) print_all(7) print_all('a string') A) Error B) No error

10

Fu Functions

• better understanding formal and actual parameters
• What happens if we do not set any parameters when

defining a function?

• How to return a value from a function to a variable.

11

Sc Scop

• pe
• I am confused about scope of variables and functions
• Is all that you have to do to allow global modification

type "global 'variable name' "? I don't understand global vs local variables and how they are used.

• Scope prevents functions from messing each other up

12

Sc Scop

• pes in acti

ction

• n

my_var = 11 def my_print(my_var): print(my_var) my_print(22) print(my_var)

13

What does it print? A) B) C) D) E) Error

11 11 11 22 22 11 22 22

Sc Scop

• pe, con
• nt.

t.

• 1. Every function is given a clean slate
• Scope: a mapping names to objects
• 2. Any variables written in a function are defined in the

function's scope

• Can be overridden with global statement
• 3. The scope is destroyed when the function returns
• 4. If a name is read that doesn't exist in the function's

scope, it tries the scope the function was defined in.

14

Fu Functions

• better understanding formal and actual parameters
• What happens if we do not set any parameters when

defining a function?

• How to return a value from a function to a variable.

15

An Annou

• unce

cements ts

• Quiz 2 this week (Thursday-Sunday)
• Practice exams up by tomorrow noon-ish
• I will compute mid-term grades tonight
• We'll have Informal Early Feedback in Labs this week
• We've now seen all of the basics of programming:
• Variables, expressions, conditionals, loops, functions
• Reading assignments will be shorter in general
• We'll focus more on solving problems w/code

16

Ex Excel: Cell Ra Ranges

• Colon-separated pair: e.g., 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
• A 3D reference in Excel includes the Sheet name
• <sheetname>!<row><column>
• E.g., statements!B2

17

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

18

Ex Excel: Func unctions ns

• 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)

19

Ex Excel Fn Fns: S : SUM(range)

• Equivalent in Python:

total = 0 for cell in range: total += sum

20

Ex Excel Fn Fns: C : COUNTIF(range, c crit riteria ria)

• Equivalent in Python:

count = 0 for cell in range: if cell == criteria: # sort of count += 1

• Criteria could be:
• A single value: 7 or "Illinois"
• A comparison: ">7" or ">" & B2
• A string with wildcards: "*BADM*" or "CS 10?"
• * matches any # of things, ? is any one character

21

Ex Excel Fn Fns: C : COUNTIFS

• COUNTIFS(range1, criteria1, range2, criteria2, …)
• Ranges 1 and 2 must have equal # of rows and columns
• Counts each time the nth value of each range meets that

range's criteria

• E.g., =COUNTIFS(A0:A6,">10", B0:B6,"empty")

22

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

23

IN INDEX

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

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

24

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

25

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

26

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

27

Ne Next week eek's s rea eading

• More on Strings! Building and manipulating text data:
• More control with formatting
• Replace X with Y
• Splitting strings into pieces
• Files! (How data is stored persistently (e.g., on disks)
• Paths to files (directory hierarchies)
• Opening files
• Reading and writing files

28