Home>

I want to extract only the date and content columns of csv, extract only the columns that contain only yesterday's date, and send an email.
I described it with the code below, but an error occurs.

I think this is because iloc will cause an error if the dataframe is empty.

① Create a pandas DataFrame that extracts only specific columns of csv
② Extract only yesterday's column
③ Perform the processing of ④ to ⑤ for his 1st and 2nd lines (index is 0 and 1) of the created DataFrame
④ Extract the column name and data of each column of the row to be processed, add a line feed code to each, and concatenate
⑤ Each column name + data extracted and concatenated is further concatenated with a line feed code
⑥ Insert the linked data as the body of the email and send it.

CSV data


Date Content C D
2021/02/07 (Sun) 11:10:00 test2 test3 test4
2021/02/06 (Sat) 20:00:00 test5 test6 test7
2021/02/10 (Wed) 15:59:00 test8 test9 test10

I want to get the columns for yesterday and send an email.

Date Contents
2021/02/10 (Wed) 15:59:00 test8

Current code

Gmail
from datetime import datetime as dt, date, timedelta
import csv
import pandas as pd
from email.mime.text import MIMEText
import smtplib
today= dt.today ()
(dt.strftime (today,'% Y /% m /% d'))
yesterday= today --timedelta (days= 1)
print (yesterday.strftime ("% Y /% m /% d"))
# Load csv data into df.
filename= "test.csv"
df= pd.read_csv (filename, encoding='shift-jis')
# # # # Df_i= df.set_index ("A", "B", "C")
df_i= df [["date", "content"]]
print (df_i)
df_h= df [df ["date"]. str.contains (yesterday)]
print (df_h)
# You can get yesterday's data well so far.
account= "[email protected]"
password= "123"
to_email= "123"
from_email= "[email protected]"
for j in range (2):
    subject= "[TEST]"
    message='\ n'. join ([f "{column} \ n {data} \ n" for column, data in df_h.iloc [j] .iteritems ()]) # # # #!
    print (message)
    msg= MIMEText (message)
    msg ["Subject"]= subject
    msg ["To"]= to_email
    msg ["From"]= from_email
    server= smtplib.SMTP ("smtp.gmail.com", 587)
    server.starttls ()
    server.login (account, password)
    server.send_message (msg)
    server.quit ()

Error code

File "c: /Users/test/Documents/S3/test.py", line 34, in <
module >
    message='\ n'. join ([f "{column} \ n {data} \ n" for column, data in df_h.iloc [j] .iteritems ()])
  File "C: \ Users \ test \ AppData \ Local \ Programs \ Python \ Python38-32 \ lib \ site-packages \ pandas \ core \ indexing.py", line 879, in __getitem__
    return self._getitem_axis (maybe_callable, axis= axis)
  File "C: \ Users \ test \ AppData \ Local \ Programs \ Python \ Python38-32 \ lib \ site-packages \ pandas \ core \ indexing.py", line 1496, in _getitem_axis
    self._validate_integer (key, axis)
  File "C: \ Users \ test \ AppData \ Local \ Programs \ Python \ Python38-32 \ lib \ site-packages \ pandas \ core \ indexing.py", line 1437, in _validate_integer
    raise IndexError ("single positional indexer is out-of-bounds")
IndexError: single positional indexer is out-of-bounds

We apologize for the inconvenience, but we would appreciate it if you could answer.

Thank you.

Regarding the content of the question, the conditions (3) to (5) do not hold, but where and how is the content of the text to be included in the email created?

kunif2021-02-23 04:01:30

for j in range (2): should be for j in range (len (df_h)) :. Then I get an error with df_h= df [df ["date"]. str.contains (yesterday)]. It should actually be df_h= df [df ["date"]. str.contains (yesterday.strftime ("% Y /% m /% d"))].

metropolis2021-02-23 04:01:30

@kunif Thank you for your answer. The first thing I want to do is extract only yesterday's worth from csv. If there is, I will send you an email. If there is no yesterday's worth, I want to end normally.

sakaolo2021-02-23 04:01:30

@metropolis Thank you for your answer. Try the above method as well.

sakaolo2021-02-23 04:01:30

@metropolis I corrected the part you pointed out and it was executed without any problem. As shown below, when there is no yesterday's worth, Empty Data Frame is displayed and the email is not sent. Another time, only yesterday's minutes were processed. Thank you very much. Empty DataFrame Columns: [post_time, content_url] Index: []

sakaolo2021-02-23 04:01:30
  • Answer # 1

    Before answering:
    The items to be confirmed regarding "the conditions (3) to (5) are not met" written in the comment are as follows.

    Question The first row, "I want to extract only columns that contain only yesterday's date and send an email," says "row that contains only yesterday's date . >I want to extract only and send an email. "

    Similarly, "I want to get the columns for yesterday and send the email." Is "I want to get the rows for yesterday and send the email." I guess.

    Then, the content of the body of the email was written in the question article as follows:

    Date Contents
    2021/02/10 (Wed) 15:59:00 test8
    

    The content shown in "Current Code" is in line with another previous question:

    message='\ n'.join ([f "{column} \ n {data} \ n" for column, data in df_h.iloc [j] .iteritems ()]) # # # # !!
    

    When applied, it would look like this:

    date
    2021/02/10 (Wed) 15:59:00
    Contents
    test8
    

    Which or another is the content of the email body you really want?


    Problems known at this point:

    As

    @metropolis commented, the process for extracting yesterday's data doesn't work. "# You can get yesterday's data well so far." Is probably something wrong with confirmation or transcription mistake when entering a question.

    This line:

    yesterday= today --timedelta (days= 1)
    print (yesterday.strftime ("% Y /% m /% d"))
    

    What to do here:

    yesterday= (today --timedelta (days= 1)). strftime ("% Y /% m /% d")
    print (yesterday)
    

    This line is:

    df_h= df [df ["date"]. str.contains (yesterday)]
    

    Should be done:

    df_h= df_i [df_i ["date"]. str.contains (yesterday)]
    

    Similarly, as @metropolis commented, "I think it's because an error occurs if the dataframe is empty in iloc." That's right, but the cause is "a line that contains only yesterday's date." This is because the answer to the previous question, which assumes that there are two rows of data fixedly, is looped even though there is not always two rows of data because "only is extracted".

    Like @ metropolis's comment, this line is:

    for j in range (2):
    

    Should be done:

    for j in range (len (df_h)):
    

    Then the following lines may need to be modified to match the content of the email body you are trying to output this time:

    message='\ n'.join ([f "{column} \ n {data} \ n" for column, data in df_h.iloc [j] .iteritems ()]) # # # # !!
    

    Then, in the first place, it will be necessary to detect the case where "there is no line containing yesterday's date" and end it before processing the mail.

    The first half seems to be "question to question", so if there is a part where the question feels ambiguous, it is better to ask for clarification first with comments etc. rather than answering by speculation. I thought.

    cubick♦2021-02-23 04:01:30

    I commented at the beginning, but it is not clear what is inconsistent with that alone, and there are some places, so it seems difficult to do it only with comments, so I pointed out what is wrong with it. I intend to do it. I will revise the answer once it becomes clear.

    kunif2021-02-23 04:01:30

    I corrected the part pointed out by @metropolis and it was executed without any problem. As shown below, when there is no yesterday's worth, Empty Data Frame is displayed and the email is not sent. Another time, only yesterday's minutes were processed. Thank you very much. Empty DataFrame Columns: [post_time, content_url] Index: [] –

    sakaolo2021-02-23 04:01:30

    @kunif Also, thank you for your kindness.

    sakaolo2021-02-23 04:01:30