How to get an entire column from a table with multiple columns on a condition that the column contai-CodePudding

I have a simple table in R which contains 3 columns (named 'countries_A', 'countries_B', and 'countries_C') each containing 4 countries. Now what I would like to do is write a function that searches in the table for a specific country, say "Italy", and then returns all the elements of the column where that country is in as a vector or list (with the exception of that specific country). So in my example here, as "Italy" in the column "countries_B" along with Sweden, Spain, and Switzerland, it means I would get a single vector containing all elements of column "countries_B" except Italy, meaning I would get "[Sweden, Spain, and Switzerland]" as an answer. I would really appreciate any help at all. And if possible, I would like the search to be vectorised if possible using library like dtplyr preferably. Below, I am attaching a screenshot of my table and the R script to generate that table. Many thanks in advance.

countries <- data.frame("countries_A" =
                          c('Belgium','Holland', 'France', 'Germany'), 
                      "countries_B" = c('Sweden','Italy','Spain','Switzerland'),
                     "countries_C"= c('England','Denmark','Portugal','Hungary'))

CodePudding user response：

Let me know if this helps you:

find.country <- function(df, country){
      
      df$ID <- seq.int(nrow(df)) 
      
      df1 <-df %>%
        pivot_longer(cols = -ID) %>%
        filter(value == {country})
      
      column.name <- df1$name
      
      df2 <- df %>%
        pivot_longer(cols = -ID) %>%
        filter(name == column.name) %>%
        filter(value != {country})
      
      vector <- as.vector(df2$value)
      return(vector)
      
      
    }
    
 find.country(df = countries, country = "Italy")


    [1] "Sweden"      "Spain"       "Switzerland"

CodePudding user response：

Not too elegant:

get_vector = function(df_countries, country){
column = countries %>% mutate(across(everything(),
                                     ~if_else(country %in% .x, cur_column(), NULL))) %>%
  mutate(var = coalesce(countries_A, countries_B, countries_C)) %>%
  select(var) %>% slice(1) %>% pull
vector = countries %>% select(column) %>% filter(.data[[column]] != country) %>% pull %>% as.vector
return(vector)
}

get_vector(countries, 'Sweden')

output:

> get_vector(countries, 'Sweden')
[1] "Italy"       "Spain"       "Switzerland"