how to fetch the values from the list of values in dataframe column

Question

mail_df = pd.DataFrame()
name = 'X'
action = 'active'
month = 'January'
message = name + ' gmail account is ' + action + ' in the month of ' + month
mail_dict = {'email_id':'[email protected]','email_msg':message}
df = pd.DataFrame.from_dict([mail_dict])
mail_df = mail_df.append(df)

the above code will be performed in the loop and will be adding more rows into the dataframe, below is the final dataframe:

then I had used groupby based on email_id, and the code and output dataframe is below:

grouped = mail_df.groupby('email_id').aggregate(lambda x: x.tolist())
pd.set_option("max_colwidth",2)
print(grouped)

Output

Grouped Dataframe Output:

                                                                                                                                                                email_msg
email_id
[email protected]  [X gmail account is active in the month of January, X gmail account is active in the month of February, X gmail account is inactive in the month of March]
[email protected]  [Y gmail account is active in the month of April, Y gmail account is inactive in the month of May, Y gmail account is inactive in the month of June]

from the grouped dataframe, I need to send mail to the respective ids in the email_id column as per the grouped dataframe , and the message should be in the below format:

Dear X,
The below are the account status:

 - Active -> January
 - Active -> February
 - Inactive -> March

In the similar manner, email has to be sent to other users. Can anyone help me to process the list of values in the email_msg column in the grouped dataframe for each row, and extract the value from email_msg and make it into the required email format?

Please post a reproducible sample of your grouped dataframe. This will help us answer better. — Mayank Porwal
– Mayank Porwal, Commented Apr 3, 2021 at 6:12

AlexisG · Accepted Answer · 2021-04-03 06:27:10Z

You make the task harder to concatenate your string to make a message, and then trying to split the message. you can simply add name, action and month to the Dataframe.

You can also simplify your code, by creating one list of dict and then create the dataframe with it. Instead of creating a new Dataframe each time and append it to mail_df.

Here is a solution

import pandas as pd


list_of_emails = []
name = 'X'
action = 'active'
month = 'January'
mail_dict = {'email_id': '[email protected]', 'name': name, "action": action, "month": month}
list_of_emails.append(mail_dict)

mail_df = pd.DataFrame(list_of_emails)
grouped = mail_df.groupby('email_id').aggregate(lambda x: x.tolist())

for index, row in grouped.iterrows():
    print(index)  # your email id for this email

    # Format the email message
    # we can take the first element of the list since the name should be the same
    message = f"Dear {row['name'][0]},\n"

    for i in range(len(row["action"])):
        message = message + f" - {row['action'][i]} -> {row['month'][i]} \n"


    print(message)
    # send the email

output message :

Dear X,
 - active -> January

Sam Szotkowski · Accepted Answer · 2021-04-03 06:47:10Z

Surely this can be improved, like the other commenter said, it doesn't make sense to split the strings then join them then split them then join them again. But given the question as asked, hopefully some of this can be useful. Note that using 'apply' can make things easier to read sometimes, but list comprehension of a pd.Series tends to be quite fast.

# Add columns to store lists of active status (5th word in email_msg) and months (last word) and name (first word)
email_msg = grouped['email_msg'].str.split()
grouped['name'] = [msg[0].capitalize() for msg in email_msg]
grouped['active'] = [msg[4].capitalize() for msg in email_msg]
grouped['month'] = [msg[-1].capitalize() for msg in email_msg]

# Add column that combines 'active' and 'month' columns into 'formatted' column
grouped['formatted'] = [f'- {active} -> {month}' for active,month in zip(grouped['active'], grouped['month'])]
grouped['formatted'] = grouped['formatted'].str.join('\n')

# Add column with entire message 'entire_msg'
grouped['entire_msg'] = 'Dear ' + grouped['name'] + ',\nThe below are the account status:\n' + grouped['formatted']

# Send email to each row using a send_email(email_address, message) function
grouped.apply(lambda df: send_email(df['email_id'], df['entire_msg']), axis=1)

Collectives™ on Stack Overflow

how to fetch the values from the list of values in dataframe column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related