CODEWITHSUNDEEP
pythonpandasdataframe
nunodsousa
nunodsousa

Reputation: 2785

Merge pandas DataFrame and return common values with the column name

Let us consider two different pandas dataframes df1 and df2 described below. This exercise consists of comparing all columns row by row and return the value that is in common in both dataframes, as well as the column name.

Let us give an example for a better understanding. Let us assume the folllowing dataframes:

df1 = pd.DataFrame([[23,12,44],[34,55,33]], columns = ['A', 'B', 'C'])
df2 = pd.DataFrame([[11,12,43],[10,51,34]], columns = ['D', 'E', 'F'])

The result of the operation should be:

   col   value
0   E    12
1   F    34

Is it possible to do it in an efficient way?

Upvotes: 0

Views: 71

Answers (1)

Erfan
Erfan

Reputation: 42886

We can use pd.melt to get the columns to rows, and after that do an inner merge:

df1_melt = df1.melt(value_vars=df1.columns, var_name='Cols')
df2_melt = df2.melt(value_vars=df2.columns, var_name='Cols')

df_final = pd.merge(df2_melt, df1_melt, on='value', suffixes=['', '_1']).drop('Cols_1', axis=1)

print(df_final)
  Cols  value
0    E     12
1    F     34

Edit: Intermediate melted tables look like this:

print(df1_melt, '\n')
print(df2_melt)

  Cols  value
0    A     23
1    A     34
2    B     12
3    B     55
4    C     44
5    C     33 

  Cols  value
0    D     11
1    D     10
2    E     12
3    E     51
4    F     43
5    F     34

Upvotes: 3

Related Questions