# Drop incomplete groups from a DataFrame

**URL:** <https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131>\
**Category:** Data\
**Tags:** question\
**Created:** [July 15, 2020, 3:08pm UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131 "2020-07-15T15:08:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [July 15, 2020, 3:08pm UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/1 "2020-07-15T15:08:44Z")

</div>

After removing some rows from a dataframe, I would like to keep only groups (grouping on a variable) which have “complete” observations (households always have 2 members in this data in the dataset I start with, so for any fewer I can consider that household incomplete).

The following MWE works, is there a more idiomatic way?

```julia
julia> using DataFrames

julia> df = DataFrame(household = [100, 100, 101, 101],
       person = [1, 2, 1, 2],
       wage = 1:4)
4×3 DataFrame
│ Row │ household │ person │ wage │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────────┼────────┼───────┤
│ 1 │ 100 │ 1 │ 1 │
│ 2 │ 100 │ 2 │ 2 │
│ 3 │ 101 │ 1 │ 3 │
│ 4 │ 101 │ 2 │ 4 │

julia> df = df[df.wage .≥ 2, :]
3×3 DataFrame
│ Row │ household │ person │ wage │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────────┼────────┼───────┤
│ 1 │ 100 │ 2 │ 2 │
│ 2 │ 101 │ 1 │ 3 │
│ 3 │ 101 │ 2 │ 4 │

julia> combine(sdf -> size(sdf, 1) == 2 ? sdf : DataFrame(),
       groupby(df, :household))
2×3 DataFrame
│ Row │ household │ person │ wage │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────────┼────────┼───────┤
│ 1 │ 101 │ 1 │ 3 │
│ 2 │ 101 │ 2 │ 4 │

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 15, 2020, 3:19pm UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/2 "2020-07-15T15:19:26Z")

</div>

That’s what I would have done.

You can iterate through sub data frames in a `GroupedDataFrame` but it’s less elegant imo.

```julia
julia> df = DataFrame(a = [1, 1, 2, 2, 3], b = rand(5));

julia> gd = groupby(df, :a);

julia> to_keep = [nrow(sdf) == 2 for sdf in gd];

julia> DataFrame(gd[to_keep])
4×2 DataFrame
│ Row │ a │ b │
│ │ Int64 │ Float64 │
├─────┼───────┼───────────┤
│ 1 │ 1 │ 0.0124186 │
│ 2 │ 1 │ 0.294827 │
│ 3 │ 2 │ 0.335624 │
│ 4 │ 2 │ 0.0368225 │

```

---

<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:** [July 15, 2020, 5:09pm UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/3 "2020-07-15T17:09:37Z")

</div>

Not sure if it’s idiomatic, but sometimes I think it’s helpful to add the count as an extra column. You can do so by calling `transform!` to the grouped data. The parent, ungrouped, dataframe is updated in-place.

```julia
julia> df = DataFrame(household = [100, 100, 101, 101],
       person = [1, 2, 1, 2],
       wage = 1:4)
4×3 DataFrame
│ Row │ household │ person │ wage │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────────┼────────┼───────┤
│ 1 │ 100 │ 1 │ 1 │
│ 2 │ 100 │ 2 │ 2 │
│ 3 │ 101 │ 1 │ 3 │
│ 4 │ 101 │ 2 │ 4 │

julia> transform!(groupby(df, :household), :household => length)
4×4 DataFrame
│ Row │ household │ person │ wage │ household_length │
│ │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────────┼────────┼───────┼──────────────────┤
│ 1 │ 100 │ 1 │ 1 │ 2 │
│ 2 │ 100 │ 2 │ 2 │ 2 │
│ 3 │ 101 │ 1 │ 3 │ 2 │
│ 4 │ 101 │ 2 │ 4 │ 2 │

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [July 15, 2020, 5:26pm UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/4 "2020-07-15T17:26:59Z")

</div>

Note that `nrow` is special-cased. So you can do

```julia
julia> transform!(groupby(df, :household), nrow)

```

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [July 16, 2020, 8:40am UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/5 "2020-07-16T08:40:16Z")

</div>

Thanks for all the answers. “Computing in the table”, which is commonly used in eg Stata, is a style I would particularly like to avoid because I find that it is frequently a source of bugs. I am very happy that DataFrames supports a functional style, and I was just wondering if I am doing the right thing.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [July 16, 2020, 8:48am UTC](https://discourse.julialang.org/t/drop-incomplete-groups-from-a-dataframe/43131/6 "2020-07-16T08:48:16Z")

</div>

There is also `filter` that will be added in the next release (see [https://github.com/JuliaData/DataFrames.jl/pull/2279](https://github.com/JuliaData/DataFrames.jl/pull/2279)).