replicate iferror and vlookup in a pandas join
<p>I want to join two dataframes:</p>

<pre><code>df1 = pd.DataFrame({'Banner': {0: 'banner1', 1: 'banner2', 2: 'banner3'},
                    'Campaign': {0: 'campaign1', 1: 'campaign2', 2: '12345'},
                    'Country ': {0: 'de', 1: 'it', 2: 'de'},
                    'Date': {0: '1/1/2016', 1: '2/1/2016', 2: '1/1/2016'},
                    'Value_1': {0: 10, 1: 5, 2: 20}})

df2 = pd.DataFrame({'Banner': {0: 'banner1', 1: 'banner2', 2: 'banner3', 3: 'banner4', 4: 'banner5'},
                    'Campaign': {0: 'campaign1',1: 'campaign2', 2: 'none',3: 'campaign4',4: 'campaign5'},
                    'Country ': {0: 'de', 1: 'it', 2: 'de', 3: 'en', 4: 'en'},
                    'Date': {0: '1/1/2016', 1: '2/1/2016', 2: '1/1/2016', 3: '3/1/2016', 4: '4/1/2016'},
                    'Value_2': {0: 5, 1: 10, 2: 15, 3: 20, 4: 25},              
                    'id_campaign': {0: 'none', 1: 'none', 2: '12345', 3: 'none', 4: 'none'}})
</code></pre>

<p><strong>edit</strong>:
let's even imagine the option:</p>

<pre><code>df1 = pd.DataFrame({'Banner': {0: 'banner1', 1: 'banner2', 2: 'banner3'},
                'Campaign': {0: 'campaign1', 1: 'campaign2', 2: '12345'},
                'Date': {0: '1/1/2016', 1: '2/1/2016', 2: '1/1/2016'},
                'Value_1': {0: 10, 1: 5, 2: 20}})
</code></pre>

<p>I have to join df2 and df1 on the keys:</p>

<ul>
<li>Date</li>
<li>Campaign</li>
<li>Banner</li>
</ul>

<p>The issue here is that when the match under the key "Campaign" is not found, the key should be switched to field "id_campaign".</p>

<p>I would like to obtain this dataframe:</p>

<pre><code>df_joined = pd.DataFrame({'Banner': {0: 'banner1', 1: 'banner2', 2: 'banner3', 3: 'banner4', 4: 'banner5'},
                      'Campaign': {0: 'campaign1', 1: 'campaign2', 2: 'none', 3: 'campaign4', 4: 'campaign5'},
                      'Country ': {0: 'de', 1: 'it', 2: 'de', 3: 'en', 4: 'en'},
                      'Date': {0: '1/1/2016', 1: '2/1/2016', 2: '1/1/2016', 3: '3/1/2016', 4: '4/1/2016'},
                      'Value_1': {0: 10, 1: 5, 2: 20, 3: 0, 4: 0},
                      'Value_2': {0: 5, 1: 10, 2: 15, 3: 20, 4: 25}, 
                      'id_campaign': {0: 'none', 1: 'none', 2: '12345', 3: 'none', 4: 'none'}})
</code></pre>

<p>any help is really appreciated.</p>

<p>You can use double <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> by 3 and 2 keys and then fill not match values by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.combine_first.html" rel="nofollow"><code>combine_first</code></a> from column <code>Value_1</code> of <code>df4</code>:</p>

<pre><code>df3 = pd.merge(df2, df1.drop('Country', axis=1), on=['Date','Campaign','Banner'], how='left')
df4 = pd.merge(df2, df1, on=['Date','Banner'], how='left')

print (df3)
    Banner   Campaign Country       Date  Value_2 id_campaign  Value_1
0  banner1  campaign1       de  1/1/2016        5        none     10.0
1  banner2  campaign2       it  2/1/2016       10        none      5.0
2  banner3       none       de  1/1/2016       15       12345      NaN
3  banner4  campaign4       en  3/1/2016       20        none      NaN
4  banner5  campaign5       en  4/1/2016       25        none      NaN

print (df4['Value_1'])
0    10.0
1     5.0
2    20.0
3     NaN
4     NaN
Name: Value_1, dtype: float64

df3['Value_1'] = df3['Value_1'].combine_first(df4['Value_1']).fillna(0).astype(int)
print (df3)
    Banner   Campaign Country       Date  Value_2 id_campaign  Value_1
0  banner1  campaign1       de  1/1/2016        5        none       10
1  banner2  campaign2       it  2/1/2016       10        none        5
2  banner3       none       de  1/1/2016       15       12345       20
3  banner4  campaign4       en  3/1/2016       20        none        0
4  banner5  campaign5       en  4/1/2016       25        none        0
</code></pre>

python pandas join merge