<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/r-null-values-null-na-nan-inf/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2018/07/04/r-null-values-null-na-nan-inf/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>R language supports several null-able values and it is relatively important to understand how these values behave, when making data pre-processing and data munging.</p>
<p>In general, R supports:</p>
<ul>
<li>NULL</li>
<li>NA</li>
<li>NaN</li>
<li>Inf / -Inf</li>
</ul>
<p><span>NULL</span> is an object and is returned when an expression or function results in an undefined value. In R language, NULL (capital letters) is a reserved word and can also be the product of importing data with unknown data type.</p>
<p><span>NA</span> is a logical constant of length 1 and is an indicator for a missing value.NA (capital letters) is a reserved word and can be coerced to any other data type vector (except raw) and can also be a product when importing data. NA and “NA” (as presented as string) are not interchangeable. NA stands for Not Available.</p>
<p><span>NaN</span> stands for Not A Number and is a logical vector of a length 1 and applies to numerical values, as well as real and imaginary parts of complex values, but not to values of integer vector. NaN is a reserved word.</p>
<p><span>Inf</span> and <span>-Inf</span> stands for infinity (or negative infinity) and is a result of storing  either a large number or a product that is a result of division by zero. Inf is a reserved word and is – in most cases – product of computations in R language and therefore very rarely a product of data import. Infinite also tells you that the value is not missing and a number!</p>
<p>All four null/missing data types have accompanying logical functions available in base R; returning the TRUE / FALSE for each of particular function: is.null(), is.na(), is.nan(), is.infinite().</p>
<p>General understanding of all values by simply using following code:</p>
<pre>#reading documentation on all data types:
?NULL
?NA
?NaN
?Inf

#populating variables
a &lt;- &quot;NA&quot;
b &lt;- &quot;NULL&quot;
c &lt;- NULL
d &lt;- NA
e &lt;- NaN
f &lt;- Inf

### Check if variables are same?
identical(a,d)
# [1] FALSE

# NA and NaN are not identical
identical(d,e)
# [1] FALSE

###checking length of data types
length(c)
# [1] 0
length(d)
# [1] 1
length(e)
# [1] 1
length(f)
# [1] 1

###checking data types
str(c); class(c); 
#NULL
#[1] &quot;NULL&quot;

str(d); class(d); 
#logi NA
#[1] &quot;logical&quot;
str(e); class(e); 
#num NaN
#[1] &quot;numeric&quot;
str(f); class(f); 
#num Inf
#[1] &quot;numeric&quot;</pre>
<h2>Getting data from R</h2>
<p>Nullable data types can have a different behavior when propagated to e.g.: list or or vectors or data.frame types.</p>
<p>We can test this by creating NULL or NA or NaN vectors and dataframes and observe the behaviour:</p>
<pre>#empty vectors for NULL, NA and NaN
v1 &lt;- c(NULL, NULL, NULL)
v2 &lt;- NULL

str(v1); class(v1); mode(v1)
str(v2); class(v2); mode(v2)

v3 &lt;- c(NA, NA, NA)
v4 &lt;- NA

str(v3); class(v3); mode(v3)
str(v4); class(v4); mode(v4)

v5 &lt;- c(NaN, NaN, NaN)
v6 &lt;- NaN

str(v5); class(v5); mode(v5)
str(v6); class(v6); mode(v6)</pre>
<p>Clearly, it is evident that the NULL vector will always be an empty one, regardless of the elements it can hold. With NA and NaN, it will be the length of the elements it holds, with a slight difference, that NA will be a vector of class Logical, whereas NaN will be a vector of class numeric.</p>
<p>NULL vector will not change the size but class when combined with a mathematical operation:</p>
<pre>#operation on NULL Vector
v1 &lt;- c(NULL, NULL, NULL)
str(v1)
# NULL

v1 &lt;- v1+1
str(v1)
# num(0)</pre>
<p>This will only change the class but not the length and still any of the data will not persist in the vector.</p>
<p>With data.frames it is relatively the same behavior.</p>
<pre>#empty data.frame
df1 &lt;- data.frame(v1=NA,v2=NA, v3=NA)
df2 &lt;- data.frame(v1=NULL, v2=NULL, v3=NULL)
df3 &lt;- data.frame(v1=NaN, v2=NaN, V3=NaN)

str(df1); str(df2);str(df3)</pre>
<p>Dataframe consisting of NULL values for each of the column will presented as dataframe with 0 observations and 0 variables (0 columns and 0 rows). Dataframe with NA and NaN will be of 1 observation and 3 variables, of logical data type and of numerical data type, respectively.</p>
<p>When adding new observations to data frames, different behavior when dealing with NULL, NA or NaN.</p>
<p>Adding to “NA” data.frame:</p>
<pre># adding new rows to existing dataframe
df1 &lt;- rbind(df1, data.frame(v1=1, v2=2,v3=3))

#explore data.frame
df1</pre>
<p>it is clear that new row is added, and when adding a new row (vector) of different size, it will generate error, since the dataframe definitions holds the dimensions. Same behavior is expected when dealing with NaN value. On the other hand, different results when using NULL values:</p>
<pre>#df2 will get the dimension definition
df2 &lt;- rbind(df2, data.frame(v1=1, v2=2))

#this will generate error since the dimension definition is set
df2 &lt;- rbind(df2, data.frame(v1=1, v2=NULL))

#and with NA should be fine
df2 &lt;- rbind(df2, data.frame(v1=1, v2=NA))</pre>
<p>with first assignment, the df2 will get the dimension definition, albeit the first construction of df2 was a nullable vector with three elements.</p>
<p>NULLable is also a result when we are looking in the vector element that is not existent, due to the fact that is out of boundaries:</p>
<pre>l &lt;- list(a=1:10, b=c(&quot;a&quot;,&quot;b&quot;,&quot;c&quot;), c=seq(0,10,0.5))
l$a
# <span>[1] 1 2 3 4 5 6 7 8 9 10
</span>l$c
#<span> [1] 0.0 0.5 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 6.5 7.0 7.5 8.0 8.5 9.0 9.5 10.0
</span>l$r
# <span>NULL</span></pre>
<p>we are calling the sublist r of list l, which is a NULL value, but is not missing or not existing, it is NULL, which in fact is rather contradictory, since the definition is not set. Different results (Not Available) would be returned when calling a vector element:</p>
<pre>v &lt;- c(1:3)
v[4]
#[1] NA</pre>
<p>Boundaries in list and in vector are defined differently for NA and NULL data types.</p>
<p> </p>
<h2>Getting data from SQL Server</h2>
<p>I will use several different data-types deriving from following SQL table.</p>
<pre>USE AzureLanding;
GO

CREATE TABLE R_Nullables
 (
 ID INT IDENTITY(1,1) NOT NULL
 ,num1 FLOAT
 ,num2 DECIMAL(20,10)
 ,num3 INT
 ,tex1 NVARCHAR(MAX)
 ,tex2 VARCHAR(100)
 ,bin1 VARBINARY(MAX)
 )

INSERT INTO R_Nullables
          SELECT 1.22, 21.535, 245, 'This is Nvarchar text','Varchar text',0x0342342
UNION ALL SELECT 3.4534, 25.5, 45, 'This another Nvarchar text','Varchar text 2',0x03423e3434tg
UNION ALL SELECT NULL, NULL, NULL, NULL,NULL,NULL
UNION ALL SELECT 0, 0, 0, '','',0x</pre>
<p>By using RODBC R library, data will be imported in R environment:</p>
<pre>library(RODBC)
SQLcon &lt;- odbcDriverConnect('driver={SQL Server};server=TOMAZK\\MSSQLSERVER2017;database=AzureLanding;trusted_connection=true')
# df &lt;- sqlQuery(SQLcon, &quot;SELECT * FROM R_Nullables&quot;)
df &lt;- sqlQuery(SQLcon, &quot;SELECT ID ,num1 ,num2 ,num3 ,tex1 ,tex2 FROM R_Nullables&quot;)
close(SQLcon)</pre>
<p>When SELECT * query is executed, the varbinary data type from SQL Server is represented as 2GiB binary object in R and most likely, you will receive an error, because R will not be able to allocate memory:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-04-23_36_12-rstudio.png?w=456" data-orig-size="518,46" data-medium-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-04-23_36_12-rstudio.png?w=456?w=300" data-attachment-id="5014" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2018/07/2018-07-04-23_36_12-rstudio.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2018/07/04/r-null-values-null-na-nan-inf/2018-07-04-23_36_12-rstudio/#main" data-large-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-04-23_36_12-rstudio.png?w=456?w=518" alt="2018-07-04 23_36_12-RStudio" data-image-title="2018-07-04 23_36_12-RStudio" sizes="(max-width: 518px) 100vw, 518px" data-comments-opened="1" class="wp-image-5014" data-recalc-dims="1"></p>
<p>After altering the columns, the df object will be created. The presentation is straight-forward, yet somehow puzzling:</p>
<pre><span> ID num1 num2 num3 tex1 tex2
1 1 1.2200 21.535 245 This is Nvarchar text Varchar text
2 2 3.4534 25.500 45 This another Nvarchar text Varchar text 2
3 3 NA NA NA </span></pre>