Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

R fill in NA with previous row value with condition

Tags:

r

na

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

like image 418
lchester Avatar asked Apr 11 '15 02:04

lchester


People also ask

Is Na code in R?

In R, missing values are represented by the symbol NA (not available).

In which either the previous value or the next value is used to fill the missing value?

Fills missing values in selected columns using the next or previous entry. This is useful in the common output format where values are not repeated, and are only recorded when they change.

How do I select NA in R?

To select NA values you should use function is.na() .


1 Answers

Another option using data.table

library(data.table)
library(xts)

dt <- data.table(df)

dt[, Price := na.locf(Price, fromLast = TRUE), by = Meal]
like image 81
Chase Avatar answered Sep 29 '22 15:09

Chase