Separate numeric column by sign-CodePudding

I wonder, if there is a more straightforward solution to the following problem. I want to separate the values in numeric columns by sign and a condition related to a categorical column.

If this is my data frame:

library(tidyverse)

a <- tribble(
  ~category , ~val ,
  'A' , 1 ,
  'A' , -1 ,
  'B' , -2 ,
  'C' , 1 ,
  'Z' , 3 ,
  'Z' , -4
)

I want to create four new columns with positive and negativ values, if the category != 'Z' and positive and negative values if category == 'Z.

This code produces the desired result:

a %>%
  mutate(good = if_else(category != 'Z' & val >= 0 , val , 0) ,
         bad = if_else(category != 'Z' & val < 0 , val , 0) ,
         reserve = if_else(category == 'Z' & val >= 0 , val , 0) , 
         risk = if_else(category == 'Z' & val < 0 , val , 0))

But as I have many category and value columns and a lot of rules to separate them, I would end up with a serious amount of if-else-conditions. So, does somebody know a more straightforward solution?

CodePudding user response：

Similar idea to the previous answer. Map out categories by condition and then pivot wider. Here I show you how to index categories using logical statements transformed into numeric indices.

library(tidyverse)

a |>
  mutate(grp = c("bad", "good", "risk", "reserve")[
    (val>0)   (category == "Z")    (val<0 & category == "Z")   (val>0 & category == "Z")  1
    ],
    id = row_number()) |>
  pivot_wider(names_from = grp, values_from = val, values_fill = 0) |>
  select(-id)
#> # A tibble: 6 x 5
#>   category  good   bad reserve  risk
#>   <chr>    <dbl> <dbl>   <dbl> <dbl>
#> 1 A            1     0       0     0
#> 2 A            0    -1       0     0
#> 3 B            0    -2       0     0
#> 4 C            1     0       0     0
#> 5 Z            0     0       3     0
#> 6 Z            0     0       0    -4

CodePudding user response：

I would go with something like this if you can ditch the val columns:

library(tidyverse)

a <- tribble(
  ~category , ~val ,
  'A' , 1 ,
  'A' , -1 ,
  'B' , -2 ,
  'C' , 1 ,
  'Z' , 3 ,
  'Z' , -4
)

a2 <- a %>%
  mutate(
    condition = case_when(
      category != 'Z' & val >= 0 ~ 'good',
      category != 'Z' & val < 0 ~ 'bad',
      category == 'Z' & val >= 0 ~ 'reserve',
      category == 'Z' & val < 0 ~'risk'
    )
  )

a2 %>%
  rownames_to_column(".flag.row") %>%
  pivot_wider(
    names_from = 'condition',
    values_from = 'val',
    values_fill = 0
  ) %>%
  select(-.flag.row)