Home > Mobile >  compare columns from different tables R
compare columns from different tables R

Time:02-22

I have two tibbles:

df1=tibble(id = 1:3,
       one = c(TRUE, FALSE, FALSE),
       two = c(TRUE, TRUE, FALSE))
df2=tibble(id = 1:3,
       one = c(TRUE, FALSE, TRUE),
       two = c( TRUE, FALSE, FALSE))

I want to perform a comparison of the same columns in both tibbles using & to get:

tibble(id = 1:3,
       one_comp = c(TRUE, FALSE, FALSE),
       two_com = c(TRUE, FALSE, FALSE))

I can compare single column with single column and combine at the end ie df1$two & df2$two , am sure there is a much better way, especially as I have >100 columns

any suggestions

Here are the tibbles and the result

enter image description here enter image description hereenter image description here

CodePudding user response:

@sbarbit's answer looks good-- here's another option

This assumes that your first field is not relevant (currently "id") and that all columns that follow are compared (2:2, 3:3... etc) This will work with any number of columns. It uses the libraries purrr and dplyr. When you call tidyverse it calls them both.

library(tidyverse)

df3 <- map_dfc(2:ncol(df1),              # skip the first column (ids)
               .f = function(x){
                 df1[, x] == df2[, x]    # create a vector of comparisons
               }) %>% 
  setNames(paste0("Cols_", 2:ncol(df1))) # names based on col compared

CodePudding user response:

I am assuming that you need to join the tables by id, otherwise simpler solutions are available.

library(dplyr)
library(tidyr)

pivot_longer(df1, 
             cols = -id,
             names_to = "colname", 
             values_to = "value_1") |>
  inner_join(pivot_longer(df2, 
                          cols = -id, 
                          names_to = "colname", 
                          values_to = "value_2"),
             by = c("id", "colname")) |>
  mutate(value = value_1 & value_2, .keep = "unused") |>
  pivot_wider(names_from = colname, values_from = value) |>
  rename_with(\(x) gsub("^", "comp_", x), !id)

  # A tibble: 3 × 3
     id comp_one comp_two
  <int> <lgl>    <lgl>   
1     1 TRUE     TRUE    
2     2 FALSE    FALSE   
3     3 FALSE    FALSE   

CodePudding user response:

We may just do an & comparison on equal sized datasets

cbind(df1[1], df1[-1] & df2[-1])

-output

 id   one   two
1  1  TRUE  TRUE
2  2 FALSE FALSE
3  3 FALSE FALSE
  •  Tags:  
  • r
  • Related