comparing the variables and their values between two data frames

General
,
1

I have two data frames with same kind of data, now i want to check for all the columns in both data frames have same kind of text in all columns in both data frames .

so for example the column name "sales executives" in both data frames have exact name "Micheal klay" in both data frames but if there is any spelling error or extra space i want to show it as not matching.

I have tried below approach and its working for small database but because my data is very big, data having approx 10 - 40 millions or records so its showing error do we have any solution or any other approach to do that

cannot allocate vector of size 3.2GB

library(tidyverse)
df1 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","NY","OD","CA","OD"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2),emial=c("dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK"),
                  mkl=c("m","f","m","m","f","m","m","f","m","m","f","m","m"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man"),wond=c("tko","kent","bho","kilt","kent","bho","kent","bho","bho","kilt","kent","bho","kilt"))

df2 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8,8,6),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","ny","OD","CA","OD","NY","OL"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani","juna","mau"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2,2,1),emial=c("dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low","High","High"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK","CHI","JYP"),
                  mkl=c("male","female","male","male","female","male","male","female","male","male","female","male","male","female","male"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car","Bus","Bus"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man","jr","man"),wond=c("tko","kent","bho","kilt","kent","bho","kent","bho","bho","kilt","kent","bho","kilt","kent","bho"))

df1_long <- df1 %>% 
  as_tibble() %>% 
  mutate_if(is.double, as.character) %>% distinct() %>% 
  pivot_longer(everything(), names_to = "Names", values_to = "options") %>%   
  arrange(Names, options)

df2_long <- df2 %>% 
  as_tibble() %>% 
  mutate_if(is.double, as.character) %>% distinct() %>% 
  pivot_longer(everything(), names_to = "Names", values_to = "options") %>%   
  arrange(Names, options)

T1 <- df1_long %>% 
  full_join(df2_long, by=c("Names", "options"), keep = TRUE) %>% 
  distinct(Names.x, options.x, Names.y, options.y) %>% 
  arrange(Names.x, Names.y,  options.x, options.y) %>% 
  mutate(
    consistant_names = !is.na(Names.x) & !is.na(Names.y),
    consistant_options = !is.na(options.x) & !is.na(options.y)
  )

the output required like below

image
image700×1596 23.3 KB

below are inconsistency between data bases

image
image700×320 5.3 KB

2

Hello,

I think indeed if you have that large a data frame, it's better to run the analysis column by column. There is also no need to join the whole dataset, as you are only looking to compare unique values. With this in mind, you can do something like this:

library(tidyverse)

df1 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","NY","OD","CA","OD"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2),emial=c("dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK"),
                  mkl=c("m","f","m","m","f","m","m","f","m","m","f","m","m"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man"),wond=c("tko","kent","bho","kilt","kent","bho","kent","bho","bho","kilt","kent","bho","kilt"))

df2 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8,8,6),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","ny","OD","CA","OD","NY","OL"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani","juna","mau"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2,2,1),emial=c("dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low","High","High"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK","CHI","JYP"),
                  mkl=c("male","female","male","male","female","male","male","female","male","male","female","male","male","female","male"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car","Bus","Bus"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man","jr","man"),wond=c("tko","kent","bho","kilt","kent","bho","kent","bho","bho","kilt","kent","bho","kilt","kent","bho"))

#Compare all columns one by one
comparison = map_df(colnames(df1), function(colName){
  
  #Get the unique values for the column in df1
  data.frame(
    col = colName, 
    val = as.character(unique(df1[,colName])), 
    match1 = T) %>% 
    #Join with the unique values for the column in df2
    full_join(
      data.frame(
        col = colName,
        val = as.character(unique(df2[,colName])), 
        match2 = T), 
    by = c("col", "val"))
  
}) %>% 
  mutate(match1 = replace_na(match1, F), match2 = replace_na(match2, F))

comparison
#>      col    val match1 match2
#> 1    MAN      6   TRUE   TRUE
#> 2    MAN      4   TRUE   TRUE
#> 3    MAN      8   TRUE   TRUE
#> 4   MANi     OD   TRUE   TRUE
#> 5   MANi     NY   TRUE   TRUE
#> 6   MANi     CA   TRUE   TRUE
#> 7   MANi     OL   TRUE   TRUE
#> 8   MANi     ny  FALSE   TRUE
#> 9   nune   akas   TRUE   TRUE
#> 10  nune   mani   TRUE   TRUE
#> 11  nune   juna   TRUE   TRUE
#> 12  nune    mau   TRUE   TRUE
#> 13  nune    nuh   TRUE   TRUE
#> 14  nune    kil   TRUE   TRUE
#> 15  nune   kman   TRUE   TRUE
#> 16  nune   nuha   TRUE   TRUE
#> 17  nune   huna   TRUE   TRUE
#> 18  klay      1   TRUE   TRUE
#> 19  klay      2   TRUE   TRUE
#> 20 emial     dd   TRUE   TRUE
#> 21 emial    xyz   TRUE   TRUE
#> 22 emial    abc   TRUE  FALSE
#> 23 emial    ABC  FALSE   TRUE
#> 24  Pass    Low   TRUE   TRUE
#> 25  Pass   High   TRUE   TRUE
#> 26   fri    KKK   TRUE   TRUE
#> 27   fri    USA   TRUE   TRUE
#> 28   fri    IND   TRUE   TRUE
#> 29   fri    SRI   TRUE   TRUE
#> 30   fri    PAK   TRUE   TRUE
#> 31   fri    CHI   TRUE   TRUE
#> 32   fri    JYP   TRUE   TRUE
#> 33   fri    TGA   TRUE   TRUE
#> 34   mkl      m   TRUE  FALSE
#> 35   mkl      f   TRUE  FALSE
#> 36   mkl   male  FALSE   TRUE
#> 37   mkl female  FALSE   TRUE
#> 38   kin   Sent   TRUE   TRUE
#> 39   kin    Rec   TRUE   TRUE
#> 40  munc    Car   TRUE   TRUE
#> 41  munc    Bus   TRUE   TRUE
#> 42  munc   Truk   TRUE   TRUE
#> 43  munc    Cyl   TRUE   TRUE
#> 44  lone     Sr   TRUE   TRUE
#> 45  lone    jun   TRUE   TRUE
#> 46  lone     sr   TRUE   TRUE
#> 47  lone    man   TRUE   TRUE
#> 48  lone     jr   TRUE   TRUE
#> 49  wond    tko   TRUE   TRUE
#> 50  wond   kent   TRUE   TRUE
#> 51  wond    bho   TRUE   TRUE
#> 52  wond   kilt   TRUE   TRUE

Created on 2022-06-03 by the reprex package (v2.0.1)

Hope this helps,
PJ

3

This is also the great idea but the out required should be
df1 is old data frame and df2 is new data frame. also i just want to display the unmatched records. this is just for a person who doesn't have any knowledge of R.

Column_name_df1| Column_name_df2| Option_df1 | Option_df2 | Column_name_matching | Options_Matching

4

Hi,

Here is an updated version. I changed some column names to simulate the result when there is no match between columns.

library(tidyverse)

df1 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","NY","OD","CA","OD"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2),emial=c("dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd","xyz","abc","dd"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK"),
                  mkl=c("m","f","m","m","f","m","m","f","m","m","f","m","m"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man"),wond=c("tko","kent","bho","kilt","kent","bho","kent","bho","bho","kilt","kent","bho","kilt"))

df2 <- data.frame(MAN=c(6,6,4,6,8,6,8,4,4,6,6,8,8,8,6),MANi=c("OD","NY","CA","CA","OD","CA","OD","NY","OL","ny","OD","CA","OD","NY","OL"),
                  nune=c("akas","mani","juna","mau","nuh","kil","kman","nuha","huna","kman","nuha","huna","mani","juna","mau"),
                  klay=c(1,2,2,1,1,2,1,2,1,2,1,1,2,2,1),emial=c("dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC","dd","xyz","ABC"),Pass=c("Low","High","Low","Low","High","Low","High","High","Low","High","High","High","Low","High","High"),fri=c("KKK","USA","IND","SRI","PAK","CHI","JYP","TGA","KKK","USA","IND","SRI","PAK","CHI","JYP"),
                  mkl=c("male","female","male","male","female","male","male","female","male","male","female","male","male","female","male"),kin=c("Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Sent","Rec","Rec","Sent","Rec","Sent","Rec"),munc=c("Car","Bus","Truk","Cyl","Bus","Car","Bus","Bus","Bus","Car","Car","Cyl","Car","Bus","Bus"),
                  lone=c("Sr","jun","sr","jun","man","man","jr","Sr","jun","sr","jun","man","man","jr","man"),xyz = "A")

#Compare all columns one by one
comparison = map_df(colnames(df1)[colnames(df1) %in% colnames(df2)], function(colName){
  
  #Get the unique values for the column in df1
  data.frame(
    col = colName, 
    val = as.character(unique(df1[,colName])), 
    match1 = T) %>% 
    #Join with the unique values for the column in df2
    full_join(
      data.frame(
        col = colName,
        val = as.character(unique(df2[,colName])), 
        match2 = T), 
    by = c("col", "val"))
  
}) %>% 
  mutate(match1 = replace_na(match1, F), match2 = replace_na(match2, F))

comparison = comparison %>% filter(match1 == F | match2 == F) %>% 
  mutate(option_df1 = ifelse(match1, val, NA),
         option_df2 = ifelse(match2, val, NA),
         Column_name_matching = TRUE, Options_Matching = FALSE) %>% 
  select(Column_name_df1 = col, Column_name_df2 = col,option_df1, option_df2,
         Column_name_matching, Options_Matching)

if(any(!colnames(df1) %in% colnames(df2))){
  comparison = bind_rows(
    comparison,
    df1 %>% select(colnames(df1)[!colnames(df1) %in% colnames(df2)]) %>% 
      distinct() %>% 
      pivot_longer(everything(), names_to = "Column_name_df1", 
                   values_to = "option_df1") %>% 
      mutate(Column_name_df2 = NA, option_df2 = NA, 
             Column_name_matching = FALSE, Options_Matching = FALSE)
  )
}


if(any(!colnames(df2) %in% colnames(df1))){
  comparison = bind_rows(
    comparison,
    df2 %>% select(colnames(df2)[!colnames(df2) %in% colnames(df1)]) %>% 
      distinct() %>% 
      pivot_longer(everything(), names_to = "Column_name_df2", 
                   values_to = "option_df2") %>% 
      mutate(Column_name_df1 = NA, option_df1 = NA, 
             Column_name_matching = FALSE, Options_Matching = FALSE)
  )
}

comparison
#>    Column_name_df1 Column_name_df2 option_df1 option_df2 Column_name_matching
#> 1             MANi            MANi       <NA>         ny                 TRUE
#> 2            emial           emial        abc       <NA>                 TRUE
#> 3            emial           emial       <NA>        ABC                 TRUE
#> 4              mkl             mkl          m       <NA>                 TRUE
#> 5              mkl             mkl          f       <NA>                 TRUE
#> 6              mkl             mkl       <NA>       male                 TRUE
#> 7              mkl             mkl       <NA>     female                 TRUE
#> 8             wond            <NA>        tko       <NA>                FALSE
#> 9             wond            <NA>       kent       <NA>                FALSE
#> 10            wond            <NA>        bho       <NA>                FALSE
#> 11            wond            <NA>       kilt       <NA>                FALSE
#> 12            <NA>             xyz       <NA>          A                FALSE
#>    Options_Matching
#> 1             FALSE
#> 2             FALSE
#> 3             FALSE
#> 4             FALSE
#> 5             FALSE
#> 6             FALSE
#> 7             FALSE
#> 8             FALSE
#> 9             FALSE
#> 10            FALSE
#> 11            FALSE
#> 12            FALSE

Created on 2022-06-03 by the reprex package (v2.0.1)

Hope this helps,
PJ

5

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.