Create a new variable based on multiple complex conditions in R-CodePudding

Below is a reproducible example. This is based on how different countries vote at the United Nations General Assembly.

Say we have three columns titled "VoteID", "Country", and "Vote" :

VoteID <- c("Vote1","Vote1","Vote1",
            "Vote2","Vote2","Vote2")
Country <- c("CountryA", "CountryB", "CountryC", 
             "CountryA", "CountryB", "CountryC")
Vote <- c("Yes", "Yes", "No",
          "No", "No", "Yes")

df <- data.frame(VoteID, Country, Vote)

df

The dataframe looks like this:

   VoteID   Country      Vote
   Vote1    CountryA     Yes
   Vote1    CountryB     Yes
   Vote1    CountryC     No
   Vote2    CountryA     No
   Vote2    CountryB     No
   Vote2    CountryC     Yes

I would like to create a new variable called CountryCVote which represents how Country C voted, but for each vote id.

That is, for each vote id, there should be a 'Vote' column that represents how each country voted, and a "CountryCVote" column that represents how CountryC voted for that Vote ID. The resulting dataframe with the new variable should look like this:

  VoteID   Country     Vote   CountryCVote
  Vote1    CountryA    Yes    No
  Vote1    CountryB    Yes    No 
  Vote1    CountryC    No     No
  Vote2    CountryA    No     Yes
  Vote2    CountryB    No     Yes
  Vote2    CountryC    Yes    Yes

Any help is appreciated. Thank you!

CodePudding user response：

You may try

library(dplyr)
df %>%
  group_by(VoteID) %>%
  mutate(key = ifelse(any(Vote =="Yes" & Country == "CountryC"), "Yes", "No"))

  VoteID Country  Vote  key  
  <chr>  <chr>    <chr> <chr>
1 Vote1  CountryA Yes   No   
2 Vote1  CountryB Yes   No   
3 Vote1  CountryC No    No   
4 Vote2  CountryA No    Yes  
5 Vote2  CountryB No    Yes  
6 Vote2  CountryC Yes   Yes

CodePudding user response：

data.table::setDT(df)[
  df[Country=="CountryC",.(VoteID, CountryCVote=Vote)],
  on=.(VoteID)]

Output:

   VoteID  Country Vote CountryCVote
1:  Vote1 CountryA  Yes           No
2:  Vote1 CountryB  Yes           No
3:  Vote1 CountryC   No           No
4:  Vote2 CountryA   No          Yes
5:  Vote2 CountryB   No          Yes
6:  Vote2 CountryC  Yes          Yes