KeyError : 0 - When looping through a dataframe

Thanks in advance for your help with this. I'm still so new to this that I don't really know what I'm doing. I've tried a number of ways to do this, but I keep getting errors. I've tried using iterrows, iloc, loc and so forth with no luck. I'm not understanding how to get each row of data and use the values in that row to send an email.

Code:

email_list 
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+
|   | Client Name | Staff Name      | Role | Due Date   | Submission ID             | Staff Email   | Generated Due Docs ID            |
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+
| 1 | H.Pot       | JohannaNameLast | IP   | 2020-04-01 | H.POT-Johanna-IP-4/1/2020 | xyz@gmail.com | h.potjohannanamelastip2020-04-01 |
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+
| 2 | S.Man       | DaveSmith       | TS   | 2020-04-01 | S.MAN-David-TS-4/1/2020   | abc@gmail.com | s.mandabc2020-04-01              |
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+
| 3 | S.Man       | LouisLastName   | IP   | 2020-04-01 | S.MAN-Louis-IP-4/1/2020   | def@gmail.com | s.manlouislastnameip2020-04-01   |
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+
| 5 | T.Hul       | KellyDLastName  | IP   | 2020-04-01 | T.HUL-Kelly-IP-4/1/2020   | ghi@gmail.com | t.hulkelleydlastnameip2020-04-01 |
+---+-------------+-----------------+------+------------+---------------------------+---------------+----------------------------------+

# Get all the Names, Email Addresses, roles and due dates.
all_clients = email_list['Client Name']
all_staff = email_list['Staff Name']
all_roles = email_list['Role']
#all_types = email_list['Form Type']
all_due_dates = email_list['Due Date']
all_emails = email_list['Staff Email']

for idx in range(len(email_list)):
    # Get each records name, email, subject and message
    client = all_clients[idx]
    staff = all_staff[idx]
    role = all_roles[idx]
    #form_type = all_types[idx]
    due_date = all_due_dates[idx]
    email_address = all_emails[idx]

    # Get all the Names, Email Addresses, roles and due dates.
    subject = f"Your monthly summary was Due on {due_date} Days For {client.upper()}"
    message = f"Hi {staff.title()}, \n\nThe {form_type} is due in {due_date} days for {client.upper()}.  Please turn it in before the due date. \n\nThanks, \n\nJudy"


full_email = ("From: {0} <{1}>\n"
                  "To: {2} <{3}>\n"
                  "Subject: {4}\n\n"
                  "{5}"
                  .format(your_name, your_email, staff, email_address, subject, message))
    # In the email field, you can add multiple other emails if you want
    # all of them to receive the same text
try:
    server.sendmail(your_email, [email_address], full_email)
    print('Email to {} successfully sent!\n\n'.format(email_address))
except Exception as e:
    print('Email to {} could not be sent :( because {}\n\n'.format(email_address, str(e)))

# Close the smtp server
server.close()

Solution

It is a bad pattern to extract all columns and then get respective element from each such variable (holding a single column).

Use rather the following pattern:

for idx, row in email_list.iterrows():
    row.Role
    row['Staff Name']

If you don't use idx, put rather _ instead.

This variant is much quicker than yours. The above code actually performs here a single iteration (over rows), whereas your code performs:

also a single iteration over row numbers,
but then your code performs n lookups, for an individual element, with particular index in each column.

Let's get back to my code sample. There are 2 variants to access an element of the current row:

row.Role - if column name contains no "special" chars (e.g. spaces).
row['Staff Name'] - in other (more complex) cases.

And the reason why you get KeyError: 0.

Note that:

your rows have indices starting with 1 (the leftmost column, with no title),
but in your loop idx starts from 0,
access to each "column variable" is actually just by the index value, not by integer position of the "wanted" element.

So the error occurs at the very first turn of your loop, when you:

have idx == 0,
no column variable (actually a Series) contains an element with index == 0.

Actually Pandas uses here 2 different names (Key and index value) for the same thing, so it is open to discussion to which extent this message is readable. You can do nothing about it. You just have to know it.

Or if you for some reason want stay by the curent version of your code, change only the for instruction to:

for idx in range(1, len(email_list) + 1):
    ...

Then this loop will start from idx == 1 and no error should occur, as long as you have the index as consecutive numbers, starting from 1.

But as I noticed, your index:

starts with 1, 2 and 3 (so far so good),
but then there is a "gap", you have no row with index of 4.