0

What is equivalent SQL server isnumeric function in R studio. I am trying to migrate one of SQL logic to r studio and i have column where it holds both Char values and Int values, now i want take only int values and update them as -1 in R data.table. Please help me to solve the problem.

I have attached results as image, column "A" values are current values and i am expecting have the values like column B.

enter image description here

Hack-R
  • 22,422
  • 14
  • 75
  • 131
Koppula
  • 95
  • 7
  • Welcome to StackOverflow! I think I have an answer for you below, but for the next time please ask with [reproducible](https://stackoverflow.com/q/5963269/1422451) code/data per the [MCVE](https://stackoverflow.com/help/mcve) and [`r`](https://stackoverflow.com/tags/r/info) tag description. Please only use screenshots to display something inherently visual and nontabular like a plot or a GUI menu. – Hack-R Aug 26 '18 at 16:44
  • `ifelse(is.na(as.integer(x)), x, -1)`, where `x` is your column data. – Rich Scriven Aug 27 '18 at 17:39

3 Answers3

1

There are also data type tools in R (as in SQL and other languages) such as is.numeric() and is.integer() in R. Normally these return boolean values, but you could use sub or gsub() to make it -1:

example <- list(123, 321, "not numeric", as.Date("2018/01/01"))

gsub(T, -1, sapply(example, is.numeric))

[1] "-1" "-1" "FALSE"   "FALSE"

Also, note that in R numeric is different from integer. 

example <- list(as.integer(123), 321, "not numeric", as.Date("2018/01/01"))
example[sapply(example, is.integer)] <- -1
example

[[1]]
[1] -1

[[2]]
[1] 321

[[3]]
[1] "not numeric"

[[4]]
[1] "2018-01-01"

You can convert them back and forth with as.numeric() and as.integer(). Also, note that in R data types in this sense are referred to as the class or classes of the data, whereas the type in R refers to the storage or R internal data type.

Hack-R
  • 22,422
  • 14
  • 75
  • 131
  • Thanks for your help. Unfortunately i am not able to fix the issue. when i use following statement "sub(T, -1, sapply(DimCombinedEnduser$DRVDEUL2AMID, is.numeric))" it's giving "FALSE" as results set that mean it's considering all integer values as chars. if i convert the column as numeric it's updating all values as "-1" instead of Integer values. i can share some sample records in offline. – Koppula Aug 27 '18 at 12:42
  • same statement working fine when hard code the values and parse it, but when i run same statement through column it's not working. is there anything i need to check it explicitly? – Koppula Aug 27 '18 at 12:59
0

I think if you're specifically interested in integers, then the question above is a duplicate of the following: Check if the number is integer

Your if condition would be something like x == round(x, 0). This will be TRUE if values are integers, but not double or other non-numeric classes.

JonMinton
  • 1,239
  • 2
  • 8
  • 26
0

Finally i have fix this issue by following below steps.

  1. captured all numeric values to separate data table by using below script CustomDerivedL2AMID <- (subset(DimCombinedEnduser$DRVDEUL2AMID, grepl('^\d+$',DimCombinedEnduser$DRVDEUL2AMID))) library(data.table) HandleDerivedL2AMID <-data.table(CustomDerivedL2AMID)

  2. match the HandleDerivedL2AMID table results with original data table and replaced all values to -1. DCE$DRVDEUL2AMID <- replace(DCE$DRVDEUL2AMID,DCE$DRVDEUL2AMID %in% HandleDerivedL2AMID$CustomDerivedL2AMID,'-1')

now i see only character values. no more numeric values with data set under DRVDEUL2AMID.

Koppula
  • 95
  • 7