<jupyter_start><jupyter_text>Iteration with Pandas [Dr. <NAME>](https://www.linkedin.com/in/tirthajyoti-sarkar-2127aa7/), Fremont, CAAs data scientists, all of us have been there.We are given a large Pandas DataFrame and asked to check some relationships between various fields in the columns - in a row-by-row fashion. It could be some logical operation or some sophisticated mathematical transformation on the raw data.Essentially, it is a **simple case of iterating over the rows of the DataFrame** and doing some processing at each iteration.However, it **may not be that simple in terms of choosing the most efficient method** of executing this type of task. You might already have a hunch that a simple for-loop is going to be quite inefficient for this iteration task. And, you will be absolutely right. So, what can we do to avoid such speed pitfall?This type of knowledge is critical to practice what we call ["Productive Data Science"](https://medium.com/productive-data-science/why-and-how-should-you-learn-productive-data-science-53377b473f37).<jupyter_code>import numpy as np, pandas as pd, matplotlib.pyplot as plt
from tqdm import tqdm
from time import time
np.random.seed(101)
df = pd.DataFrame(np.random.randint(0,100,size=(100000, 4)), 
                  columns=list('ABCD'),dtype=np.int16)
df.head()
df.info(memory_usage="deep")
df.describe()<jupyter_output><empty_output><jupyter_text>Most inefficient _for_ loop<jupyter_code>count=0
t1=time()
for i in range(len(df)):
    if df.iloc[i]['A']+df.iloc[i]['B'] > df.iloc[i]['C']+df.iloc[i]['D']:
        count+=1
t2=time()
delt1 = round((t2-t1),2)
print(f"Time:{delt1} seconds")
print("Count:",count)<jupyter_output>Time:32.22 seconds
Count: 49769<jupyter_text>Comparing `iterrows()` and `df.values` - 1<jupyter_code>count=0
t1=time()
for idx, row in df.iterrows():
    if row['A']+row['B'] > (row['C']+row['D']):
        count+=1
t2=time()
delt1 = round((t2-t1),2)
print(f"Time:{delt1} seconds")
print("Count:",count)
count=0
t1=time()
for row in df.values:
    if row[0]+row[1] > (row[2]+row[3]):
        count+=1
t2=time()
delt2 = round((t2-t1),3)
print(f"Time:{delt2} seconds")
print("Count:",count)
print(f"df.values is {round(delt1/delt2,2)} times faster")<jupyter_output>df.values is 61.7 times faster<jupyter_text>Comparing `iterrows()` and `df.values` - 2<jupyter_code>count=0
t1=time()
for idx, row in df.iterrows():
    if row['A']+row['B'] > 1.25*(row['C']+row['D']):
        count+=1
t2=time()
delt1 = round((t2-t1),2)
print(f"Time:{delt1} seconds")
print("Count:",count)
count=0
t1=time()
for row in df.values:
    if row[0]+row[1] > 1.25*(row[2]+row[3]):
        count+=1
t2=time()
delt2 = round((t2-t1),3)
print(f"Time:{delt2} seconds")
print("Count:",count)
print(f"df.values is {round(delt1/delt2,2)} times faster")<jupyter_output>df.values is 14.74 times faster<jupyter_text>Comparing `iterrows()` and `df.values` - 3<jupyter_code>count=0
t1=time()
for idx, row in df.iterrows():
    if np.log(1+row['A']+row['B']) > np.sqrt(0.5*(row['C']+row['D'])):
        count+=1
t2=time()
delt1 = round((t2-t1),2)
print(f"Time:{delt1} seconds")
print("Count:",count)
count=0
t1=time()
for row in df.values:
    if np.log(1+row[0]+row[1]) > np.sqrt(0.5*(row[2]+row[3])):
        count+=1
t2=time()
delt2 = round((t2-t1),3)
print(f"Time:{delt2} seconds")
print("Count:",count)
print(f"df.values is {round(delt1/delt2,2)} times faster")<jupyter_output>df.values is 9.11 times faster<jupyter_text>Simple vectorized operation is fastest in this counting example<jupyter_code>t1 = time()
df['result'] = np.log(1+df['A']+df['B']) > np.sqrt(0.5*(df['C']+df['D']))
t2=time()
delt3 = round((t2-t1),3)
print(f"Time:{delt3} seconds")
print("Count:",df['result'].sum())<jupyter_output>Time:0.01 seconds
Count: 9202<jupyter_text>String identifier<jupyter_code>def identifier():
    """
    Generates random identifier string of 5 characters
    """
    letters = list('CFJQZ')
    numbers = list('123456789')
    
    random_id = ''
    random_id+=np.random.choice(letters)
    random_id+=np.random.choice(letters)
    random_id+=np.random.choice(numbers)
    random_id+=np.random.choice(numbers)
    #random_id+=np.random.choice(numbers)
    #random_id+=np.random.choice(numbers)
    #random_id+=np.random.choice(letters)
    random_id+=np.random.choice(letters)
    
    return random_id
for i in range(10):
    print(identifier())
df.head()
id_lst=[]
for i in range(100000):
    id_lst.append(identifier())
id_lst=np.array(id_lst)

df.insert(0,'ID',id_lst)
df.sample(5)
df['ID'].nunique()
ratio_dict={'ID':[],'Ratio':[]}
t1=time()
for _,row in df.iterrows():
    if row['ID'][0:2] == 'ZZ' and row['ID'][-1]=='F':
        ratio = (row['A']+row['B'])/(0.01+row['C']+row['D'])
        ratio_dict['ID'].append(row[0])
        ratio_dict['Ratio'].append(ratio)
t2=time()
delt4 = round((t2-t1),3)
print(f"Time:{delt4} seconds")
ratio_dict={'ID':[],'Ratio':[]}
t1=time()
for row in df.values:
    if row[0][0:2] == 'ZZ' and row[0][-1]=='F':
        ratio = (row[1]+row[2])/(0.01+row[3]+row[4])
        ratio_dict['ID'].append(row[0])
        ratio_dict['Ratio'].append(ratio)
t2=time()
delt4 = round((t2-t1),3)
print(f"Time:{delt4} seconds")
pd.DataFrame(ratio_dict)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1897601827179292, 'fraction_numerical': 0.041872858774267226, 'mean_word_length': 5.316105769230769, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 33, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '563474', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 2114, 'n_tokens_neox': 2034, 'n_words': 424, 'path': 'Pandas and Numpy/Pandas_iteration.ipynb', 'repo_name': 'nedhub/Machine-Learning-with-Python', 'size': '5266'}