Comparing two data frames and returning a different value

Question

I am quite new to coding and am stuck on a problem. I have two DF's: df1 contains a TrailerID df2 contains a TrailerID and a number

If the Trailer ID's match I would like to take the number value from df2 and insert it into df1

I have managed to compare the columns and return a 1 or 0 but now I am not sure on how to select the data from the number column that corresponds to the 1 or 0.

from pandas import pandas
import pandas as pd
import numpy as np

df1=pd.read_excel("sheet1.xlsx",sheet_name=0)
df2=pd.read_excel("sheet2.xlsx",sheet_name=0)

print(df1)
   TrailerID
0       abc
1       def
2       ghi
3       123
4       456
5       789

print(df2)
  TrailerID  Number
0       abc  123647
1       def  937217
2       ghi  282838
3       758  183650
4      sh67  182838
5       789  273747

df1['new'] = df2.TrailerID.isin(df1.TrailerID).astype(np.int8)

print(df1)
  TrailerID  new
0       abc    1
1       def    1
2       ghi    1
3       123    0
4       456    0
5       789    1

I have the new column in df1 with 1 and 0's but I need this to be the number value from df2

Answer 1

This is similar to LEFT JOIN in SQL.

df3 = df1.merge(df2, how='left', on='TrailerID')