0

I have a data frame that is time-series like in nature where an order determines the value of the $feed_type column. Unfortunately the order can change at a later $date and affect successive days. Because the order is only made once for the particular product, the subsequent values of $date are N/A unless the order is changed. I need to impute the value for $feed_type for successive days unless it changes, and if it changes, impute it similarly. This is necessary to calculate the total amount delivered based on a $feed_type dependent factor that will be joined later. The data look like this: 

df <- data.frame(id = c("1", "1", "1", "1", "1", "2", "2",
                        "2", "2", "3", "3", "3", "3"),
                 feed_type = c("A", "N/A", "N/A", "B", "N/A", "A", "N/A",
                               "B", "N/A", "B", "N/A", "N/A", "N/A"),
                 feed_amount = c(75, 75, 80, 100, 82, 90, 70,
                                 70, 80, 90, 100, 50, 75),
                 date = c("2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04", "2020-01-05",
                          "2020-01-04", "2020-01-05", "2020-01-06", "2020-01-07", "2020-01-02",
                          "2020-01-03", "2020-01-04", "2020-01-05"))

I would like the data to look like this

> df
   id feed_type feed_amount       date
1   1         A          75 2020-01-01
2   1         A          75 2020-01-02
3   1         A          80 2020-01-03
4   1         B         100 2020-01-04
5   1         B          82 2020-01-05
6   2         A          90 2020-01-04
7   2         A          70 2020-01-05
8   2         B          70 2020-01-06
9   2         B          80 2020-01-07
10  3         B          90 2020-01-02
11  3         B         100 2020-01-03
12  3         B          50 2020-01-04
13  3         B          75 2020-01-05

Is this possible? Thank you.

nlp
  • 151
  • 5
  • 1
    Using `dplyr` and `tidyr` , `df %>% mutate(feed_type = na_if(feed_type, 'N/A')) %>% fill(feed_type)` – Ronak Shah Apr 08 '20 at 13:37
  • I have some clarifications. Are you trying to impute an actually unknown value or are you trying to fill in `NA`s based on a set of rules? Alternatively, are we merely filling in `NA`s with the previous `feed_type` by `id`? – Ian Campbell Apr 08 '20 at 13:37
  • @RonakShah That did it. Didn't think there was a dplyr/tidyr solution for that. Thank you! – nlp Apr 08 '20 at 13:46

0 Answers0