6

I need to fill in NA rows with the previous row value, but only until a criteria is not changed. As a simple example for days of week, meals and prices:

Day = c("Mon", "Tues", "Wed", "Thus", "Fri", "Sat","Sun","Mon", "Tues", 
       "Wed", "Thus", "Fri", "Sat","Sun") 
Meal = c("B","B","B","B","B","D","D","D","D","L","L", "L","L","L") 
Price = c(NA, 20, NA,NA,NA,NA,NA,15,NA,NA,10,10,NA,10) 
df = data.frame(Meal,Day ,Price )
df
   Meal  Day Price
1     B  Mon    NA
2     B Tues    20
3     B  Wed    NA
4     B Thus    NA
5     B  Fri    NA
6     D  Sat    NA
7     D  Sun    NA
8     D  Mon    15
9     D Tues    NA
10    L  Wed    NA
11    L Thus    10
12    L  Fri    10
13    L  Sat    NA
14    L  Sun    10

I need to fill in the NA with the previous but only for the same meal type, over the week.

I have tried 

     na.locf(df, fromLast = TRUE)
   Meal  Day Price
1     B  Mon    20
2     B Tues    20
3     B  Wed    15
4     B Thus    15
5     B  Fri    15
6     D  Sat    15
7     D  Sun    15
8     D  Mon    15
9     D Tues    10
10    L  Wed    10
11    L Thus    10
12    L  Fri    10
13    L  Sat    10
14    L  Sun    10

which is wrong as overlaps the meal type. The data should look like this:

  Meal  Day Price
1     B  Mon    20
2     B Tues    20
3     B  Wed    20
4     B Thus    20
5     B  Fri    20
6     D  Sat    15
7     D  Sun    15
8     D  Mon    15
9     D Tues    15
10    L  Wed    10
11    L Thus    10
12    L  Fri    10
13    L  Sat    10
14    L  Sun    10

Many Thanks

akrun
  • 874,273
  • 37
  • 540
  • 662
lchester
  • 71
  • 1
  • 4
  • What if there are duplicates for the 'Day' column for each 'Meal' i.e multiple weeks per 'Meal? Does the condition resets on 'Mon' or 'Sun'? – akrun Apr 11 '15 at 04:00

3 Answers3

7

Another option using data.table

library(data.table)
library(xts)

dt <- data.table(df)

dt[, Price := na.locf(Price, fromLast = TRUE), by = Meal]
Chase
  • 67,710
  • 18
  • 144
  • 161
  • I get warning message with this option though it works – akrun Apr 11 '15 at 03:43
  • I tried this solution and also get warnings, but the result looks ok. – lchester Apr 11 '15 at 10:02
  • @akrun & @ichester - Yeah I got that warning too, not sure why to be honest...akrun - I got a similar warning from your previous `data.table()` answer as well. – Chase Apr 11 '15 at 18:48
  • @Chase Yes, it did showed for my data.table solution as well, also, my solution was not correct as there were multiple non-NA values per Meal, though the example is a simple case, I felt that there would be cases with different non-NA values per Meal, and also I am still not sure about if the dataset has multiple Weeks per Meal.. – akrun Apr 11 '15 at 18:51
  • 1
    I tried this code verbatim and I get an error message saying: `Error in na.locf(Price, fromLast = TRUE) : unused argument (fromLast = TRUE)` – nak5120 Oct 22 '18 at 23:47
  • anyone else getting the same thing? – nak5120 Oct 22 '18 at 23:47
1

You could try

library(zoo)
library(dplyr)
df %>% 
     group_by(Meal) %>% 
     mutate(Price= ifelse(any(!is.na(Price)), na.locf(na.locf(Price, 
                    fromLast=TRUE, na.rm=FALSE)), NA_real_))
#     Meal  Day Price
#1     B  Mon    20
#2     B Tues    20
#3     B  Wed    20
#4     B Thus    20
#5     B  Fri    20
#6     D  Sat    15
#7     D  Sun    15
#8     D  Mon    15
#9     D Tues    15
#10    L  Wed    10
#11    L Thus    10
#12    L  Fri    10
#13    L  Sat    10
#14    L  Sun    10
akrun
  • 874,273
  • 37
  • 540
  • 662
  • I tested this solution on the sample data and it works fine. If I used it on my data (7Million rows) it errors with: Error: incompatible size (0), expecting 39712 (the group size) or 1 – lchester Apr 11 '15 at 10:03
  • Perhaps `df %>% group_by(Meal) %>% mutate(Price= ifelse(any(!is.na(Price)), na.locf(na.locf(Price, fromLast=TRUE, na.rm=FALSE)), NA_real_))` – akrun Apr 11 '15 at 15:40
0

How about this?

library(dplyr)

df %>% group_by(Meal) %>% fill( Price, .direction = 'updown' )

Michael
  • 196
  • 2
  • 5