Merging or Join three DataFrames where all three DataFrames contain four identical columns and one u-CodePudding

I have three DataFrames: DF1, DF2, DF3. They all contain the columns ['BHID','FROM','TO','SAMP ID','*unique element*']. The DataFrames don't have the same amount of rows, but I would like to merge or join these DFs so that the final DataFrame contains all the unique elements where BHID FROM TO SAMP ID match exactly. The final DataFrame should look something like this -

BHID | FROM | TO | SAMP ID | *element 1* | *element2* | *element3*

I have tried the following approaches:

FinalDF = pd.merge(DF1,DF2,DF3, on=['BHID','FROM','TO','SAMP ID'], how='inner')

this gave me an error - ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Please can you assist with the right merging method or join method. It would be greatly appreciated. Thanks

CodePudding user response：

You can chain merge:

cols = ['BHID','FROM','TO','SAMP ID']
out = DF1.merge(DF2, on=cols).merge(DF3, on=cols)

CodePudding user response：

You can use concat also :

out = pd.concat([DF1,DF2,DF3], axis = 0)[['BHID','FROM','TO','SAMP ID']]