3

I have a time series dataframe.

But that data contains NA like this: 

           KQ11.Open KQ11.High KQ11.Low KQ11.Close KQ11.Volume KQ11.Adjusted
2017-04-05    627.89    630.17   626.94     630.17      923700  630.17
2017-04-06    630.59    630.59   625.20     630.46      873400  630.46
2017-04-07    632.16    633.49   629.61     633.32      833200  633.32
2017-04-10    633.24    633.24   617.16     619.41      865600  619.41
2017-04-11    620.25    625.18   614.21     621.64      813400  622.64
2017-04-12    622.54    622.73   619.27     621.45      695600  621.45
2017-04-13    622.49    624.15   622.38     623.87      742500  623.87
2017-04-14        NA        NA       NA         NA          NA    NA

I want to change Na values to 618.24 618.24 618.24 618.24 742500 618.24

How can I change it? 

library(quantmod)
today <- Sys.Date()
kq <- getSymbols("^kq11", from = today-100, to = today, auto.assign = F)
kq
Commaeng
  • 75
  • 1
  • 2
  • 10
  • 2
    Possible duplicate of [How do I replace NA values with zeros in an R dataframe?](https://stackoverflow.com/questions/8161836/how-do-i-replace-na-values-with-zeros-in-an-r-dataframe) – Val Jul 14 '17 at 11:17
  • 1
    I don't think it's a duplicate. The questions wants to replace `NA`s with different values depending on column rather than zero throughout. – Dan Jul 14 '17 at 11:23
  • What are these numbers? `c(618.24, 618.24, 618.24, 618.24, 742500, 618.24` mean, meadian? Maybe use [fill na with mean](https://stackoverflow.com/questions/25835643/replacing-missing-values-in-r-with-column-mean)? – zx8754 Jul 14 '17 at 11:43
  • @zx8754 I only obtain 20170414's Close price. So what I fill that data is unavoidable. I decided to fill OHLC to Close price. – Commaeng Jul 14 '17 at 12:25

3 Answers3

3

Edited based on the comment by lyngbakr below.

In this specific case I would probably use

df$KQ11.Volume[is.na(df$KQ11.Volume)] = 742500
df[is.na(df)] = 618.24

But that only works in this specific case. OP, is there any specific logic to the numbers you want to use as replacement?

Florian
  • 24,425
  • 4
  • 49
  • 80
3

If you only want to change the NAs to your sequence:

df <- read.table(text="date KQ11.Open KQ11.High KQ11.Low KQ11.Close KQ11.Volume KQ11.Adj
2017-04-05    627.89    630.17   626.94     630.17      923700  630.17
2017-04-06    630.59    630.59   625.20     630.46      873400  630.46
2017-04-07    632.16    633.49   629.61     633.32      833200  633.32
2017-04-10    633.24    633.24   617.16     619.41      865600  619.41
2017-04-11    620.25    625.18   614.21     621.64      813400  622.64
2017-04-12    622.54    622.73   619.27     621.45      695600  621.45
2017-04-13    622.49    624.15   622.38     623.87      742500  623.87
2017-04-14        NA        NA       NA         NA          NA    NA",
                 header=TRUE,stringsAsFactors=FALSE)    

df[is.na(df)] <- c(618.24, 618.24, 618.24, 618.24, 742500, 618.24)

        date KQ11.Open KQ11.High KQ11.Low KQ11.Close KQ11.Volume KQ11.Adjusted
1 2017-04-05    627.89    630.17   626.94     630.17      923700        630.17
2 2017-04-06    630.59    630.59   625.20     630.46      873400        630.46
3 2017-04-07    632.16    633.49   629.61     633.32      833200        633.32
4 2017-04-10    633.24    633.24   617.16     619.41      865600        619.41
5 2017-04-11    620.25    625.18   614.21     621.64      813400        622.64
6 2017-04-12    622.54    622.73   619.27     621.45      695600        621.45
7 2017-04-13    622.49    624.15   622.38     623.87      742500        623.87
8 2017-04-14    618.24    618.24   618.24     618.24      742500        618.24

Note: If you don't want to have to hardcode all the NA replacement, you should look at functions na.locf and na.approx from package zoo. It might be handy.

Pierre Lapointe
  • 16,017
  • 2
  • 43
  • 56
  • Thank you for your help. and I learned na,locf, na.approx. Sorry to bother you, I have another question over seeing your answer. If dataframe contains plural Na rows(contain NA data), Can I allocate specific values to replace them? – Commaeng Jul 14 '17 at 12:41
2
df <- read.table(text="KQ11.Open KQ11.High KQ11.Low KQ11.Close KQ11.Volume KQ11.Adjusted
        2017-04-05    627.89    630.17   626.94     630.17      923700  630.17
        2017-04-06    630.59    630.59   625.20     630.46      873400  630.46
        2017-04-07    632.16    633.49   629.61     633.32      833200  633.32
        2017-04-10    633.24    633.24   617.16     619.41      865600  619.41
        2017-04-11    620.25    625.18   614.21     621.64      813400  622.64
        2017-04-12    622.54    622.73   619.27     621.45      695600  621.45
        2017-04-13    622.49    624.15   622.38     623.87      742500  623.87
        2017-04-14        NA        NA       NA         NA          NA    NA",
             header=TRUE, row.names = 1, stringsAsFactors=FALSE)

Assuming, you need to replace all the NA values within a column with a constant value of dataframe df as:

df %>% replace_na(list(KQ11.Open =  618.24, KQ11.High = 618.24,
                          KQ11.Low = 618.24, KQ11.Close = 618.24, 
                          KQ11.Volume = 742500, KQ11.Adjusted = 618.24))

Output:

#            KQ11.Open KQ11.High KQ11.Low KQ11.Close KQ11.Volume KQ11.Adjusted
# 2017-04-05    627.89    630.17   626.94     630.17      923700        630.17
# 2017-04-06    630.59    630.59   625.20     630.46      873400        630.46
# 2017-04-07    632.16    633.49   629.61     633.32      833200        633.32
# 2017-04-10    633.24    633.24   617.16     619.41      865600        619.41
# 2017-04-11    620.25    625.18   614.21     621.64      813400        622.64
# 2017-04-12    622.54    622.73   619.27     621.45      695600        621.45
# 2017-04-13    622.49    624.15   622.38     623.87      742500        623.87
# 2017-04-14    618.24    618.24   618.24     618.24      742500        618.24
Prradep
  • 5,506
  • 5
  • 43
  • 84