Merging multiple dataframe bassed on matching values from three column into single dataframe
<p>I have multiple data frames (25 dataframes), I am looking for recurrently occuuring row values from three columns of all dataframes. The following are my example of my daframes</p>

<pre><code>df1
chr start   end     name
1   12334   12334   AAA
1   2342    2342    SAP
2   3456    3456    SOS
3   4537    4537    ABR
df2
chr start   end     name
1   12334   12334   DSF
1   3421    3421    KSF
2   7689    7689    LUF
df3 
chr start   end     name
1   12334   12334   DSF
1   3421    3421    KSF
2   4537    4537    LUF
3   8976    8976    BAR
4   6789    6789    AIN
</code></pre>

<p>And finally what I am aiming is to look into fist three columns of these daframe and extract  a new dataframe based on matching rows from these 3 column values along with names of datafrme as last column. So the final data frame should look like this,</p>

<pre><code>chr start   end     name    Sample
1   12334   12334   AAA df1
1   12334   12334   AAA df2
1   12334   12334   AAA df3
</code></pre>

<p>I know the following line of python script will create the above output without Sample as a column.</p>

<pre><code>s1 = pd.merge(df1, df2, how='left', on=['chr', 'start', 'end'])
df_final = pd.merge(s1, df3[['chr', 'start', 'end']], how='left', on=['chr', 'start','end'])
</code></pre>

<p>but I have more than 25 dataframe which I need to look for merge based on matching values. Any robust and better solution would be really appreciated</p>

<p>Say you have a dictionary mapping sample names to DataFrames:</p>

<pre><code>dfs = {'df1': df1, 'df2': df2}
</code></pre>

<p>(and so on).</p>

<p>The common relevant keys (in hashable form) are</p>

<pre><code>common_tups = set.intersection(*[set(df[['chr', 'start', 'end']].drop_duplicates().apply(tuple, axis=1).values) for df in dfs.values()])
</code></pre>

<p>Now you just need, for each DataFrame, to find the relevant rows, add the name of the DataFrame as the sample, and concatenate the results:</p>

<pre><code>pd.concat([df[df[['chr', 'start', 'end']].apply(tuple, axis=1).isin(common_tups)].assign(Sample=name) for (name, df) in dfs.items()])
</code></pre>

python pandas numpy dataframe