as.Date function returns NA values

[Manikandan]

Hi. I am trying to change a date column that in character type to date type.

str(flight_data$journey_date)
chr [1:300261] "11-02-2022" "11-02-2022" "11-02-2022" "11-02-2022" "11-02-2022" "11-02-2022" "11-02-2022"......

I wrote the following code to make the change.

flight_data$journey_date <- as.Date(flight_data$journey_date, format= "%m-%d-%Y")

But for some reason, the whole column is getting filled with NA values instead of actual date.

Please help.

[nirgrahamuk]

It seems you are doing the correct thing.

(mydata <- data.frame(adate = "11-02-2022"))
(mydata$adate <- as.Date(mydata$adate, format= "%m-%d-%Y"))

it works...
how are you determining that the whole column is filled with NA after this point ? do you use any particular code for that ? what do you see ? is it maybe not the whole column, because at least for 11-02-2022 it should work.

[Manikandan]

I checked for NA values in the data set and I got this.

colSums(is.na(flight_data))
journey_date
216333

And I checked the column's data. This looks weird. Only 122 observations are converted to date. Rest all are filled with NA.

flight_data$journey_date
[1] "2022-11-02" "2022-11-02" "2022-11-02" "2022-11-02" "2022-11-02" "2022-11-02" "2022-11-02" "2022-11-02"
.
.
.
[121] "2022-12-02" "2022-12-02" NA NA NA NA NA NA NA NA NA NA

However, look at the str. The column type is date. (Not sure what is ", format")

str(flight_data)
'data.frame': 300261 obs. of 11 variables:
$ journey_date: Date, format: "2022-11-02" "2022-11-02"

[FJCC]

Since you hit a streak of NA values in row 123, please show the result of

flight_data$journey_date[120:130]

from the original data frame, before running as.Date().

[Manikandan]

Here is the answer.

flight_data$journey_date[120:130]
[1] "12-02-2022" "12-02-2022" "12-02-2022" "13-02-2022" "13-02-2022" "13-02-2022" "13-02-2022" "13-02-2022"
[9] "13-02-2022" "13-02-2022" "13-02-2022"

[nirgrahamuk]

13th cant be a month so those are d m y formats; Is it possible that in fact all the dates are d m y, and the original "11-02-2022" was 11th Feb rather than 2nd November ?

[Manikandan]

OMG. How dumb of me to not notice this silly mistake. Thank you nirgrahamuk. Appreciate your help. It is dd-mm-yyyy formatted.