How to ignore missing values in dplyr without removing rows

CarrieH 0
Viewed 28

Note: The date format is DD.MM.

I have the following data frame containing stock price data:

df1 <- data.frame(date = c("01.01.", "02.01.", "03.01.", "04.01.", "05.01.", "06.01."),
                  A = c(102, 103, 107, NA, 120, 134),
                  B = c(94, NA, 95, 100, 93, 90),
                  C = c(55, 53, 50, 51, 48, 15))

In order to normalize these across time, I'm using the following formula: x-mean(x) / sd(x)

I have the following code to apply this function to all relevant columns:

df1 = df1 %>% 
  mutate(across(.cols = A:C,
                .f = function(x){(x-mean(x))/sd(x)}
                ))

However, for those columns that have at least one NA, it returns the entire column as NA. I've tried adding:

df1 = df1 %>% 
  mutate(across(.cols = A:C,
                .f = function(x){(x-mean(x))/sd(x)}
                ), na.rm  = TRUE)

But the same thing still happens. I don't want to use na.omit because I do need all the data available. How can I successfully apply the above function/one similar to it?

dplyr r
1 Answers

Add na.rm = TRUE inside the function e.g.

df1 %>% 
  mutate(across(.cols = A:C,
                .f = function(x){(x - mean(x, na.rm = TRUE))/sd(x, na.rm = TRUE)}
  ))

Output:

    date          A          B          C
1 01.01. -0.8196829 -0.1096817  0.6420708
2 02.01. -0.7464969         NA  0.5092286
3 03.01. -0.4537530  0.1645225  0.3099652
4 04.01.         NA  1.5355438  0.3763863
5 05.01.  0.4976646 -0.3838859  0.1771230
6 06.01.  1.5222682 -1.2064987 -2.0147739
userOHjX0
Related