# Delete row from DataFrame in place based on entire row value

**URL:** <https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961>\
**Category:** New to Julia\
**Tags:** question, dataframes\
**Created:** [April 2, 2023, 1:59am UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961 "2023-04-02T01:59:01Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 2, 2023, 1:59am UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/1 "2023-04-02T01:59:01Z")

</div>

I am trying to write a function that will efficiently delete the rows in a large DataFrame if it exists in a separate larger DataFrame.

So the function look something like…

```julia
function removedup!(Tables.namedtupleiterator(NewDF), Tables.namedtupleiterator(dataDF))
    for row in NewDF 
        in(row, dataDF) && # delete row from NewDF
    end
end

```

But I can’t quite figure out how to do this in place. Based on other [posts](https://discourse.julialang.org/t/how-to-delete-rows-in-dataframe/86993/2) I think for a DataFrame alone it would look something like?

```julia
function removedup!(NewDF, dataDF)
    for row in eachrow(NewDF) 
        in(row, eachrow(dataDF)) && deleteat!(NewDF, findall(NewDF.col1 .== row.col1 .&& NewDF.col2 .== row.col2 .&& NewDF.col3 .== row.col3))
    end
end

```

But as the `DataFrame` is large I think passing the argument as `Tables.namedtupleiterator` improves row iteration [efficiency](https://bkamins.github.io/julialang/2022/07/08/iteration.html). Also I don’t know if there is a way to do it by passing the entire row without listing out each individual column? My understanding is that `DataFrames` doesn’t have row indexing so I don’t know if that would work.

---

<div class="post-metadata">

**Author:** ![manuka](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/manuka/32/20453_2.png) [@manuka](https://discourse.julialang.org/u/manuka)\
**Post date:** [April 2, 2023, 11:07am UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/2 "2023-04-02T11:07:27Z")

</div>

> [@phantom](#):
>
> I am trying to write a function that will efficiently delete the rows in a large DataFrame if it exists in a separate larger DataFrame.

I am not an expert so my answer might not be that efficient. One way is to create a unique key for each row (e.g., concatenate all the columns separated by a unique character) for both datasets and then simply use the filter function.

---

<div class="post-metadata">

**Author:** ![kevbonham](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kevbonham/32/216165_2.png) [@kevbonham](https://discourse.julialang.org/u/kevbonham)\
**Post date:** [April 2, 2023, 11:32am UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/3 "2023-04-02T11:32:51Z")

</div>

In terms of deletion, I think your best bet is to get all of the row indices that are duplicates, and then call [`deleteat!`](https://dataframes.juliadata.org/stable/lib/functions/#Base.deleteat!) once.

But in terms of efficiency, if `DataDF` is sufficiently large, I would guess that most of your time is spent searching it row by row. The way you’re doing it now, you basically have to iterate through every row of `DataDF` for each row of `NewDF`.

Are there any columns that are most likely to be unique? Maybe you could do an interactive approach where you check each column and bail of any of them are unique. An alternative is if there are one or more columns that are repeated, you could `groupby` those, and then index into the grouped data frame to reduce your search space.

---

<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:** [April 2, 2023, 1:26pm UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/4 "2023-04-02T13:26:27Z")

</div>

you could do `leftjoin!` of both tables (assuming larger table does not have duplicates). This should be efficient.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [April 2, 2023, 5:12pm UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/5 "2023-04-02T17:12:08Z")

</div>

I would have said this is a task for antijoin

---

<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:** [April 2, 2023, 9:10pm UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/6 "2023-04-02T21:10:18Z")

</div>

Indeed. It is a better solution. It will not be in-place but I assume that OP would accept this.

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [April 3, 2023, 11:21pm UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/7 "2023-04-03T23:21:38Z")

</div>

awesome! Thanks for all the helpful approaches and useful suggestions. The DataFrame being used as a reference is a large `Arrow` file. Am I correct in assuming that I can avoid bringing the arrow table into memory with the `antijoin` approach?

The following seems to work but just wanted to make sure I wasn’t missing anything.

```julia
DataDF= DataFrame(Arrow.Table("file"))
NewDF = antijoin(NewDF, DataDF, on = intersect(names(NewDF), names(DataDF))

```

---

<div class="post-metadata">

**Author:** ![kevbonham](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kevbonham/32/216165_2.png) [@kevbonham](https://discourse.julialang.org/u/kevbonham)\
**Post date:** [April 4, 2023, 12:02am UTC](https://discourse.julialang.org/t/delete-row-from-dataframe-in-place-based-on-entire-row-value/96961/8 "2023-04-04T00:02:43Z")

</div>

Oh wow, I didn’t know antijoin was a thing. Neat!