title: Pandas add column from one dataframe to another based on a join
body: <p>Assume I have 2 dataframes. I want to add a column of dataframe 1 to dataframe 2 based on a column lookup. If the join is not possible, I want in the extra column a certain constant (so I can filter for that). </p>
  
  <p><strong>Graphically:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/kCLHW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/kCLHW.png" alt="enter image description here"></a> </p>
  
  <p><strong>Code:</strong></p>
  
  <pre><code>import pandas as pd
  import numpy as np
  
  data = np.array([['','Col1','Col2'],
                  ['Row1','2','TWO'],
                  ['Row2','1','ONE']]
              )
  
  data2 = np.array([['','Col3','Col4'],
                  ['Row1','1','T1'],
                  ['Row2','2','T2'],
                  ['Row3','3','T3']]
              )
  
  df = pd.DataFrame(data=data[1:,1:],
                    index=data[1:,0],
                    columns=data[0,1:])
  
  df2 = pd.DataFrame(data=data2[1:,1:],
                    index=data2[1:,0],
                    columns=data2[0,1:])
  
  result_df = df2 + join Col2 based on df2.Col3 = df.Col1. Add certain string constant if join fails. 
  
  print(df)
  print(df2)
  print(result_df)
  </code></pre>