Trying to create a pandas series within a dataframe with values based on whether or not keys are in another dataframe
<p>Boiling it down simply...</p>

<p>Dataframe 1 = yellow_fruits
The columns are fruit_name, and location</p>

<p>Dataframe 2 = red_fruits
The columns are fruit_name, and location</p>

<p>Dataframe 3 = fruit_montage
The columns are fruit_name, pounds_of_fruit_needed, freshness</p>

<p>Let's say I want to add a column to Dataframe 3 called 'color.'  The value will be yellow if the fruit is yellow, red if the fruit is red, and unknown if it's not red or yellow.</p>

<p>Basically, pseudocode...</p>

<p>If the fruit is in the yellow fruit dataframe, yellow goes in the column
If the fruit is in the red fruit dataframe, red goes in the column
If the fruit is not in either of those dataframes, 'unknown' goes in the column.</p>

<p>My code produced an error:</p>

<pre><code> if df3['fruit_name'].isin(df1['fruit_name']):
        data = "'yellow"
    elif df3['fruit_name'].isin(df2['fruit_name']):
        data = "red"
    else:
        data = "unknown"

    df3['color'] = pd.Series(data, index = df3.index)
</code></pre>

<p>The error:</p>

<p>C:\Anaconda2\lib\site-packages\pandas\core\generic.pyc in <strong>nonzero</strong>(self)
    890         raise ValueError("The truth value of a {0} is ambiguous. "
    891                          "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
--> 892                          .format(self.<strong>class</strong>.<strong>name</strong>))
    893 
    894     <strong>bool</strong> = <strong>nonzero</strong></p>

<p>ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().</p>

<p>The classic way would be to use your conditions as indexers:</p>

<pre><code>df1 = pd.DataFrame({'fruit_name':['banana', 'lemon']})
df2 = pd.DataFrame({'fruit_name':['strawberry', 'apple']})
df3 = pd.DataFrame({'fruit_name':['lemon', 'rockmelon', 'apple']})

df3["color"] = "unknown"
df3["color"][df3['fruit_name'].isin(df1['fruit_name'])] = "yellow"
df3["color"][df3['fruit_name'].isin(df2['fruit_name'])] = "red"
df3

#   fruit_name    color
# 0      lemon   yellow
# 1  rockmelon  unknown
# 2      apple      red
</code></pre>

<p>A more functional way would be to write your logic as a function and map it along your series, however this is likely to be quite a bit slower, since a lot of the speed of pandas/numpy comes from using vectorized operations:</p>

<pre><code>def get_fruit_color(x):
    if x in df1['fruit_name'].unique():
        data = "yellow"
    elif x in df2['fruit_name'].unique():
        data = "red"
    else:
        data = "unknown"

    return data

df3["color"] = df3["fruit_name"].map(get_fruit_color)
</code></pre>

<p>An SQL-inspired approach would be to store your mappings in a dataframe, and do a join (called a merge in pandas); this should be a very performant option. Specifying <code>how='left'</code> means that it will be a left join, so that if no match is found for the join condition, the row will still remain, with a null value:</p>

<pre><code>colors = ([(x, 'yellow') for x in df1['fruit_name'].unique()] 
           + [(x, 'red') for x in df2['fruit_name'].unique()])
colors_df = pd.DataFrame(colors, columns = ['fruit_name', 'color'])
df3.merge(colors_df, how='left').fillna("unknown")
</code></pre>

<p>Finally my favourite method (although maybe it'a a little "clever") would be to use a dict to map your values (this is a special pandas trick), this will leave <code>NaN</code> if no match is found, so you can fill these with <code>fillna</code>:</p>

<pre><code>df3["color"] = df3["fruit_name"].map(dict(colors)).fillna("unknown")
</code></pre>

python pandas dataframe series