Check if value is present in dataframe and find it within a dictionary

Question

Assuming a dataframe

Market | Status | Team |
-------|--------|------|
Chicago|   1    | Tom  |
Chicago|   1    | Tom  |
SF Bay |   3    | Julia|
SF Bay |   1    | Julia|
SF Bay |   1    | Julia|

And a dictionary

Team = {"Tom": "[email protected]", "Julia": "[email protected]", "Carol": "[email protected]"}

I want to get each team members specific dataframe, convert it to HTML and email it to them. I can get the dataframes individually that I need and then df.to_html() it. In the case above, Carol is in the dictionary, but not within the dataframe, so I don't want her to receive an email.

I've tried

for i in Team:
    df[df['Team'].str.contains(i)]
    Mail = df[df['Team'].isin([i])]
    ...
    #send an email

But this would then send an email with a blank dataframe to Carol. How can I iterate through easily and get only those names that are present from my dictionary and then utilize the dictionary email value to send an email?

coldspeed95 · Accepted Answer · 2018-02-08 20:46:48Z

Use map, convert the Team column to a column of email addresses, and then use a groupby to get each team member's DataFrame separately, using a dictionary comprehension.

df['Team'] = df['Team'].map(Team)
df

    Market  Status             Team
0  Chicago       1    [email protected]
1  Chicago       1    [email protected]
2   SF Bay       3  [email protected]
3   SF Bay       1  [email protected]
4   SF Bay       1  [email protected]

df_dict = {i : g for i, g in df.groupby('Team')}

df_dict.keys()
dict_keys(['[email protected]', '[email protected]'])  # look ma, no Carol

Note that map scales better than replace. After this, you can iterate over each key-value pair, and dispatch:

for email, df in df_dict.items():
    data = df.to_html()
    ... # dispatch `data` to `email`

For more info on how to dispatch email through python using the SMTP protocol, refer to the email module in the standard library.

If you want to keep the Name column, you can combine the groupby and map step together, and simplify your solution:

df_dict = {i : g for i, g in df.groupby(df.Team.map(Team))}

This works perfectly! Is there any way to maintain their names so I can place them inside the email?
@ATCH_torn Of course! It's even simpler, in fact. I've edited my answer.
That works great! One last question. Can I then take the name and append it to the dictionary? Ideally I'd like to have name: email, df all in one fell swoop.
@ATCH_torn Well, you could do so. The key is the name and the value is a list/tuple. You could choose to create a new dict by iterating over df_dict. For each value, df.Team.unique() gives you the name. Good luck!

BENY · Accepted Answer · 2018-02-08 20:05:11Z

2

Using replace :-)

df.Team.replace(d,inplace=True)
df
Out[176]: 
    Market  Status             Team
0  Chicago       1    [email protected]
1  Chicago       1    [email protected]
2    SFBay       3  [email protected]
3    SFBay       1  [email protected]
4    SFBay       1  [email protected]

answered Feb 8, 2018 at 20:05

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Check if value is present in dataframe and find it within a dictionary

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related