Compare several tables and create a new one that shows which variables match using R

user_15482017 0
Viewed 41

I am new to R. To practice I am trying to create a table that shows which variables match after comparing about 50 tables. If the columns match, I would like to see a "Yes" in the cell. Otherwise a "No". I would appreciate any hints on how to possibly solve this.

My input data looks like this:

Tables Variables
tabla_1 A
tabla_1 Z
tabla_1 Y
tabla_1 V
tabla_1 B
tabla_2 H
tabla_2 B
tabla_2 A
tabla_2 U
tabla_3 U
tabla_3 S
tabla_3 M
tabla_4 U
tabla_4 A
tabla_4 B
tabla_4 V
tabla_4 Q
tabla_4 O
tabla_4 F

I am trying to get this:

Variables tabla_1 tabla_2 tabla_3 tabla_4
A Yes Yes No Yes
Z Yes No No No
Y Yes No No No
V Yes No No Yes
B No Yes No Yes
H No Yes No No
U No Yes Yes Yes
S No Yes Yes No
M No No Yes No
Q No No No Yes
O No No No Yes
F No No No Yes

Thanks for any help.

data-wrangling tidyr dplyr r
3 Answers

By distinct() and pivor_wider()

df %>%
  distinct(Variables, Tables) %>%
  mutate(n = "Yes") %>%
  pivot_wider(names_from = Tables, values_from = n, values_fill = list(n = "No"))

   Variables tabla_1 tabla_2 tabla_3 tabla_4
   <chr>       <dbl>   <dbl>   <dbl>   <dbl>
 1 A               1       1       0       1
 2 Z               1       0       0       0
 3 Y               1       0       0       0
 4 V               1       0       0       1
 5 B               1       1       0       1
 6 H               0       1       0       0
 7 U               0       1       1       1
 8 S               0       0       1       0
 9 M               0       0       1       0
10 Q               0       0       0       1
11 O               0       0       0       1
12 F               0       0       0       1
user_167291750

We may create a column of 'Yes' and use pivot_wider. Then, in the values_fill, specify the 'No' value (by default, it will be NA)

library(dplyr)
library(tidyr)
df1 %>%
    mutate(new = 'Yes') %>%
    pivot_wider(names_from = Tables, values_from = new, values_fill = 'No')

-output

# A tibble: 12 x 5
   Variables tabla_1 tabla_2 tabla_3 tabla_4
   <chr>     <chr>   <chr>   <chr>   <chr>  
 1 A         Yes     Yes     No      Yes    
 2 Z         Yes     No      No      No     
 3 Y         Yes     No      No      No     
 4 V         Yes     No      No      Yes    
 5 B         Yes     Yes     No      Yes    
 6 H         No      Yes     No      No     
 7 U         No      Yes     Yes     Yes    
 8 S         No      No      Yes     No     
 9 M         No      No      Yes     No     
10 Q         No      No      No      Yes    
11 O         No      No      No      Yes    
12 F         No      No      No      Yes

data

df1 <- structure(list(Tables = c("tabla_1", "tabla_1", "tabla_1", "tabla_1", 
"tabla_1", "tabla_2", "tabla_2", "tabla_2", "tabla_2", "tabla_3", 
"tabla_3", "tabla_3", "tabla_4", "tabla_4", "tabla_4", "tabla_4", 
"tabla_4", "tabla_4", "tabla_4"), Variables = c("A", "Z", "Y", 
"V", "B", "H", "B", "A", "U", "U", "S", "M", "U", "A", "B", "V", 
"Q", "O", "F")), class = "data.frame", row.names = c(NA, -19L
))
user_37322710

You can use table which will return 1/0 values instead of 'Yes'/'No'.

table(rev(df))

#   Tables
#Variables tabla_1 tabla_2 tabla_3 tabla_4
#        A       1       1       0       1
#        B       1       1       0       1
#        F       0       0       0       1
#        H       0       1       0       0
#        M       0       0       1       0
#        O       0       0       0       1
#        Q       0       0       0       1
#        S       0       0       1       0
#        U       0       1       1       1
#        V       1       0       0       1
#        Y       1       0       0       0
#        Z       1       0       0       0

To get 'Yes'/'No' values you can do -

tab <- table(rev(df))
tab <- ifelse(tab == 1, 'Yes', 'No')
user_39629140
Related