Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Send Email with Pandas Table
#1
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@email.com'] 
emaillist = [elem.strip().split(',') for elem in recipients]
msg = MIMEMultipart()
msg['Subject'] = "Subject"
msg['From'] = 'email@email.com'

#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 !
Quote
#2
message = df.sort_values(by='Employed On',ascending=False).to_string(index=False)
I assume it has something to do with .to_string()
Quote
#3
Just remove .to_string(...) in line #35. to_html() not a string method, it is for dataframes.
Quote
#4
(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).
Quote
#5
(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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Match a table with sub-total values to its detail value table klllmmm 2 1,292 Apr-03-2019, 11:28 AM
Last Post: klllmmm
  pandas Dataframe as "confidence table" for matchmaking? takaa 1 957 Nov-28-2017, 07:48 PM
Last Post: takaa

Forum Jump:


Users browsing this thread: 1 Guest(s)