Adding in Missing Data

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding in Missing Data

Jeff08
Sample Data.Frame format

Name is Returns.names

            X       id ticker      date_ adjClose totret RankStk
427225 427225 00174410    AHS 2001-11-13    21.66    100    1235


"id" uniquely defines a row


What I am trying to do is add missing data for each ID.

Important Information: Date is not continuous, the data points are for trading days, so weekends/certain holidays are off

x<-unique(Returns.names$date_) gives me the list of all the possible trading days.

For days that are missing, I would like to add a row for that date & the same totret as the previous day.

I cant think of an easy way to do this

Reply | Threaded
Open this post in threaded view
|

Re: Adding in Missing Data

Erik Iverson-3
Jeff08 wrote:

> Sample Data.Frame format
>
> Name is Returns.names
>
>             X       id ticker      date_ adjClose totret RankStk
> 427225 427225 00174410    AHS 2001-11-13    21.66    100    1235
>
>
> "id" uniquely defines a row
>
>
> What I am trying to do is add missing data for each ID.
>
> Important Information: Date is not continuous, the data points are for
> trading days, so weekends/certain holidays are off
>
> x<-unique(Returns.names$date_) gives me the list of all the possible trading
> days.
>
> For days that are missing, I would like to add a row for that date & the
> same totret as the previous day.
>
> I cant think of an easy way to do this
>
>
See this thread...

http://www.opensubscriber.com/message/r-help@.../6986471.html

specifically the last post by Gabor.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Adding in Missing Data

Jeff08
Thanks,

That thread talks about adding values to NA. However, the problem with my data is that the missing data points aren't even in the data.frame.

The method I think of is using a loop to check ID by ID, if the date column contains all elements of unique(Returns.names$date_), and if not add those rows in, setting totret equal to the previous date.

Problem is loops are so slow.

Jeff

"See this thread...

http://www.opensubscriber.com/message/r-help@.../6986471.html

specifically the last post by Gabor. "

"Sample Data.Frame format

Name is Returns.names

            X       id ticker      date_ adjClose totret RankStk
427225 427225 00174410    AHS 2001-11-13    21.66    100    1235


"id" uniquely defines a row


What I am trying to do is add missing data for each ID.

Important Information: Date is not continuous, the data points are for trading days, so weekends/certain holidays are off

x<-unique(Returns.names$date_) gives me the list of all the possible trading days.

For days that are missing, I would like to add a row for that date & the same totret as the previous day.

I cant think of an easy way to do this"
Reply | Threaded
Open this post in threaded view
|

Re: Adding in Missing Data

Jeff08
Hey All,

I have just recently thought of a completely different way to accomplish my analysis (requiring different type of coding)

Instead of going in and filling in data, I could remove any dates not shared by ALL the id's.

I was thinking about accomplishing this using merge(~~), do you think this is feasible?

It might take up a bunch of memory at first, going through and subsetting the data.frame by id.





"Sample Data.Frame format

Name is Returns.names

            X       id ticker      date_ adjClose totret RankStk
427225 427225 00174410    AHS 2001-11-13    21.66    100    1235


"id" uniquely defines a row


What I am trying to do is add missing data for each ID.

Important Information: Date is not continuous, the data points are for trading days, so weekends/certain holidays are off

x<-unique(Returns.names$date_) gives me the list of all the possible trading days.

For days that are missing, I would like to add a row for that date & the same totret as the previous day.

I cant think of an easy way to do this"
Reply | Threaded
Open this post in threaded view
|

Re: Adding in Missing Data

PIKAL Petr
Hi

Hm, maybe you can first make a sequence of all required dates and ids,
construct empty data frame with all possible dates, merge your existing
data frame with empty one just to fill in all dates, get rid of duplicated
dates and ids if necessary and finally use na.locf from zoo library to
fill totrets from previous date with split/lapply.

Seems to be quite complicated and maybe if the data frame is big you could
have problems with memory

ex=sample(letters[1:5],10, replace=TRUE)
DF<-data.frame(ex=ex, x=rnorm(10), date=1:10)
DF[c(4,8),]<-NA
EDF<-expand.grid(ex=letters[1:5], date=1:10)
MDF<-merge(DF, EDF, all=T)
MDF$y<-unlist(lapply(split(MDF$x, MDF$ex), na.locf, na.rm=F))

But without some working example it is not easy to find out what you want.

Regards
Petr


[hidden email] napsal dne 08.06.2010 09:03:13:

>
> Hey All,
>
> I have just recently thought of a completely different way to accomplish
my
> analysis (requiring different type of coding)
>
> Instead of going in and filling in data, I could remove any dates not
shared
> by ALL the id's.
>
> I was thinking about accomplishing this using merge(~~), do you think
this
> is feasible?
>
> It might take up a bunch of memory at first, going through and
subsetting

> the data.frame by id.
>
>
>
>
>
> "Sample Data.Frame format
>
> Name is Returns.names
>
>             X       id ticker      date_ adjClose totret RankStk
> 427225 427225 00174410    AHS 2001-11-13    21.66    100    1235
>
>
> "id" uniquely defines a row
>
>
> What I am trying to do is add missing data for each ID.
>
> Important Information: Date is not continuous, the data points are for
> trading days, so weekends/certain holidays are off
>
> x<-unique(Returns.names$date_) gives me the list of all the possible
trading
> days.
>
> For days that are missing, I would like to add a row for that date & the
> same totret as the previous day.
>
> I cant think of an easy way to do this"
> --
> View this message in context:
http://r.789695.n4.nabble.com/Adding-in-Missing-
> Data-tp2246825p2246946.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.