[R] remove rows with infinite/nan values from a zoo dataset
arun
smartpink111 at yahoo.com
Wed Sep 4 16:38:53 CEST 2013
Hi,
This is confusing because the error message suggests that you were not able to read the csv file. Then how did you removed the Inf values?. I guess this should be a different dataset.
From your previous email:
"
prices=read.zoo("C:\\Users\\Desktop\\\\awc_au.csv",header=TRUE,sep=",",format="%Y-%m-%d" ")"
Try:
read.zoo(...., fill=TRUE)
From my first reply:
dat1<- read.zoo(text="2009-07-15,#N/A N/A,#N/A N/A,18.96858
2009-07-16,20.30685,20.40664,#N/A N/A
2009-07-17,20.78813,20.03991,20.40664
2009-07-20,21.41278,21.41278,20.03991
2009-07-21,22.9963,22.98397,21.41278
2009-07-22,23.06443,23.01112,22.98397
2009-07-23,23.45905,24.72232,23.01112
2009-07-24,24.89291,25.56603,24.72232
2009-07-27,25.38929,24.80535,25.56603
2009-07-28,25.26712,25.65566,24.80535
2009-07-29,25.83884,24.98163,25.65566
2009-07-30,#N/A N/A,#N/A N/A,24.98163
2009-08-03,25.25553,25.93297,#N/A N/A
2009-08-04,26.02464,25.49159,25.93297
",sep=",",header=FALSE,FUN=as.Date,format="%Y-%m-%d")
#Error in scan(file, what, nmax, sep, dec, quote, skip, nlines, na.strings, :
# line 1 did not have 4 elements
dat1<- read.zoo(text="2009-07-15,#N/A N/A,#N/A N/A,18.96858
2009-07-16,20.30685,20.40664,#N/A N/A
2009-07-17,20.78813,20.03991,20.40664
2009-07-20,21.41278,21.41278,20.03991
2009-07-21,22.9963,22.98397,21.41278
2009-07-22,23.06443,23.01112,22.98397
2009-07-23,23.45905,24.72232,23.01112
2009-07-24,24.89291,25.56603,24.72232
2009-07-27,25.38929,24.80535,25.56603
2009-07-28,25.26712,25.65566,24.80535
2009-07-29,25.83884,24.98163,25.65566
2009-07-30,#N/A N/A,#N/A N/A,24.98163
2009-08-03,25.25553,25.93297,#N/A N/A
2009-08-04,26.02464,25.49159,25.93297
",sep=",",header=FALSE,FUN=as.Date,format="%Y-%m-%d",fill=TRUE)##works.
A.K.
Thank you A.K.
The infinite values are removed but how about the NA values in a
csv file? I got error message like "Error in scan(file, what, nmax,
sep, dec, quote, skip, nlines, na.strings, :
line 1356 did not have 4 elements"
How could I ignore the rows with NA value when read a csv file?
Thank you.
R.L
----- Original Message -----
From: arun <smartpink111 at yahoo.com>
To: R help <r-help at r-project.org>
Cc:
Sent: Tuesday, September 3, 2013 11:49 AM
Subject: Re: remove rows with infinite/nan values from a zoo dataset
Hi,
No problem.
In my previous post, I showed how to dput() your example dataset. Please use dput() in the future.
vec1<- c(3.369247e-04,0.000000e+00,9.022183e-04,0.000000e+00,-1.105819e-04,-Inf,1.191271e-04,1.681718e-04,NaN,1.150126e-04,1.031037e-03,2.710993e-04)
indx<-seq(as.Date("2009-09-01"),as.Date("2009-09-17"),by=1)
indx1<-indx[-c(5:7,12:13)]
library(zoo)
z1<- zoo(vec1,order.by=indx1)
sum(z1,na.rm=TRUE) #without removing the Inf.
#[1] -Inf
sum(z1[is.finite(z1)],na.rm=TRUE)
#[1] 0.002833009
#or just
sum(z1[is.finite(z1)])
#[1] 0.002833009
A.K.
Thank you for your reply A.K.
Sorry for my misleading -- the first question should be removing
#N/A N/A values when reading a csv file. So the example provided in the
original post was dragged from a csv spreadsheet directly.
(which I used the code "prices=read.zoo("C:\\Users\\Desktop\\\\awc_au.csv",header=TRUE,sep=",",format="%Y-%m-%d" ")
Then the following up question is removing from a zoo data set.
After some calculation, the new zoo data set is as following:
2009-09-01 2009-09-02 2009-09-03 2009-09-04 2009-09-08 2009-09-09
3.369247e-04 0.000000e+00 9.022183e-04 0.000000e+00 -1.105819e-04 -Inf
2009-09-10 2009-09-11 2009-09-14 2009-09-15 2009-09-16 2009-09-17
1.191271e-04 1.681718e-04 NaN 1.150126e-04 1.031037e-03 2.710993e-04
I need to sum them up so I used "sum(Z, na.rm=TRUE)" to remove the NaN values but not for the Inf/-Inf.
Hope it is clear to you.
Cheers,
R.L
----- Original Message -----
From: arun <smartpink111 at yahoo.com>
To: R help <r-help at r-project.org>
Cc:
Sent: Tuesday, September 3, 2013 2:47 AM
Subject: Re: remove rows with infinite/nan values from a zoo dataset
Hi,
Please dput() the example dataset. When I read from the one shown below, it looks a bit altered.
library(zoo)
dat1<- read.zoo(text="2009-07-15,#N/A N/A,#N/A N/A,18.96858
2009-07-16,20.30685,20.40664,#N/A N/A
2009-07-17,20.78813,20.03991,20.40664
2009-07-20,21.41278,21.41278,20.03991
2009-07-21,22.9963,22.98397,21.41278
2009-07-22,23.06443,23.01112,22.98397
2009-07-23,23.45905,24.72232,23.01112
2009-07-24,24.89291,25.56603,24.72232
2009-07-27,25.38929,24.80535,25.56603
2009-07-28,25.26712,25.65566,24.80535
2009-07-29,25.83884,24.98163,25.65566
2009-07-30,#N/A N/A,#N/A N/A,24.98163
2009-08-03,25.25553,25.93297,#N/A N/A
2009-08-04,26.02464,25.49159,25.93297
",sep=",",header=FALSE,FUN=as.Date,format="%Y-%m-%d",fill=TRUE)
dput(dat1) ###
structure(c(NA, 20.30685, 20.78813, 21.41278, 22.9963, 23.06443,
23.45905, 24.89291, 25.38929, 25.26712, 25.83884, NA, 25.25553,
26.02464, NA, 20.40664, 20.03991, 21.41278, 22.98397, 23.01112,
24.72232, 25.56603, 24.80535, 25.65566, 24.98163, NA, 25.93297,
25.49159, NA, NA, 20.40664, 20.03991, 21.41278, 22.98397, 23.01112,
24.72232, 25.56603, 24.80535, 25.65566, NA, NA, 25.93297), .Dim = c(14L,
3L), .Dimnames = list(NULL, c("V2", "V3", "V4")), index = structure(c(14440,
14441, 14442, 14445, 14446, 14447, 14448, 14449, 14452, 14453,
14454, 14455, 14459, 14460), class = "Date"), class = "zoo")
dat2<- dat1[!rowSums(is.na(dat1)),]
dat2
# V2 V3 V4
#2009-07-17 20.78813 20.03991 20.40664
#2009-07-20 21.41278 21.41278 20.03991
#2009-07-21 22.99630 22.98397 21.41278
#2009-07-22 23.06443 23.01112 22.98397
#2009-07-23 23.45905 24.72232 23.01112
#2009-07-24 24.89291 25.56603 24.72232
#2009-07-27 25.38929 24.80535 25.56603
#2009-07-28 25.26712 25.65566 24.80535
#2009-07-29 25.83884 24.98163 25.65566
#2009-08-04 26.02464 25.49159 25.93297
dat2[1,2]<- Inf
dat2[5,3]<- -Inf
dat2[rowSums(is.finite(dat2))==ncol(dat2),]
# V2 V3 V4
#2009-07-20 21.41278 21.41278 20.03991
#2009-07-21 22.99630 22.98397 21.41278
#2009-07-22 23.06443 23.01112 22.98397
#2009-07-24 24.89291 25.56603 24.72232
#2009-07-27 25.38929 24.80535 25.56603
#2009-07-28 25.26712 25.65566 24.80535
#2009-07-29 25.83884 24.98163 25.65566
#2009-08-04 26.02464 25.49159 25.93297
A.K.
Hi There,
I have a dataset with many rows and few columns as following:
2009-07-15 #N/A N/A #N/A N/A 18.96858
2009-07-16 20.30685 20.40664 #N/A N/A
2009-07-17 20.78813 20.03991 20.40664
2009-07-20 21.41278 21.41278 20.03991
2009-07-21 22.9963 22.98397 21.41278
2009-07-22 23.06443 23.01112 22.98397
2009-07-23 23.45905 24.72232 23.01112
2009-07-24 24.89291 25.56603 24.72232
2009-07-27 25.38929 24.80535 25.56603
2009-07-28 25.26712 25.65566 24.80535
2009-07-29 25.83884 24.98163 25.65566
2009-07-30 #N/A N/A #N/A N/A 24.98163
2009-08-03 25.25553 25.93297 #N/A N/A
2009-08-04 26.02464 25.49159 25.93297
The class of the dataset is "zoo". My question might be stupid
but could anyone suggest a way to remove the rows with #N/A values?
I tried "rapply" command but it didn't work due to the data class.
btw, how about for the "Inf" values?
Thank you in advance!
More information about the R-help
mailing list