# Iterate over all numeric columns in DataFrames
**URL:** https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916
**Category:** Data
**Created:** [February 8, 2018, 2:18am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916 "2018-02-08T02:18:05Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 8, 2018, 2:18am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/1 "2018-02-08T02:18:05Z")
beginner’s questions:
- how do I iterate over all numeric columns in a DataFrame, e.g., to calculate means or sum-cubed?
- bonus question: can DataFrames consider NaN to be a missing value for Float32 and Float64, too, as in completecases for example?
---
### Author:  [@RandomString123](https://discourse.julialang.org/u/RandomString123)
#### Post date: [February 8, 2018, 3:29am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/2 "2018-02-08T03:29:59Z")
Do you know which columns you want beforehand? If so one of these methods would work:
```julia
Main> df = DataFrame(A=[1,3,4,5,6,432], B=[1,3,4,5,6,3], C=[1,3,5,564,454,654])
6×3 DataFrames.DataFrame
│ Row │ A │ B │ C │
├─────┼─────┼───┼─────┤
│ 1 │ 1 │ 1 │ 1 │
│ 2 │ 3 │ 3 │ 3 │
│ 3 │ 4 │ 4 │ 5 │
│ 4 │ 5 │ 5 │ 564 │
│ 5 │ 6 │ 6 │ 454 │
│ 6 │ 432 │ 3 │ 654 │
Main> [mean(x[2]) for x in eachcol(df[[:A, :B]])] #[2] because eachcol returns a tuple
2-element Array{Float64,1}:
75.1667
3.66667
Main> colwise(mean,df[[:B, :C]])
2-element Array{Float64,1}:
3.66667
280.167
```
The only accepted value is `missing` so you can not use NaN as a missing value. However, because you can test for a missing value you can replace NaN with missing (Assuming your column can accept missing values).
```julia
Main> using Missings
Main> df = DataFrame(C=[missing,NaN,3432.34,432.2,NaN, 43.])
6×1 DataFrames.DataFrame
│ Row │ C │
├─────┼─────────┤
│ 1 │ missing │
│ 2 │ NaN │
│ 3 │ 3432.34 │
│ 4 │ 432.2 │
│ 5 │ NaN │
│ 6 │ 43.0 │
Main> df[:C] = [isnan(x) ? missing : x for x in df[:C] ]
6-element Array{Any,1}:
missing
missing
3432.34
432.2
missing
43.0
Main> df
6×1 DataFrames.DataFrame
│ Row │ C │
├─────┼─────────┤
│ 1 │ missing │
│ 2 │ missing │
│ 3 │ 3432.34 │
│ 4 │ 432.2 │
│ 5 │ missing │
│ 6 │ 43.0 │
```
---
### Author:  [@RandomString123](https://discourse.julialang.org/u/RandomString123)
#### Post date: [February 8, 2018, 3:55am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/3 "2018-02-08T03:55:38Z")
Also forgot to add the case where you do not know if a column is a number or not you can use something that expands on this basic case
```julia
Main> df = DataFrame(A=[1,3,4,5,6,432], B=[1,3,4,5,6,3], C=["a", "b", "c", "d", "e", "f"])
6×3 DataFrames.DataFrame
│ Row │ A │ B │ C │
├─────┼─────┼───┼───┤
│ 1 │ 1 │ 1 │ a │
│ 2 │ 3 │ 3 │ b │
│ 3 │ 4 │ 4 │ c │
│ 4 │ 5 │ 5 │ d │
│ 5 │ 6 │ 6 │ e │
│ 6 │ 432 │ 3 │ f │
Main> df[:, colwise(x -> (eltype(x) <: Number),df)]
6×2 DataFrames.DataFrame
│ Row │ A │ B │
├─────┼─────┼───┤
│ 1 │ 1 │ 1 │
│ 2 │ 3 │ 3 │
│ 3 │ 4 │ 4 │
│ 4 │ 5 │ 5 │
│ 5 │ 6 │ 6 │
│ 6 │ 432 │ 3 │
```
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [February 8, 2018, 6:45am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/4 "2018-02-08T06:45:37Z")
```julia
srand(0)
using DataFrames
df = DataFrame(a = 1:5, b = rand(5), c = 'a':'e')
categorical!(df, :c)
df[2,:b] = NaN # Make an observation NaN
# Intermediate Step
allowmissing!(df)
# Helpers
consideredmissing(obj::Any) = ismissing(obj)
consideredmissing(obj::Real) = ismissing(obj) | isnan(obj)
function nonmissing!(obj::AbstractDataFrame)
for (name, col) ∈ eachcol(df)
if !any(ismissing.(col))
obj[name] = disallowmissing(col)
end
end
end
function isreal_col(obj::AbstractVector) # Assumes you Real (could be Number)
T = eltype(obj)
return T <: Real | T <: Union{Real, Missing}
end
mean_rmmissing(obj::AbstractVector) = mean(skipmissing(obj))
sumcubicpower_rmmissing(obj::AbstractVector) = sum(elem -> elem^3, skipmissing(obj))
# Replace NaN with Missings
colwise(col -> col[consideredmissing.(col)] = missing, df)
# Assuming your functions want to exclude missing values (or previous NaN)
map(mean_rmmissing, filter(isreal_col, df.columns))
map(sumcubicpower_rmmissing, filter(isreal_col, df.columns))
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 8, 2018, 8:55am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/5 "2018-02-08T08:55:15Z")
I think we should improve `colwise` so that when a `MethodError` is thrown for one column, it uses `missing` for that column. That way you could just skip columns with `missing` after doing the computation. Or at least it should be an option.
---
### Author:  [@RandomString123](https://discourse.julialang.org/u/RandomString123)
#### Post date: [February 8, 2018, 12:56pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/6 "2018-02-08T12:56:52Z")
I would go with an option and even then I am not sure. The suggestion introduces ambiguity in a result that one would then need to test. This is because missing in a resultant vector could mean:
- func(col) → missing
or
- func(col) threw a method error
It would be impossible to tell which one unless you go back and re-evaluate all columns that resulted in missing.
As an add on, something I would like to see, especially now that missing is part of base, is aggregation functions surfacing a remove missing option.
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [February 8, 2018, 3:42pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/7 "2018-02-08T15:42:34Z")
Computing the `mean` of a categorical variable should throw a `MethodError`. However, more generally, it would be nice to expand methods for `AbstractArray{T} where T` such that `func(obj::AbstractVector{T}) where {T <: Union{S, Missing}}` can work either by returning `missing` or `MethodError` (i.e., à la R’s `, na.rm = True)`.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 8, 2018, 4:35pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/8 "2018-02-08T16:35:54Z")
> [@RandomString123](#):
>
> I would go with an option and even then I am not sure. The suggestion introduces ambiguity in a result that one would then need to test. This is because missing in a resultant vector could mean:
>
> ```
> func(col) -> missing
> or
> func(col) threw a method error
>
> ```
>
> It would be impossible to tell which one unless you go back and re-evaluate all columns that resulted in missing.
Yes, that’s the main limit. Pandas does this IIRC (using `NaN`). I’m not sure it would really be problematic if it was an option.
> [@RandomString123](#):
>
> As an add on, something I would like to see, especially now that missing is part of base, is aggregation functions surfacing a remove missing option.
The [official way](https://docs.julialang.org/en/latest/manual/missing/#Skipping-Missing-Values-1) to skip missing values is to use `skipmissing`. The dominant opinion is that it’s not a good idea to add arguments to all reduction functions given that `skipmissing` is universal, efficient and as easy to use as a keyword argument.
> [@Nosferican](#):
>
> Computing the mean of a categorical variable should throw a MethodError. However, more generally, it would be nice to expand methods for AbstractArray{T} where T such that func(obj::AbstractVector{T}) where {T \<: Union{S, Missing}} can work either by returning missing or MethodError (i.e., à la R’s , na.rm = True).
Indeed. AFAIK that’s the case for most Base functions, including `sum` and `mean`. That’s not the case for many functions in StatsBase, though: see [this issue](https://github.com/JuliaStats/StatsBase.jl/issues/342).
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 8, 2018, 5:40pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/9 "2018-02-08T17:40:15Z")
skipmissing is nice, but I think it should have an option (not a default) to consider NaN missing, too. After all, NaN s a much faster hardware way to designate missing observations in Floats. Many ops take three times as long when they have to deal with missing as special cases.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 8, 2018, 6:02pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/10 "2018-02-08T18:02:40Z")
It’s really easy to create your own `skipnan` function. Actually you can just do `Base.Iterators.filter(!isnan, x)`, I suspect it will be as efficient as `skipmissing`.
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [February 8, 2018, 6:16pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/11 "2018-02-08T18:16:31Z")
There was a lengthly discussion on `missing` and `NaN` not too long ago. They really represent different concepts. I would argue that the semantics and proper representation of the data beats the slight computational efficiency it might attain. It should be better to work with the `missing` rather than `NaN`, but if what you truly want is whether the data is available for computation you could do something like
```julia
using Missings
isvalidforcomputation(obj::Any) = false
isvalidforcomputation(obj::Real) = !isnan(obj)
isvalidforcomputation.([one(Int64), one(Float64), 'a', "hi", complex(one(Float64)), NaN, missing])
```
then you can make a `skipnv` function as @nalimilan suggested for `skipnan`.
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 8, 2018, 11:34pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/12 "2018-02-08T23:34:36Z")
apologies. more beginner’s question. so I want to write some functions, the julia way (yet still not too clever to remain readable), that convert every NaN to missings, where appropriate.
```julia
julia> using DataFrames ## includes Missing
julia> XFloat64= eltype([1.0, missing])
Union{Float64, Missings.Missing}
julia> VXFloat64= Vector{XFloat64}; VFloat64= Vector{Float64};
julia> function nan2missing!(x::VXFloat64)::VXFloat64
x= ifelse.( isnan.(x), missing, x )
end#function nan2missing
nan2missing! (generic function with 1 method)
julia> function nan2missing!(x::VFloat64)::VXFloat64
nan2missing!(convert(VXFloat64, x))
end#function nan2missing
nan2missing! (generic function with 1 method)
## can the above two function defs with one?
julia> nan2missing!( [1.0, NaN, 2.0] )
3-element Array{Union{Float64, Missings.Missing},1}:
1.0
missing
2.0
julia> nan2missing!( [1.0, NaN, missing, 3.0])
## epic fail about ifelse
```
Ultimately, I want to go here:
```julia
julia> function nan2missing!(din::DataFrames.DataFrame)::DataFrames.DataFrame
for col in eachcol(din)
if (eltype(col) <: Float64)
df[Symbol(col)]=nan2missing!(df[Symbol(col)])
end#if
end#for
din
end#function nan2missing
julia> df= DataFrame( n1=[1,2,3,4], n2=x1 ) ## example
julia> nan2missing!(df); df
3×2 DataFrames.DataFrame
│ Row │ n1 │ n2 │
├─────┼────┼─────┤
│ 1 │ 1 │ 1.0 │
│ 2 │ 2 │ missing │
│ 3 │ 3 │ 3.0 │
```
Apologies—this reflects multiple levels of lack of understanding by a beginner.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 9, 2018, 9:40am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/13 "2018-02-09T09:40:15Z")
> [@iwelch](#):
>
> ```julia
> function nan2missing!(x::VXFloat64)::VXFloat64
> x= ifelse.( isnan.(x), missing, x )
> end#function nan2missing
>
> ```
The problem is that this function does not change `x`, it just assigns a new vector to the local variable `x` and returns it. In fact you cannot change the type of `x` from inside the function, you can only change its values.
When looping over the data frame, instead of:
```julia
df[Symbol(col)]=nan2missing!(df[Symbol(col)])
```
do:
```julia
v = allowmissing(df[Symbol(col)])
v[isnan.(v)] = missing
df[Symbol(col)] = v
```
or just (using `recode` from CategoricalArrays):
```julia
df[Symbol(col)] = recode(df[Symbol(col)], NaN => missing)
```
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 9, 2018, 5:39pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/14 "2018-02-09T17:39:08Z")
dear nalimilan—unfortunately, these were _not_ the problems.
```julia
using DataFrames ## includes Missing
( XFloat64= eltype([1.0, missing]);
VXFloat64= Vector{XFloat64}; VFloat64= Vector{Float64}; )
function nan2missing!(x::VXFloat64)::VXFloat64
x= allowmissing(x) ## random attempt; makes no difference
ifelse.( isnan.(x), missing, x )
end#function nan2missing!(VXFloat64)
function nan2missing!(x::VFloat64)::VXFloat64
nan2missing!(convert(VXFloat64, x))
end#function nan2missing!(VFloat64)
println( "plain vector works: ", nan2missing!( [1.0, NaN, 2.0] ) )
println( "recode works: ", recode( [1.0, NaN, missing, 4.0], NaN => missing ))
try
println( "union-vector works: ", nan2missing!( [1.0, NaN, missing, 3.0]) )
catch ; println("union-vector fails ") ; end
df= DataFrame( n1=[1,2,3,missing], n2=[1.0,2.0,3.0,missing],
n3=[1.0, NaN, 2.0, missing], n4=[NaN, 2.0, 3.0, 4.0] )
function nan2missing!(din::DataFrames.DataFrame)::DataFrames.DataFrame
for col in eachcol(din)
if (eltype(col) <: Float64)
df[Symbol(col)]= recode(df[Symbol(col)], NaN => missing)
## or v=allowmissing(df[Symbol(col)]); v[isnan.(v)= missing; df[Symbol(col)]= v
end#if
end#for
din
end#function nan2missing!(Dataframes)
println(nan2missing!(df)) ## return value
println(df) ## altered value
(any(isnan.(df[:n3]))) && println("NaNs were not replaced.")
```
relevant output is
```julia
plain vector works: Union{Float64, Missings.Missing}[1.0, missing, 2.0]
recode works: Union{Float64, Missings.Missing}[1.0, missing, missing, 4.0]
union-vector fails
4×4 DataFrames.DataFrame
│ Row │ n1 │ n2 │ n3 │ n4 │
├─────┼─────────┼─────────┼─────────┼─────┤
│ 1 │ 1 │ 1.0 │ 1.0 │ NaN │
│ 2 │ 2 │ 2.0 │ NaN │ 2.0 │
│ 3 │ 3 │ 3.0 │ 2.0 │ 3.0 │
│ 4 │ missing │ missing │ missing │ 4.0 │
NaNs were not replaced.
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 9, 2018, 6:21pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/15 "2018-02-09T18:21:45Z")
Try with `for (col, v) in eachcol(din)`.
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 9, 2018, 10:31pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/16 "2018-02-09T22:31:44Z")
sorry, the answer is not at all obvious. (
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [February 10, 2018, 7:04am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/17 "2018-02-10T07:04:37Z")
For an in-place function on needs to override the values rather than allocate a new object. The assignment à la `df = DataFrame()` does not over-wrtie `df`, but `df[:,:] = DataFrame()` does as you are replacing the values in `df`. If you look at my in-place function or @nalimilan suggestion:
```julia
function replace_NaN_to_missing!(df::AbstractDataFrame)
for (name, col) ∈ eachcol(df)
df[name] = recode(col, NaN => missing)
end
end
```
The way this in-place function works is that rather than assigning `df` an object it overrides each of its values (i.e., each column of df with the desired values).
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [February 10, 2018, 5:50pm UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/18 "2018-02-10T17:50:41Z")
thanks. I think the following works for me
```julia
function NaN2missing!(df::DataFrame)
for (name, col) in eachcol(df)
if ((eltype(col) == Float64) && (any(isnan.(col))))
df[Symbol(name)]= allowmissing(df[Symbol(name)])
end#if
df[name] = recode(col, NaN => missing) ## also works on Union type
end#for
df
end#function NaN_to_missing!
```
It does not work on NaN32.
The change from AbstractDataFrame to DataFrame is due to my lack of understanding where else the former would be used, or what the advantages are.
thank you everybody.
---
### Author:  [@Nosferican](https://discourse.julialang.org/u/Nosferican)
#### Post date: [February 11, 2018, 12:03am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/19 "2018-02-11T00:03:03Z")
`AbstractDataFrame` is just the “proper” abstract type. Its children are `DataFrames` and `SubDataFrame`. If the code should work with `SubDataFrame` as well, then `AbstractDataFrame` should be used rather than `DataFrame`.
`allowmissing!(df::DataFrame, cols::AbstractVector{<:Union{Integer, Symbol}})` allows for in-place transformation of the column which might be desirable.
You don’t need the `Symbol(name)` as `name` is already the `Symbol` to query the column.
In order to generalize the code for `Float64` and `Float32`, you could do something like
```julia
if ((eltype(col) <: AbstractFloat) && (any(isnan.(col))))
if ((eltype(col) <: Real) && (any(isnan.(col))))
```
---
### Author:  [@ivo\_welch](https://discourse.julialang.org/u/ivo_welch)
#### Post date: [February 11, 2018, 1:45am UTC](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916/20 "2018-02-11T01:45:09Z")
thx, N. this was great help. I hope the following is a well-coded function in the julia way of life, using its facilities proper. I hope the recode use:
```julia
using DataFrames;
function NaN2missing!(df::DataFrame)
for (name, col) in eachcol(df)
if ((eltype(col) <: AbstractFloat) && (any(isnan.(col))))
allowmissing!(df, name)
end#if
recode!(df[name], NaN => missing) ## also works on Union type
end#for
df
end#function NaN2missing!
```
Here is a sample use:
```julia
df= DataFrame( n1=[1,2,3,missing], n2=[1.0,2.0,3.0,missing],
n3=[1.0, NaN, 2.0, missing], n4=[NaN, 2.0, 3.0, 4.0],
n5=Vector{Float32}( [1.0,NaN,3.0,4.0] ) )
showcols(df)
println("\n\n", NaN2missing!(df), "\n\n" );
showcols(df)
```
producing output of
```julia
4×5 DataFrames.DataFrame
│ Col # │ Name │ Eltype │ Missing │ Values │
├───────┼──────┼──────────────────────────────────┼─────────┼─────────────────┤
│ 1 │ n1 │ Union{Int64, Missings.Missing} │ 1 │ 1 … missing │
│ 2 │ n2 │ Union{Float64, Missings.Missing} │ 1 │ 1.0 … missing │
│ 3 │ n3 │ Union{Float64, Missings.Missing} │ 1 │ 1.0 … missing │
│ 4 │ n4 │ Float64 │ 0 │ NaN … 4.0 │
│ 5 │ n5 │ Float32 │ 0 │ 1.0 … 4.0 │
4×5 DataFrames.DataFrame
│ Row │ n1 │ n2 │ n3 │ n4 │ n5 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1 │ 1 │ 1.0 │ 1.0 │ missing │ 1.0 │
│ 2 │ 2 │ 2.0 │ missing │ 2.0 │ missing │
│ 3 │ 3 │ 3.0 │ 2.0 │ 3.0 │ 3.0 │
│ 4 │ missing │ missing │ missing │ 4.0 │ 4.0 │
4×5 DataFrames.DataFrame
│ Col # │ Name │ Eltype │ Missing │ Values │
├───────┼──────┼──────────────────────────────────┼─────────┼─────────────────┤
│ 1 │ n1 │ Union{Int64, Missings.Missing} │ 1 │ 1 … missing │
│ 2 │ n2 │ Union{Float64, Missings.Missing} │ 1 │ 1.0 … missing │
│ 3 │ n3 │ Union{Float64, Missings.Missing} │ 2 │ 1.0 … missing │
│ 4 │ n4 │ Union{Float64, Missings.Missing} │ 1 │ missing … 4.0 │
│ 5 │ n5 │ Union{Float32, Missings.Missing} │ 1 │ 1.0 … 4.0 │
```
[Next page](https://discourse.julialang.org/t/iterate-over-all-numeric-columns-in-dataframes/8916.md?page=2)