# Replace all NaN's with zeros in DataFrame
**URL:** https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001
**Category:** General Usage
**Tags:** dataframes
**Created:** [March 27, 2018, 5:47am UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001 "2018-03-27T05:47:15Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@tk3369](https://discourse.julialang.org/u/tk3369)
#### Post date: [March 27, 2018, 5:47am UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/1 "2018-03-27T05:47:15Z")
What’s the best way to replace all NaN’s in a DataFrame with zero? I can write a nested for-loop and check every cell but I thought there may be a simpler way to do that…
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [March 27, 2018, 5:53am UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/2 "2018-03-27T05:53:02Z")
Eg
```julia
using DataFrames
replace_nan(v) = map(x -> isnan(x) ? zero(x) : x, v)
df = DataFrame(a = [NaN, 2.0, 3.0], b = [4.0, 5.0, NaN])
df2 = map(replace_nan, eachcol(df))
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [March 27, 2018, 10:07am UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/3 "2018-03-27T10:07:47Z")
Note that this version allocates new columns, so you may want to use `map!` instead.
---
### Author:  [@tk3369](https://discourse.julialang.org/u/tk3369)
#### Post date: [March 27, 2018, 2:02pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/5 "2018-03-27T14:02:55Z")
This only maps the last row… seems unintuitive to use `last` here. Are you using a different version of Julia or DataFrame?
```julia
julia> df
3×2 DataFrames.DataFrame
│ Row │ a │ b │
├─────┼─────┼─────┤
│ 1 │ NaN │ 4.0 │
│ 2 │ 2.0 │ 5.0 │
│ 3 │ 3.0 │ NaN │
julia> map(replace_nan ∘ last, eachcol(df))
1×2 DataFrames.DataFrame
│ Row │ a │ b │
├─────┼─────┼─────┤
│ 1 │ 3.0 │ 0.0 │
julia> versioninfo()
Julia Version 0.6.2
Commit d386e40c17 (2017-12-13 18:08 UTC)
Platform Info:
OS: macOS (x86_64-apple-darwin14.5.0)
CPU: Intel(R) Core(TM) i5-4258U CPU @ 2.40GHz
WORD_SIZE: 64
BLAS: libopenblas (USE64BITINT DYNAMIC_ARCH NO_AFFINITY Haswell)
LAPACK: libopenblas64_
LIBM: libopenlibm
LLVM: libLLVM-3.9.1 (ORCJIT, haswell)
julia> Pkg.installed("DataFrames")
v"0.11.5"
```
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [March 27, 2018, 2:40pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/6 "2018-03-27T14:40:23Z")
I missed the fact that `DataFrames` defined a `map` for `DFColumnIterator`. So the version
```julia
using DataFrames
df = DataFrame(a = [NaN, 2.0, 3.0], b = [4.0, 5.0, NaN])
replace_nan(v::AbstractVector) = map(x -> isnan(x) ? zero(x) : x, v)
replace_nan!(v::AbstractVector) = map!(x -> isnan(x) ? zero(x) : x, v, v)
map(replace_nan, eachcol(df))
map(replace_nan!, eachcol(df))
```
works as is. Sorry for the confusion.
---
### Author:  [@tbeason](https://discourse.julialang.org/u/tbeason)
#### Post date: [March 27, 2018, 4:06pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/7 "2018-03-27T16:06:00Z")
FYI, this only works if every column contains Floats. Otherwise `isnan` will throw an error.
EDIT: To be a bit more helpful, the general idea of the `map` function is what I use, but I actually loop over each column and check that it is `Vector{<:AbstractFloat}` first before I apply the `NaN` to 0 (or `missing`, in my case) conversion.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [March 27, 2018, 4:29pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/8 "2018-03-27T16:29:04Z")
> [@tbeason](#):
>
> FYI, this only works if every column contains Floats. Otherwise isnan will throw an error.
Not quite,
```julia
julia> methods(isnan)
# 6 methods for generic function "isnan":
isnan(x::BigFloat) in Base.MPFR at mpfr.jl:828
isnan(x::Float16) in Base at float.jl:522
isnan(x::AbstractFloat) in Base at float.jl:521
isnan(x::Real) in Base at float.jl:523
isnan(z::Complex) in Base at complex.jl:118
isnan(x::AbstractArray{T,N} where N) where T<:Number in Base at deprecated.jl:56
```
---
### Author:  [@pasha](https://discourse.julialang.org/u/pasha)
#### Post date: [March 27, 2018, 4:30pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/9 "2018-03-27T16:30:59Z")
> [@tbeason](#):
>
> FYI, this only works if every column contains Floats. Otherwise isnan will throw an error.
This is where a function like R’s [`dplyr::mutate_if()`](http://dplyr.tidyverse.org/reference/summarise_all.html) will be awesome someday in Julia. I imagine that someday one of us will build that into Query.jl or DataFramesMeta.jl. Until then, the alternatives are not bad at all.
---
### Author:  [@tbeason](https://discourse.julialang.org/u/tbeason)
#### Post date: [March 27, 2018, 4:42pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/10 "2018-03-27T16:42:51Z")
Sorry, a subtype of `Number` I guess. Still, `String` columns would cause an error here. I suppose the anonymous function could have an additional logical layer that checks for proper element type first, and that would make this a robust option.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [March 27, 2018, 5:43pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/11 "2018-03-27T17:43:38Z")
I agree that a `mutate_if` function is important, but with `DataFramesMeta` you can also use the `@byrow!` macro to get similar results.
---
### Author:  [@oheil](https://discourse.julialang.org/u/oheil)
#### Post date: [March 29, 2018, 12:17pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/12 "2018-03-29T12:17:56Z")
Thhis function is what I use for this:
```julia
function checkForNotANumber(x::Any)
(!isa(x,Integer) && !isa(x,Real)) || isnan(x)
end
```
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [March 29, 2018, 12:32pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/13 "2018-03-29T12:32:47Z")
Note that `Integer <: Real`, so checking for the latter is sufficient.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [March 29, 2018, 12:51pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/14 "2018-03-29T12:51:50Z")
Thinking about this discussion,
```julia
# FIXME get letter of marque for type piracy
Base.map(f, df::AbstractDataFrame) = map(col -> map(f, col), eachcol(df))
```
would solve this problem 90% of the time (when I don’t want to do something different for columns). Eg
```julia
map(x -> x isa Real && isnan(x) ? zero(x) : x, df)
```
---
### Author:  [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)
#### Post date: [September 2, 2019, 9:14am UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/15 "2019-09-02T09:14:19Z")
For `missing` (replace `ismissing()` with `isnan()` for `NaN`s) you can use also this trick, that makes all `missing` in numeric columns equal to 0, and all `missing` in strinng columns equal to `""`:
```julia
[df[ismissing.(df[!,i]), i] .= 0 for i in names(df) if Base.nonmissingtype(eltype(df[!,i])) <: Number]
[df[ismissing.(df[!,i]), i] .= "" for i in names(df) if Base.nonmissingtype(eltype(df[!,i])) <: String]
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [September 5, 2019, 8:46pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/16 "2019-09-05T20:46:29Z")
In recent versions you can now do this:
```julia
mapcols(col -> replace!(col, NaN=>0), df) # In-place
```
or
```julia
ifelse.(isnan.(df), 0, df)
```
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [September 5, 2019, 8:53pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/17 "2019-09-05T20:53:46Z")
Yes, also it will work in place: `df .= ifelse.(isnan.(df), 0, df)`.
---
### Author:  [@VivMendes](https://discourse.julialang.org/u/VivMendes)
#### Post date: [August 3, 2021, 4:00pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/18 "2021-08-03T16:00:31Z")
Sorry for a late question related to this topic. In my case, it is not NaN but negative values. I have a time series with positive and negative entries. I need to transform the negative values into zero. I do not want to eliminate them; I need to keep them as observations, but with a value of zero. Help will be very much appreciated. Thanks.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [August 3, 2021, 4:05pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/19 "2021-08-03T16:05:08Z")
This will do it
```julia
julia> df = DataFrame(a = randn(5), b = randn(5))
5×2 DataFrame
Row │ a b
│ Float64 Float64
─────┼──────────────────────
1 │ 0.8805 0.667461
2 │ 0.17179 -0.618585
3 │ -0.667805 -0.32467
4 │ -0.517509 -0.321862
5 │ 1.64746 -0.344586
julia> mapcols(t -> ifelse.(t .< 0, 0, t), df)
5×2 DataFrame
Row │ a b
│ Real Real
─────┼───────────────────
1 │ 0.8805 0.667461
2 │ 0.17179 0
3 │ 0 0
4 │ 0 0
5 │ 1.64746 0
```
---
### Author:  [@VivMendes](https://discourse.julialang.org/u/VivMendes)
#### Post date: [August 3, 2021, 4:14pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/20 "2021-08-03T16:14:29Z")
@pdeffebach Thank you very much. After two hours of stumbling by me, it took you just a minute to do it. Thanks.
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [August 3, 2021, 4:54pm UTC](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001/21 "2021-08-03T16:54:39Z")
or even shorter:
```julia
julia> df = DataFrame(a = randn(5), b = randn(5))
5×2 DataFrame
Row │ a b
│ Float64 Float64
─────┼───────────────────────
1 │ -0.386397 0.392352
2 │ -0.476617 -0.0270584
3 │ -0.218456 -0.224436
4 │ -1.17403 -0.520317
5 │ -1.70785 0.0390936
julia> ifelse.(df .< 0, 0, df)
5×2 DataFrame
Row │ a b
│ Int64 Float64
─────┼──────────────────
1 │ 0 0.392352
2 │ 0 0.0
3 │ 0 0.0
4 │ 0 0.0
5 │ 0 0.0390936
```
[Next page](https://discourse.julialang.org/t/replace-all-nans-with-zeros-in-dataframe/10001.md?page=2)