Assigning new value based on a condition in R-CodePudding

I have data frame like this one:

df<- data.frame(
  "Col1" = c("P1", "P1", "P1", "P2", "P2", "P2", "P3", "P3", "P3",
              "P3"),
  "Col2" = c("L", "L&R", "R", "V", "V&N", "N", "M", "I", "I&M",
             "I&M&G"),
  "Value" = c("20", "5", "75", "30", "7", "63", "10", "80", "2","8"))
df

What I want is to redefine the values based on the second column. I mean when I have L&R in the second column I want to divide its value by 2 (from the third column which equals to 5 in this case 5) and add this results to both L and R with in the same P1 group. So, L&R=5/2 will be 2.5. This 2.5 should be added to L in P1 group to be 22.5 and to R in P1 group to be 77.5. But in a case if I have tow && in the column 2 , I don't what it, so I want everything has && or more to be ignored. The final output should look like this:

df.output<- data.frame(
  "Col1" = c("P1",  "P1", "P2",  "P2", "P3", "P3"),
  "Col2" = c("L",  "R", "V",  "N", "M", "I" ),
  "Value" = c("22.5",  "77.5", "33.5",  "66.5", "11",  "81"))
df.output

> df.output
  Col1 Col2 Value
1   P1    L  22.5
2   P1    R  77.5
3   P2    V  33.5
4   P2    N  66.5
5   P3    M    11
6   P3    I    81

Can you help me please? Thank you very much.

CodePudding user response：

Try this solution:

library(dplyr)
library(stringr)
library(tidyr)

df %>%
  filter(!str_count(Col2, "&") > 1) %>%
  mutate(Value = ifelse(grepl("&", Col2), as.numeric(Value) / 2, as.numeric(Value))) %>%
  separate_rows(Col2, sep = "&") %>%
  group_by(Col1, Col2) %>%
  summarise(Value = sum(Value)) %>%
  ungroup()

# A tibble: 6 x 3
  Col1  Col2  Value
  <chr> <chr> <dbl>
1 P1    L      22.5
2 P1    R      77.5
3 P2    N      66.5
4 P2    V      33.5
5 P3    I      42  
6 P3    M      50