How to check if columns in dataframe are identical in R [produce matrix]

Question

I have a large dataframe ncol =220 I want to compare the columns to see if they may be identical and produce a matrix for ease of identification.

So what I have is

      x    y   z
1   dog   dog   cat    
2   dog   dog   dog
3   cat   cat   cat

What I want

     x     y     z
x   -     True   False
y   True     -   False
z   False False   -

Is there a way to do this using identical() in R?

If you want to look for duplicate cols, you would expect a vector - not a matrix? (A vector indicationg that a column is a duplicate) — Christoph, Jul 07 '17 at 12:05
It just gives me a vector, so I dont know what comparisons have been made. — AudileF, Jul 07 '17 at 12:20
It compares *columns*, so gives a vector as @Christoph pointed out. — Dan, Jul 07 '17 at 12:23
@AudileF since `duplicated(t(df))` returns `FALSE TRUE FALSE`, I think the function compares by row since line 1 and 2 are equal. But that's not what you want. — Mbr Mbr, Jul 07 '17 at 12:23
with duplicated you won't be able to know which column is duplicated with which column — Cath, Jul 07 '17 at 12:24
@MbrMbr no it's just saying that column 2 of original df is duplicate (necessarily of column 1 in this case) — Cath, Jul 07 '17 at 12:25
@Cath Oh my bad, but it's quite the same since lines of `t(df)` are columns of `df`. — Mbr Mbr, Jul 07 '17 at 12:26
@MbrMbr indeed, it works on the rows of t(df), which are the columns of df ;-) — Cath, Jul 07 '17 at 12:36

score 4 · Answer 1 · answered Jul 07 '17 at 12:26

4

To compliment @Cath's comment about stringdist, it is as easy as,

library(stringdist)

stringdistmatrix(df, df) == 0

#      [,1]  [,2]  [,3]
#[1,]  TRUE  TRUE FALSE
#[2,]  TRUE  TRUE FALSE
#[3,] FALSE FALSE  TRUE

answered Jul 07 '17 at 12:26

Sotos

51,121
6
32
66

Cath · Accepted Answer · 2017-07-07T12:33:42.663

3

Probably not very efficient but you can try:

seq_col <- seq_len(ncol(df))
sapply(seq_col, function(i) sapply(seq_col, function(j) identical(df[, i], df[, j])))
      # [,1]  [,2]  [,3]
# [1,]  TRUE  TRUE FALSE
# [2,]  TRUE  TRUE FALSE
# [3,] FALSE FALSE  TRUE

It gives you what you want (except for the diagonal, which is all TRUE here) but there must be a package with a function to create a distance matrix based on character vectors. Maybe something with stringdist ?

edited Jul 07 '17 at 12:33

answered Jul 07 '17 at 12:17

Cath

23,906
5
52
86

1

Thanks Cath. My original data is binary so this is great. Ill give it a go. – AudileF Jul 07 '17 at 12:18
Hi @Cath I get the following error for the sapply command: `Error: unexpected 'function' in "sapply(seq_col function"` – AudileF Jul 07 '17 at 12:28
Thanks Cath, works a treat. Just wondering if you could point me in a direct to learn more about `function()` and `sapply()`. Ive seen them pop up a few times in useful commands. – AudileF Jul 07 '17 at 12:36
1

@AudileF y/w :-) sapply permits to work on each element of an object (list or vector) and apply "some function" to those elements. Then you can define the function you need or use a "predefined" one (and so the function will be applied on each element). See also https://stackoverflow.com/q/3505701/4137985 – Cath Jul 07 '17 at 12:38
1

@AudileF For a nice overview of the `*apply`-family and related functions, see: [*R Grouping functions: sapply vs. lapply vs. apply. vs. tapply vs. by vs. aggregate*](https://stackoverflow.com/q/3505701/2204410) – Jaap Jul 07 '17 at 12:40

How to check if columns in dataframe are identical in R [produce matrix]

2 Answers2