Fast replace in data frame

Asked Oct 20 '14 at 22:28

Active Mar 14 '16 at 11:10

Viewed 452 times

I need to replace NA in a data frame, the thing is that the table has many columns (over 5000), so the code:

y[is.na(y)]<-0

takes a lot of time. Is it any other way to do the same faster? With only 200 rows takes more than 3 minutes.

EDIT

The columns are all integers, and are the result of dcast (from the reshape2 package).

I tried:

z<-data.table(y)
z[is.na(z)]<-0

and it still takes quite a long time.

edited Mar 14 '16 at 11:10

3442

8,248
2
19
41

asked Oct 20 '14 at 22:28

GabyLP

3,649
7
45
66

How long is this taking? A simple assignment operation should be very quick. 5000 rows is really not large at all either. – thelatemail Oct 20 '14 at 22:33
@thelatemail, sorry columns – GabyLP Oct 20 '14 at 22:35
Ok - How many rows to go with the 5k columns? – thelatemail Oct 20 '14 at 22:36
@thelatemail 15000 rows – GabyLP Oct 20 '14 at 22:36
1

Did you try `data.table`? – bright-star Oct 20 '14 at 22:40
Something strange is happening. I can do 5000 columns with 15000 rows using `y[is.na(y)]<-0` in 5 seconds. This is no supercomputer either. – thelatemail Oct 20 '14 at 22:41
@thelatemail, maybe, y is the result of a reshape2 function, but the class is still a data.frame – GabyLP Oct 20 '14 at 22:42
Are your columns all numeric? Try converting to a matrix and doing the NA replacement on that. – Spacedman Oct 20 '14 at 22:43
Here's what I did as a test, with 5000 columns and 200 rows: `test <- data.frame(replicate(5000,sample(c(1:3,NA),200,replace=TRUE),simplify=FALSE)); system.time(test[is.na(test)]<-0)` Timing = 1.6 seconds. – thelatemail Oct 20 '14 at 22:49
Have you tried `lapply`? Also closing youtube and iTunes helps ;-) – Rich Scriven Oct 20 '14 at 22:51
@thelatemail. thanks. for me that takes for ever. and the laptop has 8gb ram and core i7. – GabyLP Oct 20 '14 at 22:52
I'm seeing BIG differences between R 3.0.2 (slooow) and 3.1.1 (fast) with @thelatemail's test code. Are you on 3.1.1 yet? – Spacedman Oct 20 '14 at 22:53
@Spacedman - 3.1.0 here. – thelatemail Oct 20 '14 at 22:54
@Spacedman, yes, I'm in 3.0.2. I'll change it and run it again. – GabyLP Oct 20 '14 at 22:55
1

`test[] <- lapply(test, function(x) replace(x,is.na(x),0) )` is also faster. – thelatemail Oct 20 '14 at 22:56
1

`m=as.matrix(test);m[is.na(m)]=0; test=data.frame(m)` is probably equivalent and runs fast in 3.0.2 too... I've always thought if you have a data frame as a rectangular set of numbers and *not* as records then you should probably be using a matrix... – Spacedman Oct 20 '14 at 22:56
@Spacedman; yes, that was fast. thanks both! – GabyLP Oct 20 '14 at 22:58
@thelatemail, with lapply is faster than the original but as.matrix is faster than both. – GabyLP Oct 20 '14 at 23:00
1

Your solution doesn't take advantage of `data.table` at all. You might want to check out this answer: http://stackoverflow.com/a/7249454/2954547. In particular, read up on the `:=` operator and the `set` function. – shadowtalker Oct 21 '14 at 03:42

Fast replace in data frame

0 Answers0