2

im new to python and trying to understand how to send email alerts. I have a csv file, the below is the dataframe for it:

,Latitude,Longtitude,LocationKey,MatchDescription
0,38.67694301003448,-77.7429029707531,609751,rooftop
1,29.372805012097956,-98.53188396363142,609750,rooftop
2,37.72961100923345,-122.38610096386918,609749,rooftop
3,42.509437395496974,-70.86884298867957,609748,rooftop
4,was not geocoded,rooftop
5,25.982770005233007,-80.16985001050395,609745,place

everyday this particular csv regenerates with new values, however, occasionally, I get the above error saying was not geocoded. I want a script that sends me an email alert each time that string shows up in this generated csv.

import pandas as pd
import smtplib
from email.message import EmailMessage

df = pd.read_csv('StgDimLocationEsri.csv')

cols_to_check = ["Latitude","Longtitude","LocationKey"]


def email_alert(subject,body,to):
            msg = EmailMessage()
            msg.set_content(body)
            msg['subject'] = subject
            msg['to'] = to
            # msg['from'] = from
            
            user = "xx@gmail.com"
            msg['from'] = user
            password = "clqdgqyfrleisynd"
            
            server = smtplib.SMTP("smtp.gmail.com", 587)
            server.starttls()
            server.login(user,password)
            server.send_message(msg)
            
            server.quit()
if __name__ == '__main__':
    email_alert("Hey","Error","yy.t@cfi.com")
    
for col in cols_to_check:
    if not pd.to_numeric(df[col], errors='coerce').all():
        email_alert()

The problem is that i am receiving an email even if there are only numerics under these columns. I tried to replace the was not geocoded with numericals and i still got the error email

1 Answers1

0

Since you have a csv file, it is better to read it using pandas and then check whether all values are numeric in the specific columns, likewise :

import pandas as pd
import smtplib
from email.message import EmailMessage

df = pd.read_csv('StgDimLocationEsri.csv')

cols_to_check = ["Latitude","Longtitude","LocationKey"]

#df = pd.DataFrame({'Latitude': [11.324342,343.4552,434.3, 'nan',-34895.6661,1,2], 'Longtitude': list('ABCDEFG'), 'LocationKey': [38.676943448,-38.6769438,34,23,12,56,67]})

def email_alert(subject,body,to):
    msg = EmailMessage()
    msg.set_content(body)
    msg['subject'] = subject
    msg['to'] = to
        
    user = "from_email@gmail.com"
    msg['from'] = user
    password = "pwd"
        
    server = smtplib.SMTP("smtp.gmail.com", 587)
    server.starttls()
    server.login(user,password)
    server.send_message(msg)
        
    server.quit()

if __name__ == '__main__':

    for col in cols_to_check:
        if not df[col].apply(lambda x: isinstance(x, (int, float))).all():
        
        body = "There is a non-numeric value in column " + col + "."
     
        email_alert("Hey",body,"to_email@gmail.com")

Since I did not have access to the csv file I tried creating a sample DataFrame which has been commented out in the above code.

Shivam Roy
  • 1,961
  • 3
  • 10
  • 23
  • 1
    Hi Shivam, thanks for your response. I believe the issue i have is more dealing with the email sending code. Looking for an alternative script that can send me the email alert –  Jun 12 '21 at 18:17
  • Ohh I see, apologies. I was focussing more on how you were checking the column datatype. My mistake. – Shivam Roy Jun 12 '21 at 18:18
  • 1
    Hi Shivam, one question within your code, where would i have to metion latitude,longitude,location key? it gives me a name error saying these are not defined –  Jun 12 '21 at 19:58
  • 1
    I am going to re edit my question with your code –  Jun 12 '21 at 19:58
  • Hi Lodu, I’m sorry I forgot to enclose them in inverted commas, thats why the error. I’ve edited the answer. Thanks – Shivam Roy Jun 12 '21 at 23:11
  • 1
    Hi Lodu, I’m sorry I forgot to enclose them in inverted commas, thats why the error. Also, I have just realised the method “is_numeric_dtype” is deprecated in the new pandas version. I’ve edited the answer. Apologies for the trouble.Thanks – Shivam Roy Jun 12 '21 at 23:26
  • Hi Shivam, the email is working now, but i think something is either wrong with your if clause or the placement in my script. Going to re-edit with my final script that sends the email. the problem is, it is sending me an email even though all the values under columns are numeric –  Jun 13 '21 at 04:29
  • Hi Lodu, I apologise for making so many edits again and again, this time I've tried the code completely and made the edit. Also, you should call your function only inside the `if` statement. If you call it right after the __name__ == "__main__", it will execute irrespective of whether the condition is met or not. – Shivam Roy Jun 13 '21 at 07:33
  • 1
    Hey Shivam, thanks for your response. I just tried your new solution but I am still receiving the email regardless of value being numeric. I feel i may be not placing it correct. Since you mentioned, you tried the complete code and it is working for you, can you please edit your answer with my script integrated. I will try that as the final answer and mark it correct when it works for me. Thank you again :) –  Jun 13 '21 at 09:04
  • 1
    Hi Lodu, thanks. I have made the edit in the answer. This worked for me perfectly for the sample DataFrame I had created. I also checked if the email part was working correctly by disabling my GMail security settings. If it still does not work for you, there might be a possibility that the numeric values in your csv file are also being read as `String`. To check that, you can load a correct csv file which has all numeric values in the specific columns and check by typing `df.dtypes`. The numeric columns should have an `int` or `float` type. – Shivam Roy Jun 13 '21 at 10:19