I have two dataframes that I would like to sort through and make a new dataframe. I would like to go through the first dataframe and append columns to it from the second dataframe to create a new third dataframe, based upon whether the values match in certain columns of the two dataframes:
df1=pd.DataFrame([[0.221,2.233,7.84554,10.222],[0.222,2.000,7.8666,10.000],[0.220,2.230,7.8500,10.005],[0.225,2.238,7.8200,10.045],[0.230,2.231,7.8560,10.605],[0.210,2.230,7.8510,10.006]],columns=('sample','mz','mz2','abundance'))
#df1 displays dataframe
df2=pd.DataFrame([[0.219,2.233,7.8450],[0.220,2.002,7.8669],[0.229,2.238,7.8508]],columns=('sample','area','retention'))
#df2 displays dataframe
Hoping to produce this:
df4=pd.DataFrame([[0.221,2.233,7.84554,10.222,0,0],[0.222,2.000,7.8666,10.000,0,0],[0.220,2.230,7.8500,10.005,2.002,7.8669],[0.225,2.238,7.8200,10.045,0,0],[0.230,2.231,7.8560,10.605,0,0],[0.210,2.230,7.8510,10.006,0,0]],columns=('rt','mz','mz2','abundance','area','retention'))
df4 #displays dataframe
This is what I have tried, but the output isn't exactly what I am looking for:
df3 = df1[[any(abs(df2.sample - valu) <= 0.0001) for valu in df1.sample]].append
Any suggestions would be appreciated on how to do this!
