0

While importing data from an excel file using the read_excel function in the readxl package, I have tried specifying a number for the na argument but it does not recode the missing values. The data was entered with a numeric value as the missing response.

Example data (in r rather than an excel file though):

missing <- data.frame(a = c(1, 2, 3, 4),
                      b = c(99, 2, 3, 4),
                      c = c(1, 99, 3, 4))

If the above data were a separate excel file I tried reading it in like so:

data <- read_excel("C:/.../missing.xlsx", na = 99)

Instead of recoding the 99's to be NA, they just get read in as numbers. I'd like to see a data frame like this without having to use a separate step to recode the data:

  a b c
1 1 NA 1
2 2 2 NA
3 3 3 3
4 4 4 4
Andrew Jackson
  • 823
  • 1
  • 11
  • 23

1 Answers1

0

read_excel is looking for a string. So you just need to type

data <- read_excel("C:/.../missing.xlsx", na = "99")

user14353
  • 135
  • 5
  • I tried this with the small example data and it worked, but it isn't working for a dataset with 2000+ rows and 7 columns. – Andrew Jackson Oct 18 '16 at 06:04
  • It actually may have to do with the number I'm trying to replace. The actual data ranges from 1 to 4 with my missing values put in as 5. The `summary` function just says the max is 5. When I do this to replace 99 it works. – Andrew Jackson Oct 18 '16 at 06:09
  • Actually, one last thought may have done it. Would the 'protected view' cause problems with this? The files were downloaded from the internet and I hadn't opened them in excel. I thought that would only be an excel thing but when I opened the files and saved them it worked. – Andrew Jackson Oct 18 '16 at 06:15
  • Depending on what you're trying to turn in to NA in your dataset you can just use `data[data==99]<-NA` or replace the 99 with your number. Or if you are just trying to replace a specific number with NA in a particular column in your dataframe that you've named `data`, like maybe column `b`, you can do `data$b[data$b==99]<-NA` – user14353 Oct 18 '16 at 06:17
  • If you could accept my response as the answer that would be great! – user14353 Oct 18 '16 at 07:16