set missing values for multiple labelled variables

Question

How to I set missing values for multiple labelled vectors in a data frame. I am working with a survey dataset from spss. I am dealing with about 20 different variables, with the same missing values. So would like to find a way to use lapply() to make this work, but I can't.

I actually can do this with base R via as.numeric() and then recode() but I'm intrigued by the possibilities of haven and the labelled class so I'd like to find a way to do this all in Hadley's tidyverse

Roughly the variables of interest look like this. I am sorry if this is a basic question, but I find the help documentaiton associated with the haven and labelled packages just very unhelpful.

library(haven)
library(labelled)
v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v3<-data.frame(v1=v1, v2=v2)
lapply(v3, val_labels)
lapply(v3, function(x) set_na_values(x, c(5,6)))

Answer 1

Ok, I think I understand now what you trying to do...

i.e. Mark the labels, and the values as NA without removing the underlying imported data...

See addendum for a more detailed example that uses a public data file to show an example that harnesses dplyr to update multiple columns, labels...

Proposed Solution

df <- data_frame(s1 = c(1,2,2,2,5,6), s2 = c(1,2,2,2,5,6)) %>%
  set_value_labels(s1 = c(agree=1, disagree=2, dk=5, refused=6), 
                   s2 = c(agree=1, disagree=2, dk = tagged_na("5"), refused = tagged_na("6"))) %>%
  set_na_values(s2 = c(5,6))


val_labels(df)
is.na(df$s1)
is.na(df$s2)
df

---

Solution Result:

> library(haven)
> library(labelled)
> library(dplyr)
> df <- data_frame(s1 = c(1,2,2,2,5,6), s2 = c(1,2,2,2,5,6)) %>%
+   set_value_labels(s1 = c(agree=1, disagree=2, dk=5, refused=6), 
+                    s2 = c(agree=1, disagree=2, dk = tagged_na("5"), refused = tagged_na("6"))) %>%
+   set_na_values(s2 = c(5,6))
> val_labels(df)
$s1
   agree disagree       dk  refused 
       1        2        5        6 

$s2
   agree disagree       dk  refused 
       1        2       NA       NA 

> is.na(df$s1)
[1] FALSE FALSE FALSE FALSE FALSE FALSE
> is.na(df$s2)
[1] FALSE FALSE FALSE FALSE  TRUE  TRUE
> df
# A tibble: 6 × 2
         s1        s2
  <dbl+lbl> <dbl+lbl>
1         1         1
2         2         2
3         2         2
4         2         2
5         5         5
6         6         6

Now we can manipulate the data

mean(df$s1, na.rm = TRUE)
mean(df$s2, na.rm = TRUE)

> mean(df$s1, na.rm = TRUE)
[1] 3
> mean(df$s2, na.rm = TRUE)
[1] 1.75

Use Labelled package to remove labels and replace with R NA

If you wish to strip the labels and replace with R NA values you can use remove_labels(x, user_na_to_na = TRUE)

Example:

df <- remove_labels(df, user_na_to_na = TRUE)
df

Result:

> df <- remove_labels(df, user_na_to_na = TRUE) 
> df
# A tibble: 6 × 2
     s1    s2
  <dbl> <dbl>
1     1     1
2     2     2
3     2     2
4     2     2
5     5    NA
6     6    NA

--

Explanation / Overview of SPSS Format:

IBM SPSS (The application) can import and export data in many formats and in non-rectangular configurations; however, the data set is always translated to an SPSS rectangular data file, known as a system file (using the extension *.sav). Metadata (information about the data) such as variable formats, missing values, and variable and value labels are stored with the dataset.

Value Labels

Base R has one data type that effectively maintains a mapping between integers and character labels: the factor. This, however, is not the primary use of factors: they are instead designed to automatically generate useful contrasts for linear models. Factors differ from the labelled values provided by the other tools in important ways:

SPSS and SAS can label numeric and character values, not just integer values.

Missing Values

All three tools (SPSS, SAS, Stata) provide a global “system missing value” which is displayed as .. This is roughly equivalent to R’s NA, although neither Stata nor SAS propagate missingness in numeric comparisons: SAS treats the missing value as the smallest possible number (i.e. -inf), and Stata treats it as the largest possible number (i.e. inf).

Each tool also provides a mechanism for recording multiple types of missingness:

• Stata has “extended” missing values, .A through .Z.
• SAS has “special” missing values, .A through .Z plus ._.
• SPSS has per-column “user” missing values. Each column can declare up to three distinct values or a range of values (plus one distinct value) that should be treated as missing.

User Defined Missing Values

SPSS’s user-defined values work differently to SAS and Stata. Each column can have either up to three distinct values that are considered as missing or a range. Haven provides labelled_spss() as a subclass of labelled() to model these additional user-defined missings.

x1 <- labelled_spss(c(1:10, 99), c(Missing = 99), na_value = 99)
x2 <- labelled_spss(c(1:10, 99), c(Missing = 99), na_range = c(90, Inf))

x1
#> <Labelled SPSS double>
#>  [1]  1  2  3  4  5  6  7  8  9 10 99
#> Missing values: 99
#> 
#> Labels:
#>  value   label
#>     99 Missing
x2
#> <Labelled SPSS double>
#>  [1]  1  2  3  4  5  6  7  8  9 10 99
#> Missing range:  [90, Inf]
#> 
#> Labels:
#>  value   label
#>     99 Missing

Tagged missing values

To support Stata’s extended and SAS’s special missing value, haven implements a tagged NA. It does this by taking advantage of the internal structure of a floating point NA. That allows these values to behave identical to NA in regular R operations, while still preserving the value of the tag.

The R interface for creating with tagged NAs is a little clunky because generally they’ll be created by haven for you. But you can create your own with tagged_na():

Important:

Note these tagged NAs behave identically to regular NAs, even when printing. To see their tags, use print_tagged_na():

Thus:

    library(haven)
    library(labelled)
    v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
    v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=tagged_na("5"), refused= tagged_na("6")))
    v3<-data.frame(v1 = v1, v2 = v2)
    v3
    lapply(v3, val_labels)

---

> v3
  x x.1
1 1   1
2 2   2
3 2   2
4 2   2
5 5   5
6 6   6
> lapply(v3, val_labels)
$x
   agree disagree       dk  refused 
       1        2        5        6 

$x.1
   agree disagree       dk  refused 
       1        2       NA       NA 

Word of caution:

SPSS’s user-defined values work differently to SAS and Stata. Each column can have either up to three distinct values that are considered as missing, or a range. Haven provides labelled_spss() as a subclass of labelled() to model these additional user-defined missings.

I hope the above helps

Take care T.

References:

• https://cran.r-project.org/web/packages/haven/haven.pdf
• https://cran.r-project.org/web/packages/haven/vignettes/semantics.html
• https://www.spss-tutorials.com/spss-missing-values-tutorial/

Addendum Example using Public Data...

SPSS Missing Values Example using an SPPS Data file {hospital.sav}

Firstly, let's make sure we highlight that

• System missing values - are values that are completely absent from the data
• User missing values are values that are present in the data but must be excluded from calculations.

SPSS View of Data...

Let's review the image and the data... The SPSS data shown in the variable view shows that each row has a Label [Column5], we note that rows 10 through 14 have specific values attributed to them [1..6] [Column 6] that have name attributes and that no values have been specified as Missing [Column 7].

Now let's look at the SPSS data view:

Here we can note that there is missing data... (See hilighted "."'s). The key point is that we have Missing data, but currently have no "Missing User Values"

Now let's turn to R, and load the data into R

hospital_url <- "https://www.spss-tutorials.com/downloads/hospital.sav"
hospital <- read_sav(hospital_url, 
                     user_na = FALSE)
head(hospital,5)

# We're interested in columns 10 through 14...
head(hospital[10:14],5)

Result

> hospital_url <- "https://www.spss-tutorials.com/downloads/hospital.sav"
> hospital <- read_sav(hospital_url, 
+                      user_na = FALSE)
> head(hospital,5)
# A tibble: 5 × 14
  visit_id patient_id first_name surname_prefix last_name    gender entry_date entry_time
     <dbl>      <dbl>      <chr>          <chr>     <chr> <dbl+lbl>     <date>     <time>
1    32943      23176    JEFFREY                 DIJKSTRA         1 2013-01-08   16:56:10
2    32944      20754       MARK        VAN DER      BERG         1 2013-02-01   14:24:45
3    32945      25419     WILLEM                VERMEULEN         1 2013-02-02   10:01:43
4    32946      21139      LINDA                  JANSSEN         0 2013-02-10   10:24:39
5    32947      25419     WILLEM                VERMEULEN         1 2013-02-10   18:05:59
# ... with 6 more variables: exit_moment <dttm>, doctor_rating <dbl+lbl>, nurse_rating <dbl+lbl>,
#   room_rating <dbl+lbl>, food_rating <dbl+lbl>, facilities_rating <dbl+lbl>

Columns 10 through 14 contain Values

1="Very Dissatisfied"
2="Dissatisfied"
3="Neutral"
4="Satisfied"
5="Very Satisfied"
6="Not applicable or don't want to answer"

thus:

> head(hospital[10:14],5)
# A tibble: 5 × 5
  doctor_rating nurse_rating room_rating food_rating facilities_rating
      <dbl+lbl>    <dbl+lbl>   <dbl+lbl>   <dbl+lbl>         <dbl+lbl>
1             5            5           4           2                 3
2             4            5           4           3                 3
3             5            6           4           5                 4
4             4            5           5           4                 4
5             5            5           6           6                 6

SPSS Value Labels

> lapply(hospital[10], val_labels)
$doctor_rating
                     Very dissatisfied                           Dissatisfied 
                                     1                                      2 
                               Neutral                              Satisfied 
                                     3                                      4 
                        Very satisfied Not applicable or don't want to answer 
                                     5                                      6

ok, note that above we can confirm we have imported the Value Labels.

Remove Non-Applicable data from the survey data

Our goal is to now remove the "Not applicable or don't want to answer" data entries by setting them to be "User NA values" i.e. An SPSS missing value.

Solution - Step 1 - A Single Column

We wish to set the missing value attribute across multiple columns in the data... Let first do this for one column...

Note we use add_value_labels not set_value_labels as we wish to append a new label, not completely overwrite existing labels...

d <- hospital
mean(d$doctor_rating, na.rm = TRUE)

d <- hospital %>% 
  add_value_labels( doctor_rating = c( "Not applicable or don't want to answer" 
                                       = tagged_na("6") )) %>%
  set_na_values(doctor_rating = 5)

val_labels(d$doctor_rating)
mean(d$doctor_rating, na.rm = TRUE)

---

> d <- hospital
> mean(d$doctor_rating, na.rm = TRUE)
[1] 4.322368
> d <- hospital %>% 
+   add_value_labels( doctor_rating = c( "Not applicable or don't want to answer" 
+                                        = tagged_na("6") )) %>%
+   set_na_values(doctor_rating = 6)
> val_labels(d$doctor_rating)
                     Very dissatisfied                           Dissatisfied 
                                     1                                      2 
                               Neutral                              Satisfied 
                                     3                                      4 
                        Very satisfied Not applicable or don't want to answer 
                                     5                                      6 
Not applicable or don't want to answer 
                                    NA 
> mean(d$doctor_rating, na.rm = TRUE)
[1] 4.097015

Solution - Step 2 - Now apply to multiple columns...

mean(hospital$nurse_rating)
mean(hospital$nurse_rating, na.rm = TRUE)
d <- hospital %>% 
  add_value_labels( doctor_rating = c( "Not applicable or don't want to answer" 
                                       = tagged_na("6") )) %>%
  set_na_values(doctor_rating = 6) %>%
  add_value_labels( nurse_rating = c( "Not applicable or don't want to answer" 
                                     = tagged_na("6") )) %>%
  set_na_values(nurse_rating = 6)
mean(d$nurse_rating, na.rm = TRUE)

Result

Note that nurse_rating contains "NaN" values and NA tagged values. The first mean() call fails, the second succeeds but includes "Not Applicable..." after the filter the "Not Applicable..." are removed...

> mean(hospital$nurse_rating)
[1] NaN
> mean(hospital$nurse_rating, na.rm = TRUE)
[1] 4.471429
> d <- hospital %>% 
+   add_value_labels( doctor_rating = c( "Not applicable or don't want to answer" 
+                                        = tagged_na("6") )) %>%
+   set_na_values(doctor_rating = 6) %>%
+   add_value_labels( nurse_rating = c( "Not applicable or don't want to answer" 
+                                      = tagged_na("6") )) %>%
+   set_na_values(nurse_rating = 6)
> mean(d$nurse_rating, na.rm = TRUE)
[1] 4.341085

Convert tagged NA to R NA

Here we take the above tagged NA and convert to R NA values.

d <- d %>% remove_labels(user_na_to_na = TRUE)

Answer 2

Not quite sure if this is what you are looking for:

v1 <- labelled(c(1, 2, 2, 2, 5, 6), c(agree = 1, disagree = 2, dk = 5, refused = 6))
v2 <- labelled(c(1, 2, 2, 2, 5, 6), c(agree = 1, disagree = 2, dk = 5, refused = 6))
v3 <- data_frame(v1 = v1, v2 = v2)

lapply(names(v3), FUN = function(x) {
  na_values(v3[[x]]) <<- 5:6
})

lapply(v3, na_values)

The last line returning

$v1
[1] 5 6

$v2
[1] 5 6

Verify missing values:

is.na(v3$v1)
[1] FALSE FALSE FALSE FALSE  TRUE  TRUE

Answer 3

Is this correct?

#Using replace to substitute 5 and 6 in v3 with NA
data.frame(lapply(v3, function(a) replace(x = a, list = a %in% c(5,6), values = NA)))
#   x x.1
#1  1   1
#2  2   2
#3  2   2
#4  2   2
#5 NA  NA
#6 NA  NA

I know labelled_spss allows you to specify na_range or even a vector of na_values

#DATA
v11 = labelled_spss(x = c(1,2,2,2,5,6),
                    labels = c(agree=1, disagree=2, dk=5, refused=6),
                    na_range = 5:6)

#Check if v11 has NA values
is.na(v11)
#[1] FALSE FALSE FALSE FALSE  TRUE  TRUE

v22 = labelled_spss(x = c(1,2,2,2,5,6),
                    labels = c(agree=1, disagree=2, dk=5, refused=6),
                    na_range = 5:6)

#Put v11 and v22 in a list
v33 = list(v11, v22)

#Use replace like above
data.frame(lapply(X = v33, FUN = function(a) replace(x = a, list = is.na(a), values = NA)))
#   x x.1
#1  1   1
#2  2   2
#3  2   2
#4  2   2
#5 NA  NA
#6 NA  NA

Answer 4

Defining SPSS-style user-defined missing values

Main functions

The two main functions in labelled package for manipulating SPSS style user-defined missing values are na_values and na_range.

library(labelled)
v1 <-c(1,2,2,2,5,6)
val_labels(v1) <- c(agree=1, disagree=2, dk=5, refused=6)
na_values(v1) <- 5:6
v1

<Labelled SPSS double>
[1] 1 2 2 2 5 6
Missing values: 5, 6

Labels:
 value    label
     1    agree
     2 disagree
     5       dk
     6  refused

set_* functions

The set_* functions in labelled are intended to be used with magrittr / dplyr.

library(dplyr)
d <- tibble(v1 = c(1, 2, 2, 2, 5, 6), v2 = c(1:3, 1:3))
d <- d %>%
  set_value_labels(v1 = c(agree=1, disagree=2, dk=5, refused=6)) %>%
  set_na_values(v1 = 5:6)
d$v1

<Labelled SPSS double>
[1] 1 2 2 2 5 6
Missing values: 5, 6

Labels:
 value    label
     1    agree
     2 disagree
     5       dk
     6  refused

What are user-defined missing values?

User-defined missing values are just and only meta-information. It doesn't change the values in a vector. This is simply a way to say to the user that these values could/should be considered in some context as missing values. It means that if you compute something (e.g. mean) from your vector, these values will still be taken into account.

mean(v1)
[1] 3

You can easily convert user-defined missing values to proper NA with user_na_to_na.

mean(user_na_to_na(v1), na.rm = TRUE)
[1] 1.75

There are very few functions that would take into account these meta-information. See for example the freq function from questionr package.

library(questionr)
freq(v1)
             n    % val%
[1] agree    1 16.7   25
[2] disagree 3 50.0   75
[5] dk       1 16.7   NA
[6] refused  1 16.7   NA
NA           0  0.0   NA

What is the difference with tagged NAs ?

The purpose of tagged NAs, introduced by haven, is to reproduce the way Stata is managing missing values. All tagged NAs are internally considered as NA by R.

Answer 5

The first argument to set_na_values is a data frame, not a vector/column, which is why your lapply command doesn't work. You could build a list of the arguments for set_na_values for an arbitrary number of columns in your data frame and then call it with do.call as below...

v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v3<-data.frame(v1=v1, v2=v2)
na_values(v3)

args <- c(list(.data = v3), setNames(lapply(names(v3), function(x) c(5,6)), names(v3)))
v3 <- do.call(set_na_values, args)
na_values(v3)

Update: You can also use the assignment form of the na_values function within an lapply statement, since it accepts a vector as it's first argument instead of a data frame like set_na_values...

library(haven)
library(labelled)
v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v3<-data.frame(v1=v1, v2=v2)
lapply(v3, val_labels)
na_values(v3)

v3[] <- lapply(v3, function(x) `na_values<-`(x, c(5,6)))
na_values(v3)

or even use the normal version of na_values in the lapply command, just making sure to return the 'fixed' vector...

library(haven)
library(labelled)
v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v3<-data.frame(v1=v1, v2=v2)
lapply(v3, val_labels)
na_values(v3)

v3[] <- lapply(v3, function(x) { na_values(x) <- c(5,6); x } )
na_values(v3)

and that idea can be used inside of a dplyr chain as well, either applying to all variables, or applying to whatever columns are selected using dplyr's selection tools...

library(haven)
library(labelled)
library(dplyr)
v1<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v2<-labelled(c(1,2,2,2,5,6), c(agree=1, disagree=2, dk=5, refused=6))
v3<-data.frame(v1=v1, v2=v2)
lapply(v3, val_labels)
na_values(v3)

v4 <- v3 %>% mutate_all(funs(`na_values<-`(., c(5,6))))
na_values(v4)

v5 <- v3 %>% mutate_each(funs(`na_values<-`(., c(5,6))), x)
na_values(v5)

Answer 6

You could use a very simple solution in using base R:

v3[v3 == 5 ] <- NA
v3[v3 == 6 ] <- NA

But if you're looking for a really fast solution, you can use a data.table approach.

library(data.table)

setDT(v3)

for(j in seq_along(v3)) { 
            set(v3, i=which(v3[[j]] %in% c(5,6)), j=j, value=NA) 
            }