# How to filter columns of a matrix by columns of another matrix
**URL:** https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061
**Category:** Data
**Tags:** question
**Created:** [January 27, 2021, 3:01pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061 "2021-01-27T15:01:51Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 3:01pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/1 "2021-01-27T15:01:51Z")
Hi
I have two matrics in Julia, both matrics are coded by 0,1,2. and I want to exclude the columns of matrix B from columns of matrix A.
at the end, I will have new matrix with ( 28960x 41227).
how can I do it ?
thanks 🙂
> matrix A 28960×45807 Array{Float64,2}:
> matrix B 28960×4580 view(::Array{Float64,2}, :, [5, 11, 12, 29, 64, 80, 94, 127, 136, 141 … 45707, 45710, 45718, 45732, 45741, 45771, 45773, 45788, 45791, 45807]) with eltype Float64:
---
### Author:  [@stevengj](https://discourse.julialang.org/u/stevengj)
#### Post date: [January 27, 2021, 3:31pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/2 "2021-01-27T15:31:55Z")
Could you give a _small_ example of `A` and `B` inputs and the desired output?
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 3:43pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/3 "2021-01-27T15:43:28Z")
here is small part of my data.
if A is 15x15 matrix and B is 15x5 matrix, how can I sort 5 columns of B from columns of A?
so my desired matrix will be C (15x10)
> 15×15 Array{Float64,2}:
> 0.0 2.0 2.0 2.0 0.0 0.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0
> 1.0 1.0 2.0 2.0 1.0 1.0 2.0 1.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0
> 1.0 1.0 1.0 2.0 2.0 2.0 2.0 1.0 1.0 2.0 1.0 1.0 2.0 1.0 1.0
> 1.0 1.0 2.0 1.0 2.0 2.0 1.0 1.0 2.0 2.0 2.0 2.0 2.0 0.0 2.0
> 2.0 0.0 2.0 1.0 2.0 2.0 2.0 2.0 2.0 0.0 1.0 0.0 1.0 2.0 2.0
> 2.0 0.0 2.0 2.0 2.0 2.0 2.0 2.0 0.0 2.0 0.0 2.0 2.0 0.0 2.0
> 2.0 0.0 2.0 1.0 2.0 2.0 2.0 2.0 1.0 1.0 1.0 1.0 1.0 1.0 2.0
> 2.0 0.0 2.0 2.0 2.0 2.0 2.0 2.0 1.0 1.0 0.0 1.0 2.0 1.0 2.0
> 2.0 1.0 1.0 1.0 2.0 2.0 1.0 2.0 2.0 2.0 2.0 1.0 2.0 1.0 2.0
> 2.0 0.0 1.0 2.0 1.0 1.0 2.0 1.0 1.0 2.0 1.0 1.0 2.0 1.0 2.0
> 2.0 0.0 2.0 2.0 2.0 2.0 2.0 1.0 1.0 2.0 1.0 2.0 2.0 1.0 2.0
> 0.0 1.0 2.0 1.0 2.0 2.0 1.0 1.0 2.0 1.0 2.0 1.0 2.0 1.0 2.0
> 0.0 2.0 2.0 2.0 1.0 1.0 2.0 2.0 2.0 1.0 2.0 2.0 1.0 2.0 1.0
> 2.0 0.0 2.0 2.0 2.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0 2.0 0.0 1.0
> 2.0 0.0 2.0 2.0 2.0 2.0 2.0 1.0 2.0 1.0 1.0 2.0 2.0 2.0 2.0
> 15×5 Array{Float64,2}:
> 0.0 2.0 2.0 2.0 2.0
> 1.0 2.0 2.0 1.0 1.0
> 2.0 1.0 1.0 2.0 0.0
> 2.0 2.0 2.0 1.0 1.0
> 2.0 1.0 0.0 1.0 1.0
> 2.0 0.0 2.0 2.0 0.0
> 2.0 1.0 1.0 2.0 0.0
> 2.0 0.0 1.0 1.0 1.0
> 2.0 2.0 1.0 2.0 2.0
> 1.0 1.0 1.0 2.0 0.0
> 2.0 1.0 2.0 1.0 1.0
> 2.0 2.0 1.0 1.0 2.0
> 1.0 2.0 2.0 2.0 2.0
> 2.0 1.0 2.0 2.0 1.0
> 2.0 1.0 2.0 2.0 2.0
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [January 27, 2021, 3:45pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/4 "2021-01-27T15:45:42Z")
Sorry, but I think we still mean more clarity.
If there are 5 columns in `B`, and 15 columns in `A`, how are you sorting one by the other?
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 3:49pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/5 "2021-01-27T15:49:22Z")
sorry for unclear message, the matrix B is part of matrix A. so I want to exlude the column of matrix B from A, to get new matrix.
---
### Author:  [@Jeff\_Emanuel](https://discourse.julialang.org/u/Jeff_Emanuel)
#### Post date: [January 27, 2021, 3:50pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/6 "2021-01-27T15:50:26Z")
I think you mean to say you want to filter the columns of B from A. There are likely more succinct ways of doing this, but here’s a straight-forward but inefficient low-level solution.
```julia
keep=Int64[]
for i=1:size(A,2)
for j=1:size(B,2)
if A[:,i]==B[:,j]
break # Match, do not keep column i
end
push!(keep,i) # No match, keep column i
end
end
A = A[:,keep]
```
I look forward to seeing more concise solutions for Julia experts.
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 4:26pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/7 "2021-01-27T16:26:49Z")
thank you for helping, I tried the command and maybe cause of my data size,
its running quite slow.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [January 27, 2021, 4:49pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/8 "2021-01-27T16:49:46Z")
You can try `@view A[:, i] == B[:, j]` and see if that speeds things up
---
### Author:  [@stevengj](https://discourse.julialang.org/u/stevengj)
#### Post date: [January 27, 2021, 5:03pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/9 "2021-01-27T17:03:50Z")
> [@Jeff\_Emanuel](#):
>
> ```julia
> for i=1:size(A,2), j=1:size(B,2)
>
> ```
This is a bit inefficient for large matrices because it scales as the _product_ of the number of columns of `A` and `B`. It might be better to construct a `Set` of `B`’s columns so that you can use fast hash-table lookup:
```julia
Bcols = Set(eachcol(B))
C = reduce(hcat, collect(c for c in eachcol(A) if c ∉ Bcols))
```
(I use `collect` here because of [issue #31636](https://github.com/JuliaLang/julia/issues/31636): Julia already has an [optimized `reduce(hcat, x)` method](https://github.com/JuliaLang/julia/pull/27188) but only for the case where `x` is an array of arrays.) Alternatively, you could do
```julia
Bcols = Set(eachcol(B))
C = A[:, @views [i for i in axes(A,2) if A[:,i] ∉ Bcols]]
```
which might be faster than `reduce`?
As usual, if you want to benchmark these it is best to first [put the code into a function](https://docs.julialang.org/en/v1/manual/performance-tips/#Avoid-global-variables):
```julia
function filtercols(A, B)
Bcols = Set(eachcol(B))
return A[:, @views [i for i in axes(A,2) if A[:,i] ∉ Bcols]]
end
```
(In general, you might want to step back and re-think the data structures you are using—matrices might not be the best choice depending on the operations you want to perform. e.g. you might want to store `B` as a `Set` of columns to begin with.)
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 5:21pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/10 "2021-01-27T17:21:04Z")
many thank you guys for helping.
both commands works for my large matrices, and both of them are quite fast.
> Bcols = Set(eachcol(B))
> C = reduce(hcat, collect(c for c in eachcol(A) if c ∉ Bcols))
> Bcols = Set(eachcol(B))
> C = A[:, @views [i for i in axes(A,2) if A[:,i] ∉ Bcols]]
---
### Author:  [@stevengj](https://discourse.julialang.org/u/stevengj)
#### Post date: [January 27, 2021, 5:22pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/11 "2021-01-27T17:22:52Z")
> [@Shazman](#):
>
> 28960x 41227
Realize that this is almost 10GB of memory. If the entries of the matrix are only 0, 1, and 2, you can save a factor of 8 in memory (and also speed things up) simply by using an array of `Int8` (8-bit integer) values instead of `Float64` values.
But again, the fact that you are working with this much data really suggests that you should put some thought into the data structures you are using. It is impossible to give you more detailed advice without knowing more about the underlying problem you are trying to solve.
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [January 27, 2021, 5:35pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/12 "2021-01-27T17:35:44Z")
thank you for suggestion and would love to hear more suggestion from you guys.
here is some info of what I am doing.
this matrix is construted by number of individuals(rows) and number of snps(columns).
the original file is bed file from plink, which has individuals ID(row) and snp ID(column). then I converted into Matrix(Float64) in Julia. the next step is to fit this matrix(28960x41227) into mixed model equation and calculate snp effects,
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [January 27, 2021, 6:57pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/13 "2021-01-27T18:57:20Z")
One alternative that seems to perform alright:
```julia
function A_except_B(A,B)
a2, b2 = size(A,2), size(B,2)
[@views B[:,j] == A[:,i] for i in 1:a2, j in 1:b2] |> ix -> maximum(ix,dims=2) |> iy -> A[:, vec(.!iy)]
end
```
---
### Author:  [@stevengj](https://discourse.julialang.org/u/stevengj)
#### Post date: [January 27, 2021, 9:18pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/14 "2021-01-27T21:18:43Z")
> [@rafael.guerra](#):
>
> One alternative that seems to perform alright:
For large matrices (the OP said A had 45807 cols and B had 4580 cols) this will be slow and will also allocate a large matrix of booleans, because the time and memory of your solution scales like the _product_ of the number of columns in A with the number of columns in B.
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [January 27, 2021, 10:51pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/15 "2021-01-27T22:51:28Z")
@stevengj, please find here below test results for large matrices as per OP, run on regular Win10 laptop and Julia 1.6.0-beta1.0, confirming your speed predictions:
```julia
using Random, BenchmarkTools
A = rand(0:2, 28960, 45807);
B = A[:,vec(rand(1:45807,1,4580))];
@btime A_except_B($A,$B) # 19.0 s (21 allocations: 9.1 GiB)
@btime filtercols1($A,$B) # 9.3 s (45833 allocations: 9.0 GiB)
@btime filtercols2($A,$B) # 11.5 s (26 allocations: 9.0 GiB)
```
The last two functions are your solutions.
_PS: input as per @Jeff_Emanuel’s suggestion_
---
### Author:  [@Jeff\_Emanuel](https://discourse.julialang.org/u/Jeff_Emanuel)
#### Post date: [January 27, 2021, 10:58pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/16 "2021-01-27T22:58:35Z")
To match the OP’s case, I think you want to create B with a random selection of 10% of A’s columns.
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [March 24, 2021, 8:48pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/17 "2021-03-24T20:48:10Z")
Hi,
I used the the function
> function filtercols(A, B)
> Bcols = Set(eachcol(B))
> return A[:, @views [i for i in axes(A,2) if A[:,i] ∉ Bcols]]
> end
to filter the columns of matrix C from matrix D
size of C = `1104x4580` and size of D = `1104x45807`
so the filtered matrix should have size of `1104x41227`, but I got filtered matrix with`1104x41192`
where did `41227 - 41192 = 35` columns go ? and how can I fix it ?
many thanks in advance!
---
### Author:  [@stevengj](https://discourse.julialang.org/u/stevengj)
#### Post date: [March 24, 2021, 11:04pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/18 "2021-03-24T23:04:06Z")
> [@Shazman](#):
>
> so the filtered matrix should have size of `1104x41227` , but I got filtered matrix with `1104x41192`
> where did `41227 - 41192 = 35` columns go ? and how can I fix it ?
Presumably those 35 columns are columns of `C` that appeared more than once in `D`.
---
### Author:  [@Shazman](https://discourse.julialang.org/u/Shazman)
#### Post date: [March 25, 2021, 3:05pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/19 "2021-03-25T15:05:44Z")
thank you for the answer, how can I get that 35 column in the filtered matrix ?
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 11, 2021, 6:03pm UTC](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061/20 "2021-04-11T18:03:50Z")
what about to check only the first n (1000 for intance) elements of each column?
```julia
function filtercolsr(A, B)
Bcols = Set(eachcol(@view B[1:1000, :]))
return A[:, @views [i for i in axes(A,2) if A[1:1000,i] ∉ Bcols]]
end
```
and then filter on the remaining table?
[Next page](https://discourse.julialang.org/t/how-to-filter-columns-of-a-matrix-by-columns-of-another-matrix/54061.md?page=2)