Inserting value in NAs repetitively in order

Question

I have a problem of making my data complete. Below is my data

> head(DF1)
# A tibble: 6 x 4
      Date     Coalprice Gasprice Co2emissionprice
    <date>     <dbl>    <dbl>            <dbl>
1 2015-12-31     47.45    14.40             8.22
2 2015-12-30     47.45    14.30             8.22
3 2015-12-29     47.40    15.40             8.27
4 2015-12-28     47.00    14.42             8.32
5 2015-12-25     47.00    14.20             8.22
6 2015-12-24     47.00    14.20             8.22

So data goes down all the way down to 2011-01-01 from 2015-12-31. But now, if you look carefully, my data has regular missing values. Every weekend's value is missing. So I want to put the prices for weekends as well to fill up NA. What I want to do is fill up every weekend (Sat and Sun) with the same prices on a day before every weekend, so Friday.

So in this example, 2015-12-25' prices 47 14.20 8.22 will go to Sat and Sun as well. Then next weekend's prices will be the same as Friday in that week.

Can you guys help me out with syntax?

Thank you very much for your advice.

dput info is below:

> dput(head(DF1, 30))

structure(list(Date = structure(c(16800, 16799, 16798, 16797, 16794, 16793, 16792, 16791, 16790, 16787, 16786, 16785, 16784, 16783, 16780, 16779, 16778, 16777, 16776, 16773, 16772, 16771, 16770, 16769, 16766, 16765, 16764, 16763, 16762, 16759), class = "Date"), Coalprice = c(47.45, 47.45, 47.4, 47, 47, 47, 47, 47.6, 47.6, 47.8, 47.75, 47.75, 47.7, 47.65, 47.35, 47.4, 47.45, 47.4, 47.75, 48.55, 48.95, 49.1, 49.7, 49.95, 50.3, 53.85, 53.95, 53.95, 54, 54.35), Gasprice = c(14.4, 14.3, 15.4, 14.42, 14.2, 14.2, 13.93, 13.85, 14.35, 14.9, 15.5, 15.25, 15.95, 16.08, 16.23, 16.5, 16.65, 16.75, 16.78, 17.15, 17.15, 17.85, 17.95, 18.2, 17.7, 17.7, 17.88, 17.7, 17.6, 17.5), Co2emissionprice = c(8.22, 8.22, 8.27, 8.32, 8.22, 8.22, 8.22, 8.25, 8.18, 8.07, 8.07, 8.12, 8.19, 8.09, 8.07, 8.36, 8.4, 8.42, 8.42, 8.52, 8.58, 8.49, 8.55, 8.58, 8.56, 8.58, 8.62, 8.65, 8.56, 8.51)), .Names = c("Date", "Coalprice", "Gasprice", "Co2emissionprice"), row.names = c(NA, -30L), class = c("tbl_df", "tbl", "data.frame"))

Jake Kaupp · Answer 1 · 2018-01-16T16:37:15.447

0

You can use tidyr packages to do this in a single line.

library(tidyr)

df <- fill(df, contains("price"), .direction = "down")

edited Jan 16 '18 at 16:37

answered Jan 16 '18 at 16:14

Jake Kaupp

7,892
2
26
36

should it be working by just pasting it? Because it does not work. I did as this. `DF1 <- mutate_at(DF1, contains("price"), funs(fill(., .direction = "down")))` Then it tells me `Error: Variable context not set` – S. Jay Jan 16 '18 at 16:27
My apologies, I forgot that `mutate_at` was unnecessary. – Jake Kaupp Jan 16 '18 at 16:37
No need to apologize.. ;) Then could you enlighten me again? Much appreciated – S. Jay Jan 16 '18 at 16:40
Updated my answer. – Jake Kaupp Jan 16 '18 at 16:40
I applied the syntax, there was no error, but there is no difference made either. I still have weekends blanks.. – S. Jay Jan 16 '18 at 16:54
Are the weekends blanks ("") or NA values? `fill` works for NA values, not blanks "". If you change those to NA it should work. – Jake Kaupp Jan 16 '18 at 16:55
As you can see up there, it does not even have row. Nothing at all. That means no blanks I guess. It is not like there are cells but with NA value. There is just nothing. – S. Jay Jan 16 '18 at 16:59
You example doesn't include the empty cols, so it's difficult for me to figure out. If you `dput(DF1)`, edit your question and paste in the result that will help. – Jake Kaupp Jan 16 '18 at 17:01
Cols are all okay. Just Rows for weekends are none. It is quite huge data. dput(DF1) won't fit the size.. – S. Jay Jan 16 '18 at 17:14
`dput(head(DF1, 50))` – Jake Kaupp Jan 16 '18 at 17:18
dput is added in the original post. Thank you. – S. Jay Jan 16 '18 at 17:24
Can you try to include a dput that actually includes the empty rows? – Jake Kaupp Jan 16 '18 at 17:25
I am sorry. I do not follow you well. How can I includes rows that are not existing? – S. Jay Jan 16 '18 at 17:27
You have rows with missing values, can you please include a sample `dput` that includes the rows that contain missing values? – Jake Kaupp Jan 16 '18 at 17:28

Inserting value in NAs repetitively in order

1 Answers1