How can I extract negative values from an existing column into a new column-CodePudding

I have a dataframe (sy2.1) with a column (Vannstand2.cm) containing both positive and negative values of water level. I want to separate the negative and positive values into two new columns, whilst still keeping the original column. I have tried to mutate the negative values using this code:

sy2.1 %>% 
  group_by(Vannstand2.cm) %>% 
  mutate(Vannstand2_neg=case_when(Vannstand2.cm<0.0))

That didnt work, the error complained about negative values even though I specifically ask to only move everything below 0.0. I have also tried several other codes, but nothing seems to work..

Are there any other codes for this issue?

CodePudding user response：

either use if_else:

sy2.1 %>% 
  group_by(Vannstand2.cm) %>% 
  mutate(Vannstand2_neg = if_else(Vannstand2.cm < 0.0, 0, NA))

or with case_when

sy2.1 %>% 
  group_by(Vannstand2.cm) %>% 
  mutate(Vannstand2_neg = case_when(Vannstand2.cm < 0.0 ~ 0))

CodePudding user response：

Here's a solution using tidyr::separate:

sy2.1 %>% tidyr::separate(col = Vannstand2.cm, sep = "(?=-)", 
                   into = c("Vannstand2Positive","Vannstand2Negative"), 
                   convert = T, remove = F)

"(?=-)" separates the column by - and keeps it on the right column (see here for more), and remove = F keeps the original column.

An example with toy data:

df <- data.frame(col1 = -10:10)
tidyr::separate(df, col = col1, sep = "(?=-)", into = c("positive","negative"), 
         convert = T, remove = F)

Output

     a positive negative
1  -10       NA      -10
2   -9       NA       -9
3   -8       NA       -8
4   -7       NA       -7
5   -6       NA       -6
6   -5       NA       -5
7   -4       NA       -4
8   -3       NA       -3
9   -2       NA       -2
10  -1       NA       -1
11   0        0       NA
12   1        1       NA
13   2        2       NA
14   3        3       NA
15   4        4       NA
16   5        5       NA
17   6        6       NA
18   7        7       NA
19   8        8       NA
20   9        9       NA
21  10       10       NA