Compare column types between two tables

Question

If I have two tables (File1) and (File2)

> dput(File1)
structure(list(Column.1 = structure(1:3, .Label = c("Row 1", 
"Row 2", "Row 3"), class = "factor"), Column.2 = c(NA, NA, NA
), Column.3 = c(NA, NA, NA), colNames = c(TRUE, TRUE, TRUE)), class = "data.frame", row.names = c(NA, 
-3L))
> dput(File2)
structure(list(Column.1 = structure(1:3, .Label = c("Row 1", 
"Row 2", "Row 3"), class = "factor"), Column.2 = c(1, 2, 34), 
    Column.3 = c(NA, NA, NA), colNames = c(TRUE, TRUE, TRUE)), class = "data.frame", row.names = c(NA, 
-3L))

and I want to confirm that the Column Names, Column Types, and Number of Rows and Columns between File 1 and File 2, returning a TRUE if they are all the same and a FALSE if not, how can I add on to this code that I have written?

I tried some of the answers in Compare column types between two data frames, but I am only looking for a TRUE or FALSE answer. Here's my current code.

check_file <- function(File1 , File2) {
  if (!nrow(File1) == nrow(File2)) {
    print("Non matching number of rows")
    return(FALSE)

  } else if (!ncol(File1) == ncol(File2)) {
    print("non matching number of columns")
    return(FALSE)
  } else if (length(grep("FALSE", names(File1) == names(File2)))>0){
    print("Non matching names of columns")
    return(FALSE)
  }else if (!class(File1)==class(File2)){
      print("Non matching column types")
    }
  return(TRUE)
}


check <- check_file(File1, File2)


if (check) {
  return(TRUE)
} else{
  return(FALSE)
}

I think all that´s left is the types. For example, in the dput File 2 Column 2 has numbers while File 1 has NA. They don´t have to be the same numbers, but it needs to return false since it´s NA. If File 1 had 3,2,564, it should return TRUE.

utubun · Answer 1 · 2018-06-20T15:05:16.623

First solution:

all(                                                 # check if all ar T
  sapply(                                            # 
    c(colnames, dim, function(x){sapply(x, class)}), # functions to apply
    function(f) all(f(File1) == f(File2))            # check 4 equality  
    )                                                #
  )                                                  #
[1] FALSE                                            # numeric != logical
#all(              
#  sapply(
#    c(colnames, dim, function(x){sapply(x, class)}), 
#    function(f) all(f(File1) == f(File1))
#    )
#  )
#[1] TRUE

[EDIT 0] dim instead of nrow. [EDIT 1]

Second solution

In case when two columns have different classes, but one is empty - return TRUE:

Datasets

df1 <- data.frame(Column1 = paste("Row", 1:3), Column2 = 1:3,
                  Column3 = NA, colNames = TRUE)

df2 <- df1; df2[, 2] <- c(1, 2, 34)

df3 <- data.frame(Column1 = paste("Row", 1:3), Column2 = NA, Column3 = NA)

df4 <- df3

df4[, 2] <- "ddd"
df4[, 3] <- c(3, 4, 2)

df1
#  Column1 Column2 Column3 colNames
#1   Row 1       1      NA     TRUE
#2   Row 2       2      NA     TRUE
#3   Row 3       3      NA     TRUE

df2
#  Column1 Column2 Column3 colNames
#1   Row 1       1      NA     TRUE
#2   Row 2       2      NA     TRUE
#3   Row 3      34      NA     TRUE

note that class(df1[,2]) == "integer" but class(df2[,2]) == "numeric"

df3
#  Column1 Column2 Column3
#1   Row 1      NA      NA
#2   Row 2      NA      NA
#3   Row 3      NA      NA

df4
#  Column1 Column2 Column3
#1   Row 1     ddd       3
#2   Row 2     ddd       4
#3   Row 3     ddd       2

Function definition

identical_df <- function(x, y){
    ifelse(!identical(colnames(x), colnames(y)), FALSE,
           ifelse(!identical(dim(x), dim(y)), FALSE,
                  all((sapply(x, class) == sapply(y, class)) |
                      (apply(is.na(x), 2, prod) | apply(is.na(y), 2, prod))
                     )
                  )
           )
}

Test the function on `df1`, `df2`; `df1`, `df3`; `df3`, `df4`

identical_df(df1, df1) # identical 
#[1] TRUE              #
identical_df(df1, df2) # class(df1[,2]) != class(df2[,2])
#[1] FALSE
identical_df(df1, df3) # dim(df1) != dim(df3)
#[1] FALSE
identical_df(df3, df4) # different classes for cols 2, 3
#[1] TRUE              # however both cols 2, 3 in df3 are empty (NAs)
# ==============================================================================
# Evaluation of 
# all((sapply(x, class) == sapply(y, class)) |
#     (apply(is.na(x), 2, prod) | apply(is.na(y), 2, prod))
# )
# for x = df3, y = df4
#
# +-------------------------------------------------+--------+--------+--------+
# |Expression                                       |Column1 |Column2 |Column3 |
# +-------------------------------------------------+--------+--------+--------+
# |sapply(x, class) == sapply(y, class)   +--------<|TRUE    |FALSE   |FALSE   |
# +                                       |         +--------+--------+--------+
# |apply(is.na(x), 2, prod)               |     +--<|0       |1       |1       |
# +                                       OR-+<OR   |        |        |        |
# |apply(is.na(y), 2, prod)               |  |  +--<|0       |0       |0       |
# |                                       |  |      |        |        |        |
# |                                       |  +----->|FALSE   |TRUE    |TRUE    |
# |                                       |         |        |        |        |
# |                                       |         +--------+--------+--------+
# |                                       +-------->|TRUE    |TRUE    |TRUE    |
# +-------------------------------------------------+--------+--------+--------+

Thanks ubuntun. How can I add a statement that if there are no values to try (but there are column names), run 'TRUE' not FALSE — MichaelLink, Jun 20 '18 at 06:54
Do you mean "there are two columns, where first contains some values, while second column is empty (populated with `NA`s which is the most probable scenario for any `data.frame`)"? — utubun, Jun 20 '18 at 08:52
There may be a header row, and Column headers, Row 1 and Column A may be filled, but everything else would be NA. (in File 1) — MichaelLink, Jun 20 '18 at 08:53
Ok, I updated my unswer but it rather resembles the logic of @DJV's answer. — utubun, Jun 20 '18 at 14:24

DJV · Answer 2 · 2018-06-18T11:31:39.037

You can use the identical function.

With your function:

check_file <- function(File1 , File2) {
  if (identical(summary.default(File1)[,3],
                summary.default(File2)[,3]) == FALSE) {
    print("Not Same Str")
    return(FALSE)}

  if (identical(class(File1), class(File2)) == FALSE) {
    print("Not Same Class")
    return(FALSE)}

  if (identical(names(File1), names(File2)) == FALSE) {
    print("Non matching number of rows")
    return(FALSE)}

  if (identical(dim(File1), dim(File2)) == FALSE) {
    print("non matching number of columns")
    return(FALSE)
  } else if (length(grep("FALSE", names(File1) == names(File2)))>0){
    print("Non matching names of columns")
    return(FALSE)
  }else if (!class(File1)==class(File2)){
    print("Non matching column types")
  }
  return(TRUE)
}

Test on your data.frames:

File1 <- structure(list(Column.1 = structure(1:3, .Label = c("Row 1", 
                                                    "Row 2", "Row 3"), class = "factor"), Column.2 = c(NA, NA, NA
                                                    ), Column.3 = c(NA, NA, NA), colNames = c(TRUE, TRUE, TRUE)), class = "data.frame", row.names = c(NA, 
                                                                                                                                                      -3L))
File2 <-
structure(list(Column.1 = structure(1:3, .Label = c("Row 1", 
                                                    "Row 2", "Row 3"), class = "factor"), Column.2 = c(1, 2, 34), 
               Column.3 = c(NA, NA, NA), colNames = c(TRUE, TRUE, TRUE)), class = "data.frame", row.names = c(NA, 
                                                                                                              -3L))

check <- check_file(File1, File2)
check

[1] TRUE

Or with nonmatching number of rows:

df1 <- data.frame(x = 1:20)
df2 <- data.frame(x = 1:10)
check <- check_file(df1, df2)
[1] "non matching number of columns"
check 
[1] FALSE

However, in my example Column 2 in Sheet 2 has numbers while Sheet 1 does not. That should return FALSE — MichaelLink, Jun 18 '18 at 11:05
I'm editing my answer. However, regardless, I think that maybe a series of `if`s would be more accurate than `else if`. — DJV, Jun 18 '18 at 11:09
Yes. The idea is that the two files should match types exactly — MichaelLink, Jun 18 '18 at 11:11
So if it´s Date and Number, that´s false. Date and Date is true — MichaelLink, Jun 18 '18 at 11:11
I'm asking because `NA` is recognized as logical. However, you can still have a vector like this `c(1, NA, 2)` which is num. — DJV, Jun 18 '18 at 11:22