# Duplicates in IndexedTables/JuliaDB

**URL:** <https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306>\
**Category:** General Usage\
**Tags:** statistics, data, indexing\
**Created:** [November 25, 2017, 5:45pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306 "2017-11-25T17:45:10Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pazzo83](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pazzo83/32/232_2.png) [@pazzo83](https://discourse.julialang.org/u/pazzo83)\
**Post date:** [November 25, 2017, 5:45pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/1 "2017-11-25T17:45:11Z")

</div>

Hi, with a 2-D ndsparse table (with one value column), I am trying to select the max value for each of the 1st dimension of the index, but I am not really sure how to do that (groupby removes the second dimension, which I need to keep). For example, with a table like this:

```julia
2-d NDSparse with 8 values (Float64):
a b │
─────┼──────────
1 1 │ 0.196028
1 2 │ 0.0369555
1 3 │ 0.630628
2 4 │ 0.521345
2 5 │ 0.495389
2 6 │ 0.300472
3 7 │ 0.212149
3 8 │ 0.816977

```

I would like to produce the following:

```julia
2-d NDSparse with 8 values (Float64):
a b │
─────┼──────────
1 3 │ 0.630628
2 4 │ 0.521345
3 8 │ 0.816977

```

In pandas, you can usually filter out duplicates based on one index of a multi-indexed dataframe, but I’m not sure how to closely approximate that here.  
Thank you!

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [November 25, 2017, 5:58pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/2 "2017-11-25T17:58:41Z")

</div>

```julia
groupreduce(max, df)

```

where `df` is your NDSparse should do the trick. You can find up to date documentation [here](http://juliadb.org/latest/api/aggregation.html#Grouping-1) (the API changed quite a bit recently).

---

<div class="post-metadata">

**Author:** ![pazzo83](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pazzo83/32/232_2.png) [@pazzo83](https://discourse.julialang.org/u/pazzo83)\
**Post date:** [November 25, 2017, 6:02pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/3 "2017-11-25T18:02:34Z")

</div>

Thanks, however, that doesn’t seem to do the selection based on only the first index while preserving whatever the second index is at that point. For example:

```julia
julia> groupreduce(max, testtbl)
2-d NDSparse with 8 values (Float64):
a b │
─────┼──────────
1 1 │ 0.196028
1 2 │ 0.0369555
1 3 │ 0.630628
2 4 │ 0.521345
2 5 │ 0.495389
2 6 │ 0.300472
3 7 │ 0.212149
3 8 │ 0.816977

```

If I try to select the first dimension to act upon, then I lose the second dimension:

```julia
julia> groupreduce(max, testtbl, :a)
1-d NDSparse with 3 values (Float64):
a │
──┼─────────
1 │ 0.630628
2 │ 0.521345
3 │ 0.816977

```

---

<div class="post-metadata">

**Author:** ![pazzo83](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pazzo83/32/232_2.png) [@pazzo83](https://discourse.julialang.org/u/pazzo83)\
**Post date:** [November 25, 2017, 6:04pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/4 "2017-11-25T18:04:28Z")

</div>

Interesting, this just about works, I have to move the 3rd column here back into the index:

```julia
julia> groupreduce(max, testtbl, :a; select=(3,:b))
1-d NDSparse with 3 values (2-tuples):
a │ 2 3
──┼────────────
1 │ 0.630628 3
2 │ 0.521345 4
3 │ 0.816977 8

```

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [November 25, 2017, 6:33pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/5 "2017-11-25T18:33:28Z")

</div>

> [@pazzo83](#):
>
> Thanks, however, that doesn’t seem to do the selection based on only the first index while preserving whatever the second index is at that point.

Sorry, I misunderstood the question. The way to do what you want would probably be with `mapslices`, but it’s a bit verbose. I believe something like:

```julia
function get_max(dd)
   data = values(dd)
   n = indmax(data)
   NDSparse([keys(dd,2)[n]], [data[n]])
end
mapslices(get_max, df, 2)

```

should work.

I’m not sure if there’s a simple way to change which columns are index and which are data, though that’s certainly interesting. If you need that functionality, maybe you’re better off with a simple [Table](http://juliadb.org/latest/api/datastructures.html#Table-1)

---

<div class="post-metadata">

**Author:** ![pazzo83](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pazzo83/32/232_2.png) [@pazzo83](https://discourse.julialang.org/u/pazzo83)\
**Post date:** [November 25, 2017, 7:27pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/6 "2017-11-25T19:27:12Z")

</div>

Thanks! I wasn’t aware of `mapslices`, I will definitely check that out.  
Edit: That seems to throw an exception:

```julia
julia> mapslices(get_max, df, 2)
ERROR: MethodError: all(::IndexedTables.##195#200, ::Array{Any,1}) is ambiguous. Candidates:
  all(f, A::AbstractArray{#s3,N} where N where #s3>:Nulls.Null) in Nulls at /Users/christopheralexander/.julia/v0.6/Nulls/src/Nulls.jl:221
  all(f, A::AbstractArray{#s3,N} where N where #s3>:Missings.Missing) in Missings at /Users/christopheralexander/.julia/v0.6/Missings/src/Missings.jl:411
Possible fix, define
  all(::Any, ::AbstractArray{#s3,N} where N where #s3>:Union{Missings.Missing, Nulls.Null})

```

Edit → just needed an update, works now!

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/julialang/original/3X/1/2/12829a7ba92b924d4ce81099cbf99785bee9b405.png) [@system](https://discourse.julialang.org/u/system)\
**Post date:** [November 28, 2017, 7:54pm UTC](https://discourse.julialang.org/t/duplicates-in-indexedtables-juliadb/7306/8 "2017-11-28T19:54:29Z")

</div>

Closed on November 28, 2017, 7:54pm UTC

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