[bug] join with unknown keys returns 0, should nan

[goldentom42]

On Ubuntu 16.04 LTS, datatable 0.8.0.dev71

Using the following code

L_dt = dt.Frame(
    {
        "A": [5, 6, 7, 9],
        "B": [7, 8, 9, 10],
    }
)

R_dt = dt.Frame(
    {
        "A": [5, 7],
        "B": [7, 9], 
        "yhat": [1,2]
    }
)
R_dt.key=["A", "B"]

L_dt[:,:,dt.join(R_dt)]

returns :

IMHO column yhat should not be valued for keys in L_dt that are not in X_dt.
And this is pandas standard behaviour.

[st-pasha]

This appears to be a display bug in Jupyter. If you tried the same code in console, it would work as expected:

>>> L_dt[:,:,dt.join(R_dt)]
     A   B  yhat
--  --  --  ----
 0   5   7     1
 1   6   8      
 2   7   9     2
 3   9  10      

[4 rows x 3 columns]

So, the problem is not with the data itself, but rather with how it gets rendered into an html table in Jupyter notebook.

[goldentom42]

Hi Pasha,

I still have a few issues and they don't seem to be display related:

I used the following code

import datatable as dt
import numpy as np

L_dt = dt.Frame(
    {
        "A": [5, 6, 7, 9],
        "B": [7, 8, 9, 10],
    }
)

R_dt = dt.Frame(
    {
        "A": [5, 7],
        "B": [7, 9], 
        "yhat": [1,2]
    }
)

R_dt.key=["A", "B"]

join_res = L_dt[:,:,dt.join(R_dt)]

print(join_res)

yhat_dt = join_res[:, "yhat"]
yhat_np = join_res[:, "yhat"].to_numpy()[:, 0]

print(yhat_dt)
print(yhat_np)
print(join_res.to_pandas())
print(yhat_dt.to_pandas())

print("yhat_dt using .replace(None, 15)")
yhat_dt.replace(None, [15])
print(yhat_dt)

print("replace in yhat_np using np.isnan")
yhat_np[np.isnan(yhat_np)] = 15
print(yhat_np)

print("replace in yhat_np using ==None")
yhat_np[yhat_np==None] = 15
print(yhat_np)

The code above outputs :

     A   B  yhat
--  --  --  ----
 0   5   7     1
 1   6   8      
 2   7   9     2
 3   9  10      

[4 rows x 3 columns]

    yhat
--  ----
 0     1
 1      
 2     2
 3      

[4 rows x 1 column]

[1 -- 2 --]
   A   B  yhat
0  5   7   1.0
1  6   8   NaN
2  7   9   2.0
3  9  10   NaN
   yhat
0   1.0
1   NaN
2   2.0
3   NaN
yhat_dt using .replace(None, 15)
    yhat
--  ----
 0     1
 1    15
 2     2
 3    15

[4 rows x 1 column]

replace in yhat_np using np.isnan
[1 -- 2 --]
replace in yhat_np using ==None
[1 -- 2 --]

So it seems that to_pandas behaves correctly (in fact None in pandas are automatically replaced by NaNs) but the to_numpy does not seem to output NaN nor None.

What do you think ?

If you believe the above is fixed with #1874, will this get automatically pushed to DAI ?
So that I can use it to remove pandas code where possible.

Thanks, Olivier

[st-pasha]

Hi Olivier,
What you are observing is the expected behavior. Let's start with the datatable frame join_res. Its last column, yhat, was created in such a way that missing values were produced. This is fine, since datatable supports missing values in columns of any types, similar to how in SQL any cell can contain the value NULL.

When we transform such frame into various other formats, the results will vary according to the capabilities of the target format. For example, casting yhat_dt.to_python() will result in a list [1, None, 2, None], because we use python Nones to represent missing values when converting into python. The Nones are used regardless of the column type.

However, pandas does not support missing values. There is limited support for floating columns, where a floating nan value can be used to represent NAs. Because of this, pandas tends to convert integer columns with missing values into floating columns.

In numpy the situation is better: numpy supports arbitrary missing values via masked arrays. Thus, converting yhat_dt into numpy produces a masked_array object, with the 2nd and the 4th elements masked: [1 -- 2 --]. In order to replace missings in a masked array used .filled() method: yhat_np.filled(15) => array([ 1, 15, 2, 15], dtype=int8).