Working with missing data

In this section, we will discuss missing (also referred to as NA) values in cudf. cudf supports having missing values in all dtypes. These missing values are represented by <NA>. These values are also referenced as “null values”.

  1. How to Detect missing values

  2. Float dtypes and missing data

  3. Datetimes

  4. Calculations with missing data

  5. Sum/product of Null/nans

  6. NA values in GroupBy

  7. Inserting missing data

  8. Filling missing values: fillna

  9. Filling with cudf Object

  10. Dropping axis labels with missing data: dropna

  11. Replacing generic values

  12. String/regular expression replacement

  13. Numeric replacement

How to Detect missing values

To detect missing values, you can use isna() and notna() functions.

[1]:
import cudf
import numpy as np
[2]:
df = cudf.DataFrame({'a': [1, 2, None, 4], 'b':[0.1, None, 2.3, 17.17]})
[3]:
df
[3]:
a b
0 1 0.1
1 2 <NA>
2 <NA> 2.3
3 4 17.17
[4]:
df.isna()
[4]:
a b
0 False False
1 False True
2 True False
3 False False
[5]:
df['a'].notna()
[5]:
0     True
1     True
2    False
3     True
Name: a, dtype: bool

One has to be mindful that in Python (and NumPy), the nan’s don’t compare equal, but None’s do. Note that cudf/NumPy uses the fact that np.nan != np.nan, and treats None like np.nan.

[6]:
None == None
[6]:
True
[7]:
np.nan == np.nan
[7]:
False

So as compared to above, a scalar equality comparison versus a None/np.nan doesn’t provide useful information.

[8]:
df['b'] == np.nan
[8]:
0    False
1    False
2    False
3    False
Name: b, dtype: bool
[9]:
s = cudf.Series([None, 1, 2])
[10]:
s
[10]:
0    <NA>
1       1
2       2
dtype: int64
[11]:
s == None
[11]:
0    False
1    False
2    False
dtype: bool
[12]:
s = cudf.Series([1, 2, np.nan], nan_as_null=False)
[13]:
s
[13]:
0    1.0
1    2.0
2    NaN
dtype: float64
[14]:
s == np.nan
[14]:
0    False
1    False
2    False
dtype: bool

Float dtypes and missing data

Because NaN is a float, a column of integers with even one missing values is cast to floating-point dtype. However this doesn’t happen by default.

By default if a NaN value is passed to Series constructor, it is treated as <NA> value.

[15]:
cudf.Series([1, 2, np.nan])
[15]:
0       1
1       2
2    <NA>
dtype: int64

Hence to consider a NaN as NaN you will have to pass nan_as_null=False parameter into Series constructor.

[16]:
cudf.Series([1, 2, np.nan], nan_as_null=False)
[16]:
0    1.0
1    2.0
2    NaN
dtype: float64

Datetimes

For datetime64 types, cudf doesn’t support having NaT values. Instead these values which are specific to numpy and pandas are considered as null values(<NA>) in cudf. The actual underlying value of NaT is min(int64) and cudf retains the underlying value when converting a cudf object to pandas object.

[17]:
import pandas as pd
datetime_series = cudf.Series([pd.Timestamp("20120101"), pd.NaT, pd.Timestamp("20120101")])
datetime_series
[17]:
0    2012-01-01 00:00:00.000000
1                          <NA>
2    2012-01-01 00:00:00.000000
dtype: datetime64[us]
[18]:
datetime_series.to_pandas()
[18]:
0   2012-01-01
1          NaT
2   2012-01-01
dtype: datetime64[ns]

any operations on rows having <NA> values in datetime column will result in <NA> value at the same location in resulting column:

[19]:
datetime_series - datetime_series
[19]:
0    0 days 00:00:00
1               <NA>
2    0 days 00:00:00
dtype: timedelta64[us]

Calculations with missing data

Null values propagate naturally through arithmetic operations between pandas objects.

[20]:
df1 = cudf.DataFrame({'a':[1, None, 2, 3, None], 'b':cudf.Series([np.nan, 2, 3.2, 0.1, 1], nan_as_null=False)})
[21]:
df2 = cudf.DataFrame({'a':[1, 11, 2, 34, 10], 'b':cudf.Series([0.23, 22, 3.2, None, 1])})
[22]:
df1
[22]:
a b
0 1 NaN
1 <NA> 2.0
2 2 3.2
3 3 0.1
4 <NA> 1.0
[23]:
df2
[23]:
a b
0 1 0.23
1 11 22.0
2 2 3.2
3 34 <NA>
4 10 1.0
[24]:
df1 + df2
[24]:
a b
0 2 NaN
1 <NA> 24.0
2 4 6.4
3 37 <NA>
4 <NA> 2.0

While summing the data along a series, NA values will be treated as 0.

[25]:
df1['a']
[25]:
0       1
1    <NA>
2       2
3       3
4    <NA>
Name: a, dtype: int64
[26]:
df1['a'].sum()
[26]:
6

Since NA values are treated as 0, the mean would result to 2 in this case (1 + 0 + 2 + 3 + 0)/5 = 2

[27]:
df1['a'].mean()
[27]:
2.0

To preserve NA values in the above calculations, sum & mean support skipna parameter. By default it’s value is set to True, we can change it to False to preserve NA values.

[28]:
df1['a'].sum(skipna=False)
[28]:
nan
[29]:
df1['a'].mean(skipna=False)
[29]:
nan

Cumulative methods like cumsum and cumprod ignore NA values by default.

[30]:
df1['a'].cumsum()
[30]:
0       1
1    <NA>
2       3
3       6
4    <NA>
Name: a, dtype: int64

To preserve NA values in cumulative methods, provide skipna=False.

[31]:
df1['a'].cumsum(skipna=False)
[31]:
0       1
1    <NA>
2    <NA>
3    <NA>
4    <NA>
Name: a, dtype: int64

Sum/product of Null/nans

The sum of an empty or all-NA Series of a DataFrame is 0.

[32]:
cudf.Series([np.nan], nan_as_null=False).sum()
[32]:
0.0
[33]:
cudf.Series([np.nan], nan_as_null=False).sum(skipna=False)
[33]:
nan
[34]:
cudf.Series([], dtype='float64').sum()
[34]:
0.0

The product of an empty or all-NA Series of a DataFrame is 1.

[35]:
cudf.Series([np.nan], nan_as_null=False).prod()
[35]:
1.0
[36]:
cudf.Series([np.nan], nan_as_null=False).prod(skipna=False)
[36]:
nan
[37]:
cudf.Series([], dtype='float64').prod()
[37]:
1.0

NA values in GroupBy

NA groups in GroupBy are automatically excluded. For example:

[38]:
df1
[38]:
a b
0 1 NaN
1 <NA> 2.0
2 2 3.2
3 3 0.1
4 <NA> 1.0
[39]:
df1.groupby('a').mean()
[39]:
b
a
2 3.2
1 NaN
3 0.1

It is also possible to include NA in groups by passing dropna=False

[40]:
df1.groupby('a', dropna=False).mean()
[40]:
b
a
2 3.2
1 NaN
3 0.1
<NA> 1.5

Inserting missing data

All dtypes support insertion of missing value by assignment. Any specific location in series can made null by assigning it to None.

[41]:
series = cudf.Series([1, 2, 3, 4])
[42]:
series
[42]:
0    1
1    2
2    3
3    4
dtype: int64
[43]:
series[2] = None
[44]:
series
[44]:
0       1
1       2
2    <NA>
3       4
dtype: int64

Filling missing values: fillna

fillna() can fill in NA & NaN values with non-NA data.

[45]:
df1
[45]:
a b
0 1 NaN
1 <NA> 2.0
2 2 3.2
3 3 0.1
4 <NA> 1.0
[46]:
df1['b'].fillna(10)
[46]:
0    10.0
1     2.0
2     3.2
3     0.1
4     1.0
Name: b, dtype: float64

Filling with cudf Object

You can also fillna using a dict or Series that is alignable. The labels of the dict or index of the Series must match the columns of the frame you wish to fill. The use case of this is to fill a DataFrame with the mean of that column.

[47]:
import cupy as cp
dff = cudf.DataFrame(cp.random.randn(10, 3), columns=list('ABC'))
[48]:
dff.iloc[3:5, 0] = np.nan
[49]:
dff.iloc[4:6, 1] = np.nan
[50]:
dff.iloc[5:8, 2] = np.nan
[51]:
dff
[51]:
A B C
0 0.771245 0.051024 1.199239
1 -1.168041 0.702664 -0.270806
2 -1.467009 -0.143080 -0.806151
3 NaN -0.610798 -0.272895
4 NaN NaN 1.396784
5 -0.439343 NaN NaN
6 1.093102 -0.764758 NaN
7 0.003098 -0.722648 NaN
8 -0.095899 -1.285156 -0.300566
9 0.109465 2.497843 -1.199856
[52]:
dff.fillna(dff.mean())
[52]:
A B C
0 0.771245 0.051024 1.199239
1 -1.168041 0.702664 -0.270806
2 -1.467009 -0.143080 -0.806151
3 -0.149173 -0.610798 -0.272895
4 -0.149173 -0.034364 1.396784
5 -0.439343 -0.034364 -0.036322
6 1.093102 -0.764758 -0.036322
7 0.003098 -0.722648 -0.036322
8 -0.095899 -1.285156 -0.300566
9 0.109465 2.497843 -1.199856
[53]:
dff.fillna(dff.mean()[1:3])
[53]:
A B C
0 0.771245 0.051024 1.199239
1 -1.168041 0.702664 -0.270806
2 -1.467009 -0.143080 -0.806151
3 NaN -0.610798 -0.272895
4 NaN -0.034364 1.396784
5 -0.439343 -0.034364 -0.036322
6 1.093102 -0.764758 -0.036322
7 0.003098 -0.722648 -0.036322
8 -0.095899 -1.285156 -0.300566
9 0.109465 2.497843 -1.199856

Dropping axis labels with missing data: dropna

Missing data can be excluded using dropna():

[54]:
df1
[54]:
a b
0 1 NaN
1 <NA> 2.0
2 2 3.2
3 3 0.1
4 <NA> 1.0
[55]:
df1.dropna(axis=0)
[55]:
a b
2 2 3.2
3 3 0.1
[56]:
df1.dropna(axis=1)
[56]:
0
1
2
3
4

An equivalent dropna() is available for Series.

[57]:
df1['a'].dropna()
[57]:
0    1
2    2
3    3
Name: a, dtype: int64

Replacing generic values

Often times we want to replace arbitrary values with other values.

replace() in Series and replace() in DataFrame provides an efficient yet flexible way to perform such replacements.

[58]:
series = cudf.Series([0.0, 1.0, 2.0, 3.0, 4.0])
[59]:
series
[59]:
0    0.0
1    1.0
2    2.0
3    3.0
4    4.0
dtype: float64
[60]:
series.replace(0, 5)
[60]:
0    5.0
1    1.0
2    2.0
3    3.0
4    4.0
dtype: float64

We can also replace any value with a <NA> value.

[61]:
series.replace(0, None)
[61]:
0    <NA>
1     1.0
2     2.0
3     3.0
4     4.0
dtype: float64

You can replace a list of values by a list of other values:

[62]:
series.replace([0, 1, 2, 3, 4], [4, 3, 2, 1, 0])
[62]:
0    4.0
1    3.0
2    2.0
3    1.0
4    0.0
dtype: float64

You can also specify a mapping dict:

[63]:
series.replace({0: 10, 1: 100})
[63]:
0     10.0
1    100.0
2      2.0
3      3.0
4      4.0
dtype: float64

For a DataFrame, you can specify individual values by column:

[64]:
df = cudf.DataFrame({"a": [0, 1, 2, 3, 4], "b": [5, 6, 7, 8, 9]})
[65]:
df
[65]:
a b
0 0 5
1 1 6
2 2 7
3 3 8
4 4 9
[66]:
df.replace({"a": 0, "b": 5}, 100)
[66]:
a b
0 100 100
1 1 6
2 2 7
3 3 8
4 4 9

String/regular expression replacement

cudf supports replacing string values using replace API:

[67]:
d = {"a": list(range(4)), "b": list("ab.."), "c": ["a", "b", None, "d"]}
[68]:
df = cudf.DataFrame(d)
[69]:
df
[69]:
a b c
0 0 a a
1 1 b b
2 2 . <NA>
3 3 . d
[70]:
df.replace(".", "A Dot")
[70]:
a b c
0 0 a a
1 1 b b
2 2 A Dot <NA>
3 3 A Dot d
[71]:
df.replace([".", "b"], ["A Dot", None])
[71]:
a b c
0 0 a a
1 1 <NA> <NA>
2 2 A Dot <NA>
3 3 A Dot d

Replace a few different values (list -> list):

[72]:
df.replace(["a", "."], ["b", "--"])
[72]:
a b c
0 0 b b
1 1 b b
2 2 -- <NA>
3 3 -- d

Only search in column ‘b’ (dict -> dict):

[73]:
df.replace({"b": "."}, {"b": "replacement value"})
[73]:
a b c
0 0 a a
1 1 b b
2 2 replacement value <NA>
3 3 replacement value d

Numeric replacement

replace() can also be used similar to fillna().

[74]:
df = cudf.DataFrame(cp.random.randn(10, 2))
[75]:
df[np.random.rand(df.shape[0]) > 0.5] = 1.5
[76]:
df.replace(1.5, None)
[76]:
0 1
0 <NA> <NA>
1 <NA> <NA>
2 0.123160746 1.09464783
3 <NA> <NA>
4 <NA> <NA>
5 0.68137677 -0.357346253
6 <NA> <NA>
7 <NA> <NA>
8 1.173285961 -0.968616065
9 0.147922362 -0.154880098

Replacing more than one value is possible by passing a list.

[77]:
df00 = df.iloc[0, 0]
[78]:
df.replace([1.5, df00], [5, 10])
[78]:
0 1
0 5.000000 5.000000
1 5.000000 5.000000
2 0.123161 1.094648
3 5.000000 5.000000
4 5.000000 5.000000
5 0.681377 -0.357346
6 5.000000 5.000000
7 5.000000 5.000000
8 1.173286 -0.968616
9 0.147922 -0.154880

You can also operate on the DataFrame in place:

[79]:
df.replace(1.5, None, inplace=True)
[80]:
df
[80]:
0 1
0 <NA> <NA>
1 <NA> <NA>
2 0.123160746 1.09464783
3 <NA> <NA>
4 <NA> <NA>
5 0.68137677 -0.357346253
6 <NA> <NA>
7 <NA> <NA>
8 1.173285961 -0.968616065
9 0.147922362 -0.154880098