set ... to na when using read_excel()

Question

I am trying to read in excel files which use three horizontal dots to represent missing values, for example...

https://population.un.org/wpp/Download/Files/1_Indicators%20(Standard)/EXCEL_FILES/2_Fertility/WPP2019_FERT_F02_SEX_RATIO_AT_BIRTH.xlsx

Is it possible to set these to NA using read_excel()? I have tried different options for the na argument (see below), none which seem to work

d0 <- read_excel(path = "WPP2019_FERT_F02_SEX_RATIO_AT_BIRTH.xlsx)", 
                 # na = "...", # does not work
                 # na = "…", # copying the output does not work
                 # na = "U+2026", # unicode character does not work  
                 sheet = 2, skip = 16)
d0
# # A tibble: 255 x 21
#    Index Variant `Region, subreg~ Notes `Country code` Type  `Parent code` `1950-1955`
#    <dbl> <chr>   <chr>            <chr>          <dbl> <chr>         <dbl> <chr>      
#  1     1 Estima~ WORLD            NA               900 World             0 1.06       
#  2     2 Estima~ UN development ~ a               1803 Labe~           900 …          
#  3     3 Estima~ More developed ~ b                901 Deve~          1803 1.06       
#  4     4 Estima~ Less developed ~ c                902 Deve~          1803 1.06       
#  5     5 Estima~ Least developed~ d                941 Deve~           902 1.04       
#  6     6 Estima~ Less developed ~ e                934 Deve~           902 1.06       
#  7     7 Estima~ Less developed ~ NA               948 Deve~          1803 1.05       
#  8     8 Estima~ Land-locked Dev~ f               1636 Spec~          1803 1.04       
#  9     9 Estima~ Small Island De~ g               1637 Spec~          1803 1.05       
# 10    10 Estima~ World Bank inco~ NA              1802 Labe~           900 …          
# # ... with 245 more rows, and 13 more variables: `1955-1960` <chr>, `1960-1965` <chr>,
# #   `1965-1970` <chr>, `1970-1975` <chr>, `1975-1980` <chr>, `1980-1985` <chr>,
# #   `1985-1990` <chr>, `1990-1995` <chr>, `1995-2000` <chr>, `2000-2005` <chr>,
# #   `2005-2010` <chr>, `2010-2015` <chr>, `2015-2020` <chr>

Example column where NA is not being created, and values are not of numeric type...

d3 %>% select(`1950-1955`) %>% pull()
#  [1] "1.06"               "…"                  "1.06"               "1.06"              
#  [5] "1.04"               "1.06"               "1.05"               "1.04"              
#  [9] "1.05"               "…"                  "1.06"               "1.06"

@RuiBarradas that does not seem to work for me (see the commented out part of my example `read_excel()` code) — guyabel, Jun 23 '19 at 15:04
For plan B, this worked for me - `d0 %>% mutate_all(na_if, "…")` — Shree, Jun 23 '19 at 15:10

score 3 · Accepted Answer · answered Jun 23 '19 at 15:30

3

As you can see here Print unicode character string in R the issue is with how you define the unicode char.

Try this:

readxl::read_xlsx("C:/Stack/WPP2019_FERT_F02_SEX_RATIO_AT_BIRTH.xlsx", sheet = 2, skip = 16, na="\U2026")

answered Jun 23 '19 at 15:30

LocoGris

4,432
3
15
30

score 2 · Answer 2 · answered Jun 23 '19 at 15:21

Simple but effective, just convert the column to numeric after importing. Throws a warning, but who cares.

head(df)
#    something    v
# 1 -0.2168503 1.06
# 2  0.9863558    …
# 3  1.8623381 1.06
# 4 -1.0441477 1.06
# 5  0.4244308 1.04
# 6  1.5825152 1.06

df <- transform(df, v=as.numeric(v))
head(df)
#    something    v
# 1 -0.2168503 1.06
# 2  0.9863558   NA
# 3  1.8623381 1.06
# 4 -1.0441477 1.06
# 5  0.4244308 1.04
# 6  1.5825152 1.06

Data

df <- structure(list(something = c(0.344600422686915, 1.12754949114835, 
0.264102711671497, -0.588052830551214, 0.916134405190614, 0.118418825652515, 
-1.5711759894206, 0.561452729377526, -0.27524305006459, -0.611306705421411, 
0.614179300117269, 0.765082495652037), v = c("1.06", "…", "1.06", 
"1.06", "1.04", "1.06", "1.05", "1.04", "1.05", "…", "1.06", 
"1.06")), class = "data.frame", row.names = c(NA, -12L))

set ... to na when using read_excel()

2 Answers2