CODEWITHSUNDEEP
r
Gino
Gino

Reputation: 15

In R, unambiguous format error when removing "NA"

I'm trying to simply convert the NA values of a column into blanks in preparation for Excel export. My code returns the error below:

> class(manifest_final$MIN_SAIL_DATE)
[1] "Date"
> head(manifest_final$MIN_SAIL_DATE)
[1] "2016-10-23" NA           "2016-10-23" NA           NA           NA          
> manifest_final$MIN_SAIL_DATE[is.na(manifest_final$MIN_SAIL_DATE)] <- ''
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

the column MIN_SAIL_DATE was a actually a computed column from a sqlQuery{RODBC} function and then join{plyr} to another table. the column was originally in the POSIX format and I forced it to Date.

EDIT:::

This following code solved my problem with the NAs when executed immediately after querying the data:

next_sail$MIN_SAIL_DATE <- as.character.Date(next_sail$MIN_SAIL_DATE)

the following join was used to append two detail columns from next_detail onto manifest on both person_num and MIN_SAIL_DATE=SAIL_SAIL_DATE:

manifest <- merge(manifest, next_detail, by.x = c('PERSON_NUM', 'MIN_SAIL_DATE'), by.y= c('PERSON_NUM', 'SAIL_SAIL_DTE'), all.x = TRUE)

when the format change happens before the join, I lose the NAs but also lose the appended data. when the format change is moved AFTER the merge I get the original Error in as.POSIXlt.character(x, tz, ...) : character string is not in a standard unambiguous format

A sample of the data is below. then the NAs are removed i lose the circled codes from the join. im assuming its a format issue

code snippet

Upvotes: 0

Views: 1978

Answers (1)

steveb
steveb

Reputation: 5532

For the write function you are using (e.g. write.csv) use the na option as follows to set the NA character

write.csv(manifest_final, your_output_file, na = '')

You can of course include whatever other options you are using in write.csv. This option should be available in the other write.* functions as well.

EDITS

The issue with 

manifest_final$MIN_SAIL_DATE[is.na(manifest_final$MIN_SAIL_DATE)] <- ''

is that you are attempting to set a Date vector element to a character string.

Although, I think the above solution (using na=) is the way to go, you can also convert the MIN_SAIL_DATE column to a string, then set the NA columns to the empty string.

Upvotes: 1

Related Questions