return all equal columns

user_6790701 0
Viewed 87

if I have these dataframes

df1 = pd.DataFrame({'index': [1,2,3,4],
                    'col1': ['a','b','c','d'],
                   'col2': ['h','e','l','p'],
                   'col3': ['p','l','i','z']})
df2 = pd.DataFrame({'index': [1,2,3,4],
                    'col1': ['a','e','f','d'],
                   'col2': ['h','e','l','p'],
                   'col3': ['p','l','i','z']})

df1

   index col1 col2 col3
0      1    a    h    p
1      2    b    e    l
2      3    c    l    i
3      4    d    p    z

df2

   index col1 col2 col3
0      1    a    h    p
1      2    e    e    l
2      3    f    l    i
3      4    d    p    z

i can run df1['col1'].equals(df2['col1']) on each column, but if they're so many columns i'll need a code to return all equal columns or all unique columns, how can i achieve that?

pandas python
3 Answers

It is possible compare this way if same number of rows and same index in both DataFrames - compare by DataFrame.eq and test if all Trues columns by DataFrame.all, last select by DataFrame.loc:

df11 = df1.set_index('index')
df22 = df2.set_index('index')

df = df11.loc[:, df11.eq(df22).all()]
print (df)
      col2 col3
index          
1        h    p
2        e    l
3        l    i
4        p    z

Detail:

print (df11.eq(df22))
        col1  col2  col3
index                   
1       True  True  True
2      False  True  True
3      False  True  True
4       True  True  True

For not matched columns filter columns names with inverted mask by ~:

cols = df11.columns[~df11.eq(df22).all()]
user_29010020

Here is a solution which works even if the names of the columns are different in df1 and df2 (I added an underscore to column names in df2 to show that).
It loops over df2 columns and uses a dict (dict_tuple_to_name_1) to look up efficiently for equal columns in df1.
The order of columns in both dataframes does not matter, neither the index.
It outputs (in dict_equal_cols) the the list of columns in df2 equal to each column in df1.
I added another column col4 in df2 so that col3 and col4 in df2 are both equal to col3 in df1 (to test the case of multiple columns in df2 equal to a given column in df1).

import pandas as pd
from collections import defaultdict

df1 = pd.DataFrame({'index': [1,2,3,4],
                    'col1': ['a','b','c','d'],
                   'col2': ['h','e','l','p'],
                   'col3': ['p','l','i','z']})
df2 = pd.DataFrame({'index_': [1,2,3,4],
                    'col1_': ['a','e','f','d'],
                   'col2_': ['h','e','l','p'],
                   'col3_': ['p','l','i','z'],
                   'col4_': ['p','l','i','z']})

dict_tuple_to_name_1 = {tuple(df1[col_name]): col_name for col_name in df1.columns}
dict_equal_cols = defaultdict(list)

for col_name_2 in df2.columns:
    col_name_1 = dict_tuple_to_name_1.get(tuple(df2[col_name_2]))
    if col_name_1 is not None:
        # we found equal columns, so we add the column name to the list
        dict_equal_cols[col_name_1].append(col_name_2)

for col_name_1, col_names_2 in dict_equal_cols.items():
    print("column {} in df1 has these equal columns in df2 : {}".format(col_name_1, col_names_2))

Which outputs :

column index in df1 has these equal columns in df2 : ['index_']
column col2 in df1 has these equal columns in df2 : ['col2_']
column col3 in df1 has these equal columns in df2 : ['col3_', 'col4_']
user_39658880

Try using merge on transposed dataframes.

import pandas as pd

df1 = pd.DataFrame({'index': [1,2,3,4],
                    'col1': ['a','b','c','d'],
                   'col2': ['h','e','l','p'],
                   'col3': ['p','l','i','z']})

df2 = pd.DataFrame({'index': [1,2,3,4],
                    'col1': ['a','e','f','d'],
                   'col2': ['h','e','l','p'],
                   'col3': ['p','l','i','z']})

df1t = df1.T
df1t['col_name'] = df1.columns
df2t = df2.T
df2t['col_name'] = df2.columns
df_match = pd.merge(df1t, df2t, how='outer', indicator=True)
df_match


0   1   2   3   col_name    _merge
0   1   2   3   4   index   both
1   a   b   c   d   col1    left_only
2   h   e   l   p   col2    both
3   p   l   i   z   col3    both
4   a   e   f   d   col1    right_only
user_108157180
Related