CODEWITHSUNDEEP
rexcelimport
user1665355
user1665355

Reputation: 3393

Converting excel output from character to numeric in R

I have a problem. Say I have a vector x:

x:

 [1] "2 416" "143"   "280"   "2 503" "144"   "128"   "55"    "697"   "826"   "9"     "35"    "9 257" "234"   "2 044" NA      "219"  
[17] NA      NA      "219"   "7 431" "82"    "88"    "186"   "231"   "192"   "456"   "585"   "75"    "142"   NA      NA      NA     
[33] "72"    "246"   "900"   "143"   "231"   "195"   "282"   "226"   "967"   "247"   "2 252" "694"   "64"    "7 744" "204"   "428"  
[49] "19"    "94"    "174"   "292"   "94"    "172"   "221"   "123"   "404"   "385"   "324"   "346"   "658"   "53"    "377"   "119"  
[65] NA      "51"    "391"   "1 072" "387"   "1 742" "518"   "173"   "366"   "67"    "163"   "1 151" "382"   "864"   "184"   "172"  
[81] NA      "538"   "39"    "2 272" "334"   "464"   "82"    "112" 

class(x) 
"character"

I imported this vector from Excel

x=read.csv(file="C:/Users/Documents/x.csv",header=TRUE,sep=";",na.strings=c("NA",""),
dec = ",",stringsAsFactors=FALSE,blank.lines.skip = F)

No when I try to convert x to numeric, this happens:

as.numeric(x)

 [1]  NA 143 280  NA 144 128  55 697 826   9  35  NA 234  NA  NA 219  NA  NA 219  NA  82  88 186 231 192 456 585  75 142  NA  NA  NA  72
[34] 246 900 143 231 195 282 226 967 247  NA 694  64  NA 204 428  19  94 174 292  94 172 221 123 404 385 324 346 658  53 377 119  NA  51
[67] 391  NA 387  NA 518 173 366  67 163  NA 382 864 184 172  NA 538  39  NA 334 464  82 112
Warning message:
NAs introduced by coercion

So some numbers, i.e. "2 416" is converted to NA, while I want the number to be converted to 2 416. I want R to interpret "2 416" as a numeric value 2416What am I doing wrong?

Best Regards

Upvotes: 2

Views: 4148

Answers (2)

agstudy
agstudy

Reputation: 121608

Using stringr package

you can do something like 

library(stringr)
> as.numeric(str_replace_all(dat,pattern=' ',replacement=''))
 [1] 2416  143  280 2503  144  128   55  697  826    9   35 9257  234 2044   NA  219   NA   NA  219 7431   82   88  186  231  192  456  585   75  142
[30]   NA   NA   NA   72  246  900  143  231  195  282  226  967  247 2252  694   64 7744  204  428   19   94  174  292   94  172  221  123  404  385
[59]  324  346  658   53  377  119   NA   51  391 1072  387 1742  518  173  366   67  163 1151  382  864  184  172   NA  538   39 2272  334  464   82
[88]  112

Upvotes: 0

Andrie
Andrie

Reputation: 179558

You have to replace the spaces with empty strings before doing the conversion:

x <- c("2 416", "143", "280", "2 503")

Immediate conversion fails, because "2 416" isn't a number:

as.numeric(x)
[1]  NA 143 280  NA
Warning message:
NAs introduced by coercion

Use gsub() to replace spaces with empty strings, then convert:

as.numeric(gsub(" ", "", x))
[1] 2416  143  280 2503

Upvotes: 3

Related Questions