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?

Answer 1

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))}

Answer 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]