Pandas iterrows not working on a data frame as expected

Question

I have two dataframes df1 and df2. I am trying create a table from dataframe df2 and insert it in the body of email content . My current code is only taking records matching to Number=123 and creating tables in the body.While Subject is iterated correctly and the email is created correctly.

What is that I making wrong in the iterations of the rows . I am attaching code below

df1

Subject                    Number
Hello David Bill is due      123
Hello Adam  Bill is due      456
Hello James  Bill is due     789

df2

Number   Month  Amount
123       Jan    1oo
123       March  220
123       June   212
456       Jan    1oo
456       Feb    230
789       June   400
789       July   650

My code

import os
import boto3
from os.path import basename
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.utils import formatdate, COMMASPACE
from tabulate import tabulate


def create_message(send_from, send_to, cc_to, subject, plain_text_body):
    
    message = MIMEMultipart("alternative", None, [MIMEText(html,'html')])
    message['From'] = send_from
    
    if str(send_to).__contains__(","):
        message['To'] = COMMASPACE.join(send_to)
    else:
        message['To'] =send_to
        
    message['Cc'] = cc_to
    message['Date'] = formatdate(localtime=True)
    message['Subject'] = subject
    message.attach(MIMEText(plain_text_body, 'plain'))
    return message

def send_message(message):
    #print(message)
    client = boto3.client("ses",region_name='eu-west-1')
    response = client.send_raw_email(RawMessage = {"Data": message.as_string()})

html ='''
<p>Dear receiver,</p>

<p>Please find below the details</p>
{table}
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>Thanks and best regards</p>
<p>Rahul.</p>'''

for i, row in df1.iterrows():
    subject  = row["Subject"]
    to_address ="[email protected]"
    cc_list = "[email protected]"
    send_from="[email protected]"
    df3=df2[df2['Number']==row['Number']]
    headers= ['Number','Month','Amount']
    html = html.format(table=tabulate(df3, headers, tablefmt="html"))
    message = create_message(send_from,to_address, cc_list, subject,html)
    send_message(message)

Expected output

Email1

Subject:  Hello David Bill is due

Body of email

Please find below the details

Number   Month  Amount
123       Jan    1oo
123       March  220




Email2
    
    Subject:  Hello Adam Bill is due
    
    Body of email
    
    Please find below the details
    
    Number   Month  Amount
   456       Jan    1oo
   456       Feb    230

Any help Appreciated

Answer 1

In your code you have html variable - a multi-line string. But in your loop you assign to html another content, created by its formatting, thus overwriting the original content.

This way, in the next turn of your loop, it will not have e.g. {table} - the placeholder for the table.

Use another variable name for the reformatted html content.

Answer 2

I tried to copy your situation using two separate CSV files, but it seems to work witout any issue:

df1 = pd.read_csv('test1_iter.csv', delimiter=";")
print(df1)
df2 = pd.read_csv('test2_iter.csv', delimiter=";")
print(df2)

for i, row in df1.iterrows():
    df_aux = df2[df2['Number']==row['Number']]
    print(df_aux)

And this is the result:

               Subject  Number
0  HelloDavidBillisdue     123
1   HelloAdamBillisdue     456
2  HelloJamesBillisdue     789

   Number  Month  Amount
0     123    Jan     100
1     123  March     220
2     123   June     212
3     456    Jan     100
4     456    Feb     230
5     789   June     400
6     789   July     650


   Number  Month  Amount
0     123    Jan     100
1     123  March     220
2     123   June     212
   Number Month  Amount
3     456   Jan     100
4     456   Feb     230
   Number Month  Amount
5     789  June     400
6     789  July     650

How are you loading your data? The problem might be there.