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
In R, missing values are represented by the symbol NA (not available).
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.
To select NA values you should use function is.na() .
Another option using data.table
library(data.table)
library(xts)
dt <- data.table(df)
dt[, Price := na.locf(Price, fromLast = TRUE), by = Meal]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With