Convert incorrect filled missing values to NA in R data frame

One of the most important components of a data management strategy is the proper handling of missing values. Often you will find datasets that have used either zero or NA to represent missing values in cell of a data frame or table. The question that may sober our mind is that, which of the two way of storing missing value in the data is right? You will notice that everyone who deals with data has to deal with this important distinction. And far too often people get it wrong.

Given the prevalence of PC’s in the government and business world, most people have Excel on their desktop and many small datasets are generated in Excel and disseminated as Excel files. Spreadsheet software like Microsoft Excel (Kraus 2014) and OpenOffice Calc (Oualline and Oualline 2018) is ubiquitous.

One of the problems in using spreadsheet software to work with data is that the default data validation settings on these tools are too forgiving. By default, data entered into a cell can be of any type — string, float, decimal, date, etc. Though these tools offers features to properly encode the “missing value”, data entry in spreadsheet tend to ignore them.

Unlike spreadsheet, R data stored in data frame are type specific and you can not mix them in one variable [R Core Team (2018). R store missing values by the symbol NA (not available). Impossible values (e.g., dividing by zero) are represented by the symbol NaN (not a number). To those accustomed to working with missing values in spreadsheet software they find hard to handle data in R and handles missing values in R to them may require a shift in thinking.

On this post, I will present a case in which you have received a data with zero as missing values and how you can convert to NA—a standard form for missing value in R. Let’s first load the package we will use for this post.

require(tidyverse)

The tidyverse is an ecosystem of packages for data import, manipulate, model, visualize and share. For the purpose of learning, we first create a random sea surface temperature for three sites recorded for thirty days in March 2020. Table 1 shows a sample of temperature records in the three sites for four consecutive days generated in the chunk below;

sst = tibble(data = rnorm(n = 90, mean = 28.5, sd = 1.5), 
              sites = rep(c("Pemba", "Zanzibar", "Mafia"), times = 30), 
              day = rep(seq(lubridate::dmy(010320),lubridate::dmy(300320), by = "day"), each = 3)) %>%
  select(day, everything())

Once we have the dataset, we can introduce the zero value in the temperature variable at 5, 9, 13, 18, 22, 27, 31, 36, 40, 45, 49, 54, 58, 63, 67, 72, 76, 81, 85, 90 index of the data frame.

sst[seq(5, 90, length.out = 20) %>% as.integer(),2] = 0

To have a clear view of the position of zero, I first converted the long form table of the sea surface temperature to wider form using the pivot_wider function from tidyr package (Wickham and Henry 2018). Table 2 shows the temperature in the wider format with four columns. The first column represent the data of temperature records and the second to the fourth columns represent the temperature records of the three channels. Take note of the position of zero at each channel

sst.wide = sst %>% 
  pivot_wider(names_from = sites, 
              values_from = data, 
              id_cols = day)

In short, table 2 show that there are several days with zero value. But we know that it is impossible to have zero sea surface temperature. This indicate a common problem that you will often face when you deal with data from different sources. The data found in the real world is that data is rarely clean and homogeneous. In particular, many interesting datasets will have some amount of missing values. To make matters even more complicated, different data sources may indicate missing data in different ways.

Thus, for us to work with this data, we need to convert the 0 entries in the three channels to NA. To do that, we use the mutate_at() function from dplyr (Wickham et al. 2018) to specify which variables we want to apply our mutating function to, and we use the if_else() function to specify what to replace the value with if the condition is true or false. The chunk below summarize how to convert the zero cell to NA and the result is presented in table 3

sst.clean = sst.wide %>% 
  mutate_at(vars(Pemba, Zanzibar, Mafia),
            
            function(.var){
              
              if_else(.var ==0, ## if the value is equal to zero
                      true = as.numeric(NA), ## replace it with NA
                      false = .var ## else leave the value as it is
                      )
              
                         }
            )

sst.wide %>%
  na_if(0)

# A tibble: 30 x 4
   day        Pemba Zanzibar Mafia
   <date>     <dbl>    <dbl> <dbl>
 1 2020-03-01  29.0     27.3  29.0
 2 2020-03-02  30.0     NA    29.9
 3 2020-03-03  27.5     26.4  NA  
 4 2020-03-04  29.8     31.0  29.4
 5 2020-03-05  NA       27.1  27.4
 6 2020-03-06  29.4     28.1  NA  
 7 2020-03-07  27.3     27.9  29.7
 8 2020-03-08  NA       27.4  28.3
 9 2020-03-09  28.7     29.3  NA  
10 2020-03-10  27.9     28.6  26.6
# ... with 20 more rows