Send Email with Pandas Table - Printable Version

Send Email with Pandas Table - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Send Email with Pandas Table (/thread-20374.html)

Send Email with Pandas Table - yoitspython - Aug-07-2019

Hi all,

I have successfully web scraped data from the URL (in the code) and could print the table via output and email (without the .to_string(index=False)). My end goal is to print the table without its index in the body of an email. This is the Python code:

import pandas as pd
import datetime as dt
import smtplib
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from smtplib import SMTP


recipients = ['[email protected]'] 
emaillist = [elem.strip().split(',') for elem in recipients]
msg = MIMEMultipart()
msg['Subject'] = "Subject"
msg['From'] = '[email protected]'

#Change the end date to the current date
month = dt.datetime.now().strftime("%m")
day   = dt.datetime.now().strftime("%d")
year  = dt.datetime.now().strftime("%Y")
url = 'https://www8.miamidade.gov/Apps/COB/LobbyistOnline/Views/Queries/Registration_ByPeriod_List.aspx?startdate=08%2f01%2f2019&enddate={}%2f{}%2f{}'.format(month,day,year)

df = pd.read_html(url)
df = df[3]

#clean the types, see defaults df.dtypes
df['Employed On'].astype('datetime64')
df.drop(df.tail(1).index,inplace=True)
#df.style.hide_index()

#Formats the table on the IDLE -> Python Shell display
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)

message = df.sort_values(by='Employed On',ascending=False).to_string(index=False)

#print(message)

html = """\
<html>
  <head></head>
  <body>
    {0}
  </body>
</html>
""".format(message.to_html())

part1 = MIMEText(html, 'html')
msg.attach(part1)

server = smtplib.SMTP("**credentials like IP or Server Name or Port**")
server.sendmail(msg['From'], emaillist , msg.as_string())

I know the code works without the .to_string(index=False) but I want to email the table without the index on the side. I get this error:

Error:Traceback (most recent call last):
  File "D:\PythonScripts\CountyLobbyistRegistration\CountyLobbyistRegistration.py", line 58, in <module>
    """.format(message.to_html())
AttributeError: 'str' object has no attribute 'to_html'

In other words, I have my table printing out like this: see 80, 106, 115 as the index

Output:                          Lobbyist Name                                          Principal Employed On                                  Issue Description Issue Status
80                     MCCARTHY, ERIC                                     PROTERRA, INC.  08/06/2019                                                RFP       Active
106                     RUANO, ROBERT                                   REGIS HOUSE, INC  08/06/2019                                               NONE       Active
115                      SHUBIN, JOHN K                             SUSTAINABILITY PARTERS  08/06/2019  INTRODUCTION TO SUSTAINABILITY PARTNERS AND IT...       Active

But I want to print the table like this:

Output:                      Lobbyist Name                                          Principal Employed On                                  Issue Description Issue Status
                     MCCARTHY, ERIC                                     PROTERRA, INC.  08/06/2019                                                RFP       Active
                      RUANO, ROBERT                                   REGIS HOUSE, INC  08/06/2019                                               NONE       Active
                     SHUBIN, JOHN K                             SUSTAINABILITY PARTERS  08/06/2019  INTRODUCTION TO SUSTAINABILITY PARTNERS AND IT...       Active

Would someone be able to help me out with this? Thx so much !

RE: Send Email with Pandas Table - ThomasL - Aug-07-2019

message = df.sort_values(by='Employed On',ascending=False).to_string(index=False)

I assume it has something to do with .to_string()

RE: Send Email with Pandas Table - scidam - Aug-07-2019

Just remove .to_string(...) in line #35. to_html() not a string method, it is for dataframes.

RE: Send Email with Pandas Table - yoitspython - Aug-08-2019

(Aug-07-2019, 10:53 PM)scidam Wrote: Just remove .to_string(...) in line #35. to_html() not a string method, it is for dataframes.

But I don't want to show the index value of each entry/row. I want the columns to start with the Names column. I know that removing the .to_string() will make the code work (I actually stated that in my post).

RE: Send Email with Pandas Table - scidam - Aug-08-2019

(Aug-08-2019, 05:48 PM)yoitspython Wrote: But I don't want to show the index value of each entry/row

to_html method uses index as a keyword (default is True). Try to pass index=False to to_html() method.