# Creating sub-arrays by comparing multiple arrays DataFrames
**URL:** https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518
**Category:** General Usage
**Tags:** array, dataframes
**Created:** [April 25, 2019, 4:12pm UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518 "2019-04-25T16:12:47Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@eed](https://discourse.julialang.org/u/eed)
#### Post date: [April 25, 2019, 4:12pm UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518/1 "2019-04-25T16:12:47Z")
Hi
I am very new to Julia, and have some experience with Matlab. However, I have been struggling to perform the following:
I have two DataFrames X and Y that I have created (imported from txt file) .
Let’s say X is the following
```julia
│ X1 │ X2 │ X3 │ X4 │ X5 │
-----------------------------------
│ 1 │ "a" │ "a" │ 1 │ "a" │
│ 2 │ "b" │ "b" │ 2 │ "b" │
│ 3 │ "c" │ "c" │ 3 │ "c" │
│ 1 │ "a" │ "d" │ 4 │ "a" │
```
Where column X1 contains numbers and column X2 contains strings.
Let’s say Y is the following
```julia
│ Y1 │Y2 │ Y3 │ Y4 │Y5 │ Y6 │ Y7 │
-----------------------------------------------
│ 1 │ 1 │ "a" │ 1 │ "a" │ 1 │ "a" |
│ 2 │ 2 │ "b" │ 2 │ "b" │ 1 │ "b" |
│ 1 │ 1 │ "c" │ 3 │ "c" │ 1 │ "d" |
│ 3 │ 2 │ "d" │ 4 │ "a" │ 1 │ "e" |
```
Also with columns a mix of numbers and strings.
I would like to find the subset of X where only elements of the column X2 that match the values contained in Y7 i.e.
```julia
│ X1 │ X2 │ X3 │ X4 │ X5 │
-----------------------------------
│ 1 │ "a" │ "a" │ 1 │ "a" │
│ 2 │ "b" │ "b" │ 2 │ "b" │
│ 1 │ "a" │ "d" │ 4 │ "a" │
```
Also, I would like to apply a second filter on that subset, by filtering those elements of X1 that are equal to a certain value (value contained in Y2), in this case 1.
```julia
│ X1 │X2 │ X3 │ X4 │ X5 │
-----------------------------------
│ 1 │ 1 │ "a" │ 1 │ "a" │
│ 1 │ 2 │ "d" │ 4 │ "a" │
```
How could I perform this?
Thanks a lot for your help.
---
### Author:  [@aaowens](https://discourse.julialang.org/u/aaowens)
#### Post date: [April 25, 2019, 4:19pm UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518/2 "2019-04-25T16:19:40Z")
If all the X and Y are in the same DataFrame, you could do something like this with DataFramesMeta
```julia
julia> using DataFrames, DataFramesMeta
julia> df = DataFrame(x = 1:10, y = 1:10)
10×2 DataFrame
│ Row │ x │ y │
│ │ Int64 │ Int64 │
├─────┼───────┼───────┤
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 3 │ 3 │ 3 │
│ 4 │ 4 │ 4 │
│ 5 │ 5 │ 5 │
│ 6 │ 6 │ 6 │
│ 7 │ 7 │ 7 │
│ 8 │ 8 │ 8 │
│ 9 │ 9 │ 9 │
│ 10 │ 10 │ 10 │
julia> @where(df, :x .== :y)
10×2 DataFrame
│ Row │ x │ y │
│ │ Int64 │ Int64 │
├─────┼───────┼───────┤
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 3 │ 3 │ 3 │
│ 4 │ 4 │ 4 │
│ 5 │ 5 │ 5 │
│ 6 │ 6 │ 6 │
│ 7 │ 7 │ 7 │
│ 8 │ 8 │ 8 │
│ 9 │ 9 │ 9 │
│ 10 │ 10 │ 10 │
```
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [April 25, 2019, 6:26pm UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518/3 "2019-04-25T18:26:16Z")
Are your `X` and `Y` `Array`s or `DataFrame`s? Your question isn’t clear to me.
This works:
```julia
using DataFrames
# create dummy data
x = DataFrame(:x1 => rand(1:10, 100), :x2 => rand(1:10, 100))
y = DataFrame(:y1 => rand(1:10, 100), :y2 => rand(1:10, 100))
# Select rows where the values in the x1 column of x are the same as the y1
# column of y, and the x2 value is 7 (arbitrary value for illustration)
x[(x.x1 .== y.y1) .& (x.x2 .== 7), :]
```
This however implicitly assumes that it is meaningful to compare the values of a column in `x` to the values in the same row of a column in `y`. In that case it would probably be more natural to simply have `x` and `y` as a single DataFrame with all columns in `x` and `y`:
```julia
df = hcat(x,y)
```
then:
```julia
df[(df.x1 .== df.y1) .& (df.x2 .== 7), [:x1, :x2]] == x[(x.x1 .== y.y1) .& (x.x2 .== 7), :]
# true
```
---
### Author:  [@eed](https://discourse.julialang.org/u/eed)
#### Post date: [April 26, 2019, 9:22am UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518/4 "2019-04-26T09:22:07Z")
> [@aaowens](#):
>
> X and Y are in the same DataFrame
Thank you, I just edited the post. Unfortunately, X and Y are not in the same DataFrame.
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [April 26, 2019, 10:11am UTC](https://discourse.julialang.org/t/creating-sub-arrays-by-comparing-multiple-arrays-dataframes/23518/5 "2019-04-26T10:11:01Z")
My suggestion still works in principle, however upon reading your edited post it appears that your initial criterion is not that the value of `X.X2` should not be the same as the value in the same row in `Y.Y7`, but rather you want all rows for which the value in `X.X2` is contained _anywhere_ in `Y.Y7`, correct?
In which case you want do to:
```julia
x[in.(x.x2, Ref(unique(y.y7))) .& (x.x2 .== 1), :]
```
EDIT a slightly more concise way to write the above:
```julia
x[in(unique(y.Y7)).(x.X2), :]
```