I've got a massive data.table so I can't see all my entries in the columns.
I want to convert a column, which is apparently of class character to numeric, however, when I use as.numeric(col_name), i get the warning "NAs introduced by coercion". Before I do anything else, I was wondering whether I can find out which entries in the column are not characters or what is causing the issue.
I do str on the data.table which gives:
Classes ‘data.table’ and 'data.frame': 57042881 obs. of 21 variables:
$ V1 : int 142466 1265 142510 199933 143297 13548 143605 15194 143894 16701 ...
$ V2 : int 1 1 1 1 1 1 1 1 1 1 ...
$ V3 : int 20150702 20160316 20150702 20160316 20150703 20160324 20150704 20160327 20150704 20160331 ...
$ V4 : int 14 17 15 6 16 17 9 20 14 15 ...
$ V5 : chr "2015-07-02 14:50:00" "2016-03-16 17:40:00" "2015-07-02 15:58:00" "2016-03-16 06:20:00" ...
$ V6 : int 33547 25523 33547 25523 33547 25523 33547 25523 33547 25523 ...
$ V7 : num 42.9 33.9 53.8 65.3 35.7 ...
$ V8 : int 2 2 2 2 2 2 2 2 2 2 ...
$ V9 : num 60 34.5 75.3 66.5 50 ...
$ V10: num 5.46 3.14 6.84 6.05 4.55 3.3 0.71 2.18 3.11 1.82 ...
$ V11: chr "1.271732" "0.926145" "1.271883" "0.926295" ...
$ V12: num 1.4 1.02 1.4 1.02 1.4 ...
$ V13: int 0 0 0 0 0 0 0 0 0 0 ...
$ V14: int 0 0 0 0 0 1 0 0 0 0 ...
$ V16: chr "ULP" "ULP" "ULP" "ULP" ...
$ V17: POSIXct, format: "2015-07-02 14:50:00" "2016-03-16 17:40:00" "2015-07-02 15:58:00" "2016-03-16 06:20:00" ...
$ V18: Date, format: "2015-07-02" "2016-03-16" "2015-07-02" "2016-03-16" ...
$ V19: int 2015 2016 2015 2016 2015 2016 2015 2016 2015 2016 ...
$ V20: int 7 3 7 3 7 3 7 3 7 3 ...
$ V21: int 2 16 2 16 3 24 4 27 4 31 ...
And then I want to convert V11 to numeric.
dt_2 <- dt[, V11 := as.numeric(V11)]
Warning message:
In eval(expr, envir, enclos) : NAs introduced by coercion
Why am I getting this warning? Is it because there are types other than character in column V11? If so, how do I find the values in column V11 which aren't character?
Thanks!
As the dataset is really big, it may be better to read the single column again in a fresh session (as the OP already replaced the column 'V11' by assigning (:=) it to itself.
library(data.table)
dt1 <- fread("yourfile.csv", select = 11)
By using the select argument, we can read the specific column. Then, we convert that column to numeric, check the NA elements with is.na too create a logical vector.
i1 <- is.na(as.numeric(dt1[[1]]))
Subset the column based on the 'i1'
v1 <- dt1[[1]][i1]
and then do the investigation.
after 1 hour
and based on the investigation, the OP mentioned that the values were "null". In that case, we can use na.strings = "null" in fread and it should replace the "null" with NA and we get the correct class (assuming there are no other non-numeric strings)
dt2 <- fread("yourfile.csv", na.strings = "null")