Differences in Data Frames w/ Partial Matches for Python

May 28, 2022

This will be short and to the point. I’m looking for a summary of the differences between the data frames, but with partial matching counting as a non-difference.

import pandas as pd
import numpy as np

abc = {'Sport' : ['Football', 'Basketball', 'Baseball', 'Hockey'], 'Year' : ['2021','2021','2022','2022'], 'ID' : ['1','2','3','4']}
abc = pd.DataFrame({k: pd.Series(v) for k, v in abc.items()})
abc
xyz = {'SportLeague' : ['Football:NFL', 'Basketball:NBA', 'Baseball:MLB', 'Hockey:NHL', 'Soccer:MLS'], 'Year' : ['2022','2022','2022','2022', '2022'], 'ID' : ['2','3','2','4', '1']}
xyz = pd.DataFrame({k: pd.Series(v) for k, v in xyz.items()})
xyz = xyz.sort_values(by = ['ID'], ascending = True)
abc

>Solution :

Here’s an idea using pd.DataFrame.compare, but you need to reindex dataframes to get like indexing:

xyz = xyz.assign(**xyz['SportLeague'].str.split(':', expand=True).set_axis(['Sport','League'], axis=1))
xyz_c = xyz.reindex(abc.columns, axis=1)
xyz_c.compare(abc.reindex_like(xyz_c), keep_shape=True, keep_equal=True)

Output:

        Sport              Year         ID      
         self       other  self other self other
4      Soccer         NaN  2022   NaN    1   NaN
0    Football    Football  2022  2021    2     1
2    Baseball    Baseball  2022  2022    2     3
1  Basketball  Basketball  2022  2021    3     2
3      Hockey      Hockey  2022  2022    4     4