How to ignore Nan while performing the comparison

Question

I have pandas dataframe as below,

    Location    Loc_Code    Service_Onroad  Service_Cargo
0   Bangalore   Vadakara01  Common  Common
1   Bangalore   Hosdung02   Transit Transit
2   Bangalore   Hosdung02   Common  Transit
3   Bangalore   Kozhikode   Common  Common
4   Bangalore   Malappuram  Transit Transit
5   Bangalore   Kalpetta09  Common  Common
6   Bangalore   Kasargod    Transit Transit
7   Bangalore   Maananthavaadi  Common  Common
8   Bangalore   Alattur Transit Transit
9   Bangalore   Pazhani Common  NaN
10  Bangalore   Ernakulam   Transit Transit
11  Bangalore   Aluva   Connect Connect
12  Bangalore   Kochi   Common  Transit
13  Bangalore   Kochi   Transit Transit
14  Bangalore   Paravoor    Common  Common
15  Bangalore   Chaavakkaad Transit Transit
16  Bangalore   Tirur   Transit Transit
17  Bangalore   Mannaarkkad Common  Common
18  Bangalore   Koramangala NaN NaN
19  Bangalore   Rajna   NaN NaN
20  Bangalore   Palai09 Interconnect    NaN
21  Bangalore   Palai09 Common  NaN

I wanted to compare Service_Onroad with Service_Cargo and if it matches, I wanted to write it as "Matched" in new Column (i.e Results). And if it doesn't match, I wanted to write it as "Not Matched". In any of the column (Service_Onroad & Service_Cargo) if the value is NaN then I do not wanted to write anything there.

How can we do the comparison ? I believe that in Pandas, Nan is also taken as string for the comparison which results in "FALSE"? So how do we rule this out?

Have you tried any code to filter NaN? How about searching on this? https://stackoverflow.com/questions/944700/how-can-i-check-for-nan-values — asylumax, Jun 07 '20 at 20:55

score 0 · Answer 1 · answered Jun 07 '20 at 20:18

Try this:

df['Match'] = np.where((df['Service_Onroad']==df['Service_Cargo']), 'Matched', 'Non-Matched')
df['Match'] = np.where((df['Service_Onroad'].isna()) | (df['Service_Cargo'].isna()), '', df['Match'])
print(df)

     Location        Loc_Code Service_Onroad Service_Cargo        Match
0   Bangalore      Vadakara01         Common        Common      Matched
1   Bangalore       Hosdung02        Transit       Transit      Matched
2   Bangalore       Hosdung02         Common       Transit  Non-Matched
3   Bangalore       Kozhikode         Common        Common      Matched
4   Bangalore      Malappuram        Transit       Transit      Matched
5   Bangalore      Kalpetta09         Common        Common      Matched
6   Bangalore        Kasargod        Transit       Transit      Matched
7   Bangalore  Maananthavaadi         Common        Common      Matched
8   Bangalore         Alattur        Transit       Transit      Matched
9   Bangalore         Pazhani         Common           NaN
10  Bangalore       Ernakulam        Transit       Transit      Matched
11  Bangalore           Aluva        Connect       Connect      Matched
12  Bangalore           Kochi         Common       Transit  Non-Matched
13  Bangalore           Kochi        Transit       Transit      Matched
14  Bangalore        Paravoor         Common        Common      Matched
15  Bangalore     Chaavakkaad        Transit       Transit      Matched
16  Bangalore           Tirur        Transit       Transit      Matched
17  Bangalore     Mannaarkkad         Common        Common      Matched
18  Bangalore     Koramangala            NaN           NaN
19  Bangalore           Rajna            NaN           NaN
20  Bangalore         Palai09   Interconnect           NaN
21  Bangalore         Palai09         Common           NaN

Nev1111 · Accepted Answer · 2020-06-12T20:05:15.313

0

Define the conditions to be met bythe results column:

condition_1=((df['Service_Onroad'].isnull())|(df['Service_Cargo'].isnull()))
condition_2=df['Service_Onroad']==df['Service_Cargo']

Then use "np.where" and nest the conditions to obtain the result:

df['result']=np.where(condition_1,'',(np.where(condition_2,'match','no_match')))

df.to_excel('PATH',na_rep='')

edited Jun 12 '20 at 20:05

answered Jun 07 '20 at 20:39

Nev1111

1,039
9
13

When I write this dataframe to excel. It is also writing nan some of the columns. Why is it writing as nan? Is nan and NaN same? – Virendar Jun 08 '20 at 10:32
They are the same, just different representations. – Nev1111 Jun 08 '20 at 11:53
when I do df.to_excel, i see nan is also been written in the excel file but the cells with NaN values are blank..Can we not have the similar ways? – Virendar Jun 08 '20 at 12:08
See revised answer. – Nev1111 Jun 12 '20 at 20:05

How to ignore Nan while performing the comparison

2 Answers2