# How to clean/filter/remove wrong data from DataFrame

**URL:** <https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797>\
**Category:** New to Julia\
**Tags:** question, dataframes\
**Created:** [February 20, 2022, 12:04pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797 "2022-02-20T12:04:18Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![likzew](https://avatars.discourse-cdn.com/v4/letter/l/76d3ee/32.png) [@likzew](https://discourse.julialang.org/u/likzew)\
**Post date:** [February 20, 2022, 12:04pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/1 "2022-02-20T12:04:19Z")

</div>

Hi,  
What’s the best way to fix the anomaly in column c?

```julia
test = DataFrame(a = 1:5, b = rand(5), c=[2,5,5,"%42,,",5])

```

---

<div class="post-metadata">

**Author:** ![juliohm](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juliohm/32/215266_2.png) [@juliohm](https://discourse.julialang.org/u/juliohm)\
**Post date:** [February 20, 2022, 12:30pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/2 "2022-02-20T12:30:07Z")

</div>

Maybe your issue is caused by some loading pipeline? What about fixing the loading pipeline so that the dataframe has proper columns from the beginning?

If that is not possible, you need to define what “fix” means for you. We can then provide links to the sections of the DataFrames.jl documentation to help you.

---

<div class="post-metadata">

**Author:** ![likzew](https://avatars.discourse-cdn.com/v4/letter/l/76d3ee/32.png) [@likzew](https://discourse.julialang.org/u/likzew)\
**Post date:** [February 20, 2022, 12:44pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/3 "2022-02-20T12:44:04Z")

</div>

Thank you for the prompt answer.

Let’s assume that the data is imported from Excel.  
Assume additionally that this is a large data set and manual modifications are not possible  
By fix, I mean:

- dropping rows,
- replacement by missings
- replacement by NaNs

likzew

---

<div class="post-metadata">

**Author:** ![juliohm](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juliohm/32/215266_2.png) [@juliohm](https://discourse.julialang.org/u/juliohm)\
**Post date:** [February 20, 2022, 1:31pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/4 "2022-02-20T13:31:28Z")

</div>

> [@likzew](#):
>
> Let’s assume that the data is imported from Excel.

Are you using XLSX.jl to import the data? Did you check if they provide any functionality to parse the cells according to a specific idiom?

> [@likzew](#):
>
> By fix, I mean:
> 
> - dropping rows,
> - replacement by missings
> - replacement by NaNs

You can check DataFrames.jl docs for the `replace` and `dropmissing` functions.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [February 20, 2022, 2:11pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/5 "2022-02-20T14:11:50Z")

</div>

> [@juliohm](#):
>
> You can check DataFrames.jl docs for the `replace` and `dropmissing` functions.

By reading the `DataFrames.jl` docs, we may get something like this:

```julia
dropmissing(ifelse.(isa.(df, (Number,)), df, missing))

```

However, the following seems to perform better:

```julia
df[vec(all(Matrix{Bool}(isa.(df, (Number,))), dims=2)), :]

```

What is the recommended way to drop rows with non-numeric entries?

---

<div class="post-metadata">

**Author:** ![likzew](https://avatars.discourse-cdn.com/v4/letter/l/76d3ee/32.png) [@likzew](https://discourse.julialang.org/u/likzew)\
**Post date:** [February 20, 2022, 2:24pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/6 "2022-02-20T14:24:34Z")

</div>

Thank you kindly,

I fiqure out somthing like that:

To detect:

```julia
test.c[isa.(test.c,String)]

```

To replace

```julia
test.c[isa.(test.c,String)].=missing

```

To clean:

```julia
test! = dropmissing(test)

```

It seems that working.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [February 20, 2022, 4:59pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/7 "2022-02-20T16:59:24Z")

</div>

Seems a bit redundant then to set the value to missing first? Why not just

```julia
test[.!isa.(test.c, String), :]

```

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [February 20, 2022, 5:12pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/8 "2022-02-20T17:12:23Z")

</div>

What about the general case where non-numeric entries can be anywhere?

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [February 20, 2022, 5:59pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/9 "2022-02-20T17:59:16Z")

</div>

Maybe

```julia
any.(eachrow(.!isa.(test, Number)))

```

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [February 20, 2022, 6:32pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/10 "2022-02-20T18:32:31Z")

</div>

It is shorter and nice but taking the `eachrow` path doesn’t seem as performant as creating the `Matrix{Bool}`.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [February 20, 2022, 6:58pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/11 "2022-02-20T18:58:41Z")

</div>

I would hope that no one ever has to do this in a hot loop!

---

<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:** [February 20, 2022, 7:33pm UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/12 "2022-02-20T19:33:53Z")

</div>

In DataFramesMeta.jl you would do

```julia
@rsubset df !(:c isa String)

```

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [February 21, 2022, 12:07am UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/13 "2022-02-21T00:07:24Z")

</div>

Regarding this mutation:

```julia
test.c[isa.(test.c,String)].=missing

```

I want to point out that the update is done in place, and the element type of the column is still `Vector{Any}`. And, that would not be performant with subsequent processing. Another option is to just build and mutate the column:

```julia
test.c = [x isa Number ? x : missing for x in test.c]

```

After that, you can still use `dropmissing!` mutate the existing data frame.

In any case, it would be best if you can avoid loading in junk data into the data frame in the first place.

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [February 21, 2022, 12:52am UTC](https://discourse.julialang.org/t/how-to-clean-filter-remove-wrong-data-from-dataframe/76797/14 "2022-02-21T00:52:19Z")

</div>

```julia
f(x) = typeof(x) <: Number
subset(test, Cols(:) .=> ByRow(f))

```