CODEWITHSUNDEEP
rdateloopsreplace
GabyLP
GabyLP

Reputation: 3781

R Fill cells with previous data

I have a table like the following:

    days    Debit   loaddate    
1   23/01/2014  138470289.4 23/01/2014  
2   24/01/2014  NA  NA  
3   25/01/2014  NA  NA  
4   26/01/2014  NA  NA  
5   27/01/2014  NA  NA

one row for each day and then in the columns loaddate after a few NA another date appears:

28  19/02/2014  NA  NA  
29  20/02/2014  NA  NA  
30  21/02/2014  NA  NA  
31  22/02/2014  9090967.9   22/02/2014  
32  23/02/2014  NA  NA  
33  24/02/2014  308083.5    24/02/2014

I would like to replace each NA in loaddate column with the previous date in loaddate.

I tried:

for(i in 1:nrow(data3)) 
    { if (!is.na(data3[i,'Debit'])) 
          {data3[i,'loaddate1']<-as.Date(data3[i,'loaddate'], format='%Y-%m-%d')}
      else {data3[i,'loaddate1']<-data3[i-1,'loaddate1']}
}

But I got the wrong format: 

> head(data3)
        days     Debit   loaddate loaddate1
1 2014-01-23 138470289 2014-01-23     16093
2 2014-01-24        NA       <NA>     16093
3 2014-01-25        NA       <NA>     16093
4 2014-01-26        NA       <NA>     16093
5 2014-01-27        NA       <NA>     16093
6 2014-01-28        NA       <NA>     16093

I need to get the date format also. If I do:

for(i in 1:nrow(data3)) 
    { if (!is.na(data3[i,'Debit'])) 
          {data3[i,'loaddate1']<-as.Date(data3[i,'loaddate'], format='%Y-%m-%d')}
      else {data3[i,'loaddate1']<-as.Date(data3[i-1,'loaddate1'], format='%Y-%m-%d')}
}

I got the wrong result (with NA).

        days     Debit   loaddate loaddate1
1 2014-01-23 138470289 2014-01-23     16093
2 2014-01-24        NA       <NA>      <NA>
3 2014-01-25        NA       <NA>      <NA>
4 2014-01-26        NA       <NA>      <NA>
5 2014-01-27        NA       <NA>      <NA>
6 2014-01-28        NA       <NA>      <NA>

How can I get the right result and with the right format? Also, Is there a better way to do this replacement? I mean without a loop. Thanks.

Upvotes: 1

Views: 111

Answers (1)

flodel
flodel

Reputation: 89057

Try zoo::na.locf and make sure to use the appropriate date format:

library(zoo)
data3$loaddate <- as.Date(na.locf(data3$loaddate), format='%d/%m/%Y'))

Upvotes: 4

Related Questions