Data 3-4 Pandas Iterations and lambdas

Data 3-4 Pandas Iterations and lambdas#

Apply, lambdas, iterrows, itertuples

In this lesson we will start learning how to clean a dataframe data and loop over it

import pandas as pd

checks = pd.read_csv('https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/dining/check-data.csv')
checks.sample(10)

	check	date	party size	total items on check	total amount of check	gratuity
33	3842	2024-03-31	6	6	$147.12	$5.88
44	2053	2024-12-14	7	23	$588.11	$164.67
9	2968	2024-12-28	1	3	$122.97	$23.36
10	2809	2024-12-30	6	6	$104.46	$1.04
37	4829	2024-12-30	9	11	$816.20	$16.32
40	2512	2024-03-30	3	12	$181.56	$39.94
31	1945	2024-02-05	3	7	$132.86	$21.26
16	3694	2024-11-03	5	17	$1,574.37	$173.18
17	3795	2024-02-21	3	7	$212.38	$46.72
15	2386	2024-03-31	5	12	$1,147.80	$137.74

# Note: numbers are not numbers!!!
checks.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 50 entries, 0 to 49
Data columns (total 6 columns):
 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   check                  50 non-null     int64 
 1   date                   50 non-null     object
 2   party size             50 non-null     int64 
 3   total items on check   50 non-null     int64 
 4   total amount of check  50 non-null     object
 5   gratuity               50 non-null     object
dtypes: int64(3), object(3)
memory usage: 2.5+ KB

Apply#

Apply allows us to execute a function over a Series or the entire DataFrame.

series.Apply(func) <== call function func for every item in the Series

dataframe.Apply(lambda row: func, axis=1) <== call function func for every row in the DataFrame axis=1 == row

dataframe.Apply(lambda col: func, axis=0) <== call function func for every row in the DataFrame axis=0 == col

Why Apply ?#

Apply helps us clean up our data because we can execute non-trivial transformations over our dataframes.

For example, we want to enhance this data by calculating the price per item this is defined as:

total amount of check / total items on check

The problem is total amount of check is an object, not a float. This means we cannot do math on it.

# Type error because of the dollar sign and commas!!!
checks['price_per_item'] = checks['total amount of check'] / checks['total items on check']

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File c:\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:218, in _na_arithmetic_op(left, right, op, is_cmp)
    217 try:
--> 218     result = func(left, right)
    219 except TypeError:

File c:\Python312\Lib\site-packages\pandas\core\computation\expressions.py:242, in evaluate(op, a, b, use_numexpr)
    240     if use_numexpr:
    241         # error: "None" not callable
--> 242         return _evaluate(op, op_str, a, b)  # type: ignore[misc]
    243 return _evaluate_standard(op, op_str, a, b)

File c:\Python312\Lib\site-packages\pandas\core\computation\expressions.py:73, in _evaluate_standard(op, op_str, a, b)
     72     _store_test_result(False)
---> 73 return op(a, b)

TypeError: unsupported operand type(s) for /: 'str' and 'int'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
Cell In[10], line 1
----> 1 checks['price per item'] = checks['total amount of check'] / checks['total items on check']

File c:\Python312\Lib\site-packages\pandas\core\ops\common.py:76, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     72             return NotImplemented
     74 other = item_from_zerodim(other)
---> 76 return method(self, other)

File c:\Python312\Lib\site-packages\pandas\core\arraylike.py:210, in OpsMixin.__truediv__(self, other)
    208 @unpack_zerodim_and_defer("__truediv__")
    209 def __truediv__(self, other):
--> 210     return self._arith_method(other, operator.truediv)

File c:\Python312\Lib\site-packages\pandas\core\series.py:6135, in Series._arith_method(self, other, op)
   6133 def _arith_method(self, other, op):
   6134     self, other = self._align_for_op(other)
-> 6135     return base.IndexOpsMixin._arith_method(self, other, op)

File c:\Python312\Lib\site-packages\pandas\core\base.py:1382, in IndexOpsMixin._arith_method(self, other, op)
   1379     rvalues = np.arange(rvalues.start, rvalues.stop, rvalues.step)
   1381 with np.errstate(all="ignore"):
-> 1382     result = ops.arithmetic_op(lvalues, rvalues, op)
   1384 return self._construct_result(result, name=res_name)

File c:\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:283, in arithmetic_op(left, right, op)
    279     _bool_arith_check(op, left, right)  # type: ignore[arg-type]
    281     # error: Argument 1 to "_na_arithmetic_op" has incompatible type
    282     # "Union[ExtensionArray, ndarray[Any, Any]]"; expected "ndarray[Any, Any]"
--> 283     res_values = _na_arithmetic_op(left, right, op)  # type: ignore[arg-type]
    285 return res_values

File c:\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:227, in _na_arithmetic_op(left, right, op, is_cmp)
    219 except TypeError:
    220     if not is_cmp and (
    221         left.dtype == object or getattr(right, "dtype", None) == object
    222     ):
   (...)
    225         # Don't do this for comparisons, as that will handle complex numbers
    226         #  incorrectly, see GH#32047
--> 227         result = _masked_arith_op(left, right, op)
    228     else:
    229         raise

File c:\Python312\Lib\site-packages\pandas\core\ops\array_ops.py:163, in _masked_arith_op(x, y, op)
    161     # See GH#5284, GH#5035, GH#19448 for historical reference
    162     if mask.any():
--> 163         result[mask] = op(xrav[mask], yrav[mask])
    165 else:
    166     if not is_scalar(y):

TypeError: unsupported operand type(s) for /: 'str' and 'int'

How do we fix this? we write a user-defined function to convert string values like this: $4,590.45 into floats like this: 4590.45

def clean_currency(value:str) -> float:
    '''
    This function will take a string value and remove the dollar sign and commas
    and return a float value.
    '''
    return float(value.replace(',', '').replace('$', ''))


# tests
assert clean_currency('$1,000.00') == 1000.00
assert clean_currency('$1,000') == 1000.00
assert clean_currency('1,000') == 1000.00
assert clean_currency('$1000') == 1000.00

With our function written we can use apply() to transform the series.

Remember its a really good idea to track lineage when you are building a data pipeline.

NEVER replace columns, always create new ones.

checks['total_amount_of_check_cleaned'] = checks['total amount of check'].apply(clean_currency)
checks['price_per_item'] = checks['total_amount_of_check_cleaned'] / checks['total items on check']
checks.sample(10)

	check	date	party size	total items on check	total amount of check	gratuity	total_amount_of_check_cleaned	price_per_item
1	2443	2024-06-09	3	10	$286.40	$31.50	286.40	28.64
28	2446	2024-12-15	4	12	$575.64	$28.78	575.64	47.97
31	1945	2024-02-05	3	7	$132.86	$21.26	132.86	18.98
43	1186	2024-09-21	5	16	$298.72	$74.68	298.72	18.67
37	4829	2024-12-30	9	11	$816.20	$16.32	816.20	74.20
34	1368	2024-12-21	10	25	$2,193.00	$372.81	2193.00	87.72
33	3842	2024-03-31	6	6	$147.12	$5.88	147.12	24.52
9	2968	2024-12-28	1	3	$122.97	$23.36	122.97	40.99
48	4161	2024-06-22	9	28	$1,385.16	$235.48	1385.16	49.47
7	1564	2024-09-23	8	11	$928.40	$204.25	928.40	84.40

Challenge 3-4-1#

Modularize our work!#

Let’s take what we did so far, and create a dataset that would be better prepared for analysis / machine learning.

create a module check_functions.py
- add the clean_currency() function definition to it.
- under if __name__=='__main__': add the tests
- run the code to make sure it works.
create your challenge file 3-4-1.py
- import streamlit, pandas and your clean_currency function
- load the checks dataset into a dataframe:
- clean the total amount of check and gratuity columns
- calculate the price_per_item as total amount of check / total items on check
- calcualte the price_per_person as total amont of check / party size
- calcualte the items_per_person as total items on check / party size
- calcualte the tip_percentage as the total amount of check / gratuity
- display dataframe
- describe dataframe

checks dataset https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/dining/check-data.csv

Using Row Apply to setup some KPI’s#

KPI is a key performance indicator. It summarizes larger points of data, so they can be measured over time. For example a letter grade such as an A- is a KPI summary of all your graded efforts to date.

Let’s build some simple KPIs from this data.
While KPI’s are determined and decided upon from the business decision makers,
Actionable KPI’s are always based on data evidence.

KPI 1: Whales

With your help, marketing has decided that whale customers have checks that are: - whale whales are in the top 75% percentile for both items per person and price per person - big eaters whales are in the top 75% for items per person - big spenders are in the top 75% for price per person

KPI 2: Tippers

With your help, marketing has decided that light tippers are in the botton 25% of tip percentage and heavy tippers are in the top 75%

Before we can apply our KPI’s we must write the functions!#

checks['gratuity_cleaned'] = checks['gratuity'].apply(clean_currency)
checks['price_per_item'] = checks['total_amount_of_check_cleaned'] / checks['total items on check']
checks['price_per_person'] = checks['total_amount_of_check_cleaned'] / checks['party size']
checks['items_per_person'] = checks['total items on check'] / checks['party size']
checks['tip_percentage'] = checks['gratuity_cleaned'] / checks['total_amount_of_check_cleaned']

def detect_whale(
        items_per_person:float, 
        price_per_person:float, 
        items_per_person_75th_pctile:float, 
        price_per_person_75_pctile:float) -> str:
    if items_per_person > items_per_person_75th_pctile and price_per_person > price_per_person_75_pctile:
        return 'whale'
    if items_per_person > items_per_person_75th_pctile:
        return 'big eater'
    if price_per_person > price_per_person_75_pctile:
        return 'big spender'
    
    return ''

# tests
ppp_75 = checks['price_per_person'].quantile(0.75)
ipp_75 = checks['items_per_person'].quantile(0.75)
print(ppp_75, ipp_75)
assert detect_whale(5, 250, 3, 175) == 'whale'
assert detect_whale(5, 100, 3, 175) == 'big eater'
assert detect_whale(1, 250, 3, 175) == 'big spender'
assert detect_whale(1, 100, 3, 175) == ''


# Apply the detect_whale function to the checks DataFrame
checks['whale'] = checks.apply(lambda row: detect_whale(row['items_per_person'], row['price_per_person'], ipp_75, ppp_75), axis=1)
checks.sample(25)

158.35666666666668 3.0

	check	date	party size	total items on check	total amount of check	gratuity	total_amount_of_check_cleaned	price_per_item	gratuity_cleaned	price_per_person	items_per_person	tip_percentage	whale
2	3685	2024-12-07	5	5	$252.95	$50.59	252.95	50.59	50.59	50.590000	1.000000	0.200000
27	3653	2024-10-29	1	4	$72.88	$16.76	72.88	18.22	16.76	72.880000	4.000000	0.229967	big eater
30	2705	2024-07-08	10	19	$838.85	$671.08	838.85	44.15	671.08	83.885000	1.900000	0.800000
6	2527	2024-03-27	6	21	$921.48	$55.29	921.48	43.88	55.29	153.580000	3.500000	0.060001	big eater
24	4310	2024-11-30	10	34	$3,262.30	$913.44	3262.30	95.95	913.44	326.230000	3.400000	0.279999	whale
48	4161	2024-06-22	9	28	$1,385.16	$235.48	1385.16	49.47	235.48	153.906667	3.111111	0.170002	big eater
25	4031	2024-08-12	6	14	$655.48	$65.55	655.48	46.82	65.55	109.246667	2.333333	0.100003
10	2809	2024-12-30	6	6	$104.46	$1.04	104.46	17.41	1.04	17.410000	1.000000	0.009956
19	3718	2024-10-30	2	5	$464.70	$120.82	464.70	92.94	120.82	232.350000	2.500000	0.259996	big spender
43	1186	2024-09-21	5	16	$298.72	$74.68	298.72	18.67	74.68	59.744000	3.200000	0.250000	big eater
29	4590	2024-05-08	3	5	$220.40	$22.04	220.40	44.08	22.04	73.466667	1.666667	0.100000
16	3694	2024-11-03	5	17	$1,574.37	$173.18	1574.37	92.61	173.18	314.874000	3.400000	0.110000	whale
7	1564	2024-09-23	8	11	$928.40	$204.25	928.40	84.40	204.25	116.050000	1.375000	0.220002
0	2827	2024-05-06	8	12	$415.08	$107.92	415.08	34.59	107.92	51.885000	1.500000	0.259998
28	2446	2024-12-15	4	12	$575.64	$28.78	575.64	47.97	28.78	143.910000	3.000000	0.049997
14	3676	2024-02-25	1	1	$19.89	$1.99	19.89	19.89	1.99	19.890000	1.000000	0.100050
26	4257	2024-01-22	6	8	$593.60	$47.49	593.60	74.20	47.49	98.933333	1.333333	0.080003
37	4829	2024-12-30	9	11	$816.20	$16.32	816.20	74.20	16.32	90.688889	1.222222	0.019995
32	1440	2024-11-30	3	8	$589.04	$141.37	589.04	73.63	141.37	196.346667	2.666667	0.240001	big spender
46	3621	2024-06-23	1	2	$138.76	$19.43	138.76	69.38	19.43	138.760000	2.000000	0.140026
11	3693	2024-01-18	10	20	$1,820.00	$309.40	1820.00	91.00	309.40	182.000000	2.000000	0.170000	big spender
1	2443	2024-06-09	3	10	$286.40	$31.50	286.40	28.64	31.50	95.466667	3.333333	0.109986	big eater
9	2968	2024-12-28	1	3	$122.97	$23.36	122.97	40.99	23.36	122.970000	3.000000	0.189965
49	3404	2024-07-19	9	26	$2,382.90	$71.49	2382.90	91.65	71.49	264.766667	2.888889	0.030001	big spender
20	3393	2024-08-26	5	6	$302.64	$24.21	302.64	50.44	24.21	60.528000	1.200000	0.079996

Challenge 3-4-2#

Write and test your KPI’s!!!#

In module check_functions.py

copy over the detect_whale() function and tests
write function detect_tipper(tip_pct, tip_pcy_75th_pctile, tip_pct_25_pctile)
- should return either “light”, “heavy” or “”
write tests for detect_tipper()

in 3-4-2.py

copy the code from 3-4-1.py
Calculate the ntiles using .quantile()
call the apply() function on the row to make new colums whale and tipper

Looping over Dataframes#

If you must for loop over your DataFrames, there are two choices:

df.iterrows() dict-like iteration
df.itertuples() named-tuple like iteration (faster)

Let’s do an example whewre we display the check number, whale and tipper for “heavy tipper” checks.

## Using the iterrows() method
print("Total Amount of Whale Checks")
for i,row in checks.iterrows():
    if row['whale'] == 'whale':
        print(i, row['check'], row['total_amount_of_check_cleaned'])

Total Amount of Whale Checks
16 3694 1574.37
24 4310 3262.3

# Same example with the itertuples() method
print("Total Amount of Whale Checks")
for row in checks.itertuples():
    if row.whale == 'whale':
        print(row.check, row.total_amount_of_check_cleaned)

Total Amount of Whale Checks
3694 1574.37
4310 3262.3

# Of course you don't need a loop to do this:
checks[checks['whale'] == 'whale'][['check', 'total_amount_of_check_cleaned']]

	check	total_amount_of_check_cleaned
16	3694	1574.37
24	4310	3262.30