Add element from column if duplicate in another column by groups in R-CodePudding

I have a dataframe such as :

Groups Element 
G1     E1
G2     E1
G1     E2
G1     E3
G1     E4
G1     E5
G2     E5
G2     E5
G2     E6
G1     E6
G1     E7

and I would like to create a new column called Element2 for G1 and G2 groups, which will have the Element value only if this element is in G1 and G2 group.

I should then get:

Groups Element Element2
G1     E1      E1
G2     E1      E1
G1     E2      NA
G1     E3      NA
G1     E4      NA
G1     E5      E5
G2     E5      E5
G2     E5      E5
G2     E6      E6
G1     E6      E6
G1     E7      NA

Here is the dput format of the table if it can helps:

structure(list(Groups.Element = c("G1     E1", "G2     E1", "G1     E2", 
"G1     E3", "G1     E4", "G1     E5", "G2     E5", "G2     E5", 
"G2     E6", "G1     E6", "G1     E7")), class = "data.frame", row.names = c(NA, 
-11L))

CodePudding user response：

You can use match on the unique elements of group g2.

. <- unique(x$Element[x$Groups=="G2"])
x$Element2 <- .[match(x$Element, .)]
#   Groups Element Element2
#1      G1      E1       E1
#2      G2      E1       E1
#3      G1      E2     <NA>
#4      G1      E3     <NA>
#5      G1      E4     <NA>
#6      G1      E5       E5
#7      G2      E5       E5
#8      G2      E5       E5
#9      G2      E6       E6
#10     G1      E6       E6
#11     G1      E7     <NA>

Data:

x <- read.table(header=TRUE, text="Groups Element 
G1     E1
G2     E1
G1     E2
G1     E3
G1     E4
G1     E5
G2     E5
G2     E5
G2     E6
G1     E6
G1     E7")

CodePudding user response：

Using intersect and case_when

library(dplyr)
u <- Reduce(intersect, split(dat$Element, dat$Groups))

dat %>% 
  mutate(Element2 = case_when(Element %in% u ~ Element))

   Groups Element Element2
1      G1      E1       E1
2      G2      E1       E1
3      G1      E2     <NA>
4      G1      E3     <NA>
5      G1      E4     <NA>
6      G1      E5       E5
7      G2      E5       E5
8      G2      E5       E5
9      G2      E6       E6
10     G1      E6       E6
11     G1      E7     <NA>

CodePudding user response：

You can try the code below

transform(
    df,
    Element2 = Element[ave(match(Groups, Groups), Element, FUN = var) > 0]
)

which gives

   Groups Element Element2
1      G1      E1       E1
2      G2      E1       E1
3      G1      E2     <NA>
4      G1      E3     <NA>
5      G1      E4     <NA>
6      G1      E5       E5
7      G2      E5       E5
8      G2      E5       E5
9      G2      E6       E6
10     G1      E6       E6
11     G1      E7     <NA>