how can i compare two dataframes row by row in panda with insensitive cases?

When one wants a case insensitive comparison between strings in python, one would like to set both strings to upper or lower case and then do a traditional == or != comparison.

When using pandas, this can be achieved by the .str Series method, which allows the use of string methods such as .upper() and .lower(). In your case, a possible solution would be:

df, df1 = df.astype(str), df1.astype(str)
_df = df1.copy()

for i in df1.index:
    comparison = df.loc[i].str.upper() != df1.loc[i].str.upper()
    _df.loc[i, comparison] = '*' + df1.loc[i, comparison].astype(str) + '*'
    

If we print the resulting dataframe _df we get your desired output 1:

     ID    NAME ID_COUNTRY  COUNTRY ID_CITY  CITY STATUS
0    14     Kwi          1  *MXICO*      10    MX     **
1  *10*     NED          2    itaLY    *22*  *AT*     OK
2    14    riCK          3   CANADA      21    CA     **
3  *11*    nich        *6*  ENGLAND      31   ENG     OK
4    14  DIONIC          5  GERMANY      18  *EG*     **

In this case I’m assuming that corresponding rows have the same index across both dataframes.

For your second desired output, you can just iterate over each row again:

print("Data in df1 that does't match df:")
for i in _df.index:
    not_matching_cols = _df.loc[i].str.endswith('*')
    if not_matching_cols.any():
        print(','.join(_df.loc[i, not_matching_cols].index), end=' ')
        print('with', 'NAME', df1.loc[i, 'NAME'], 'ID_COUNTRY', df1.loc[i, 'ID_COUNTRY'])

If you also want to print the numbers of rows missing on df1 you can just add

print(df.shape[0] - df1.shape[0], 'ROWS ARE MISSING')

The output of this last part should be:

Data in df1 that does't match df:
COUNTRY,STATUS with NAME Kwi ID_COUNTRY 1
ID,ID_CITY,CITY with NAME NED ID_COUNTRY 2
STATUS with NAME riCK ID_COUNTRY 3
ID,ID_COUNTRY with NAME nich ID_COUNTRY 6
CITY,STATUS with NAME DIONIC ID_COUNTRY 5
4 ROWS ARE MISSING

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top