Merging Two Dataframes With Pd.na In Merge Column Yields 'typeerror: Boolean Value Of Na Is Ambiguous'

With Pandas 1.0.1, I'm unable to merge if the df = df.merge(df2, on=some_column) yields File /home/torstein/code/fintechdb/Sheets/sheets/gild.py, line 42, in gild df = df.mer

Solution 1:

This has to do with pd.NA being implemented in pandas 1.0.0 and how the pandas team decided it should work in a boolean context. Also, you take into account it is an experimental feature, hence it shouldn't be used for anything but experimenting:

Warning Experimental: the behaviour of pd.NA can still change without warning.

In another link of pandas documentation, where it covers working with missing values, is where I believe the reason and the answer you are looking for can be found:

NA in a boolean context: Since the actual value of an NA is unknown, it is ambiguous to convert NA to a boolean value. The following raises an error: TypeError: boolean value of NA is ambiguous

Furthermore, it provides a valuable piece of advise:

"This also means that pd.NA cannot be used in a context where it is evaluated to a boolean, such as if condition: ... where condition can potentially be pd.NA. In such cases, isna() can be used to check for pd.NA or condition being pd.NA can be avoided, for example by filling missing values beforehand."

Solution 2:

I decided that the pd.NA instances in my data were valid, and hence I needed to deal with them rather than filling them, like with fillna(). If you're like me in this case, then convert it from pd.NA to either True or False by simply using pd.isna(val). Only you can decide whether the null should come out T or F, but here's a simple example:

val = pd.NA
if pd.isna(val) :
    print('it is null')
else :
    print('it is not null')

returns: it is null

Then,

val = 7
if pd.isna(val) :
    print('it is null')
else :
    print('it is not null')

returns: it is not null

Hope this helps other trying to get a definitive course of action (Celius's answer is accurate, but I wanted to provide actionable code for those struggling with this).