# Replacing some DataFrame values based on their type, for multiple columns - limits of the df.colname syntax

**URL:** <https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541>\
**Category:** New to Julia\
**Created:** [May 22, 2024, 2:07am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541 "2024-05-22T02:07:47Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjmat](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@mjmat](https://discourse.julialang.org/u/mjmat)\
**Post date:** [May 22, 2024, 2:07am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/1 "2024-05-22T02:07:47Z")

</div>

I have a DataFrame where missing values are indicated by somewhat random strings instead of real numbers, so the only way to know that a value is missing is to test whether it is not a Float64. I am trying to replace these random strings by the “proper” missing indicator.  
I found a satisfactory (to me!) method to do this one column at a time, but I find the resulting code really not easy to read when I try to apply it to all variables in the DataFrame.  
Here is an example

```julia
df = DataFrame(a = [1.0, 2.0, "N/A", 3.0, "bad"], b = [4.0, "error", 5.0, 6.0, 7.0])
# One column at a time (satisfactorily readable to me)
df.a[typeof.(df.a) .!= Float64] .= missing
# For all columns (not very readable to me, because of the df[:,colname] syntax)
for colname in names(df)
    df[typeof.(df[:, varname]) .!= Float64, colname] .= missing
end

```

I wish I could use the `df.colname` syntax in the second version, when colname is in a string variable, but I can’t figure out how to do that. And writing `df[:, colname]` leads to the above code, which I, for one, do not find very readable. In Matlab, I could have written df.(colname), adding parentheses to indicate that colname is not the actual name of the column but a string variable containing that column name.

I am opened to solutions with `transform()` or `@transform` but, to me (coming from Matlab), what I have found so far is even less readable.

Thanks!

---

<div class="post-metadata">

**Author:** ![Jollywatt](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jollywatt/32/202198_2.png) [@Jollywatt](https://discourse.julialang.org/u/Jollywatt)\
**Post date:** [May 22, 2024, 6:06am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/2 "2024-05-22T06:06:28Z")

</div>

Where is your data coming from? You should almost certainly move this tidyihg step further up your pipeline, e.g., when parsing a CSV, than to have a DataFrame with columns of eltype Any.

---

<div class="post-metadata">

**Author:** ![Jollywatt](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jollywatt/32/202198_2.png) [@Jollywatt](https://discourse.julialang.org/u/Jollywatt)\
**Post date:** [May 22, 2024, 6:07am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/3 "2024-05-22T06:07:22Z")

</div>

(Sorry for giving advice instead of answering the question!)

---

<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:** [May 22, 2024, 8:57am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/4 "2024-05-22T08:57:50Z")

</div>

I would write it like this:

```julia
julia> replace_non_floats(x) = [xᵢ isa Number ? xᵢ : missing for xᵢ ∈ x]
replace_non_floats (generic function with 1 method)

julia> mapcols!(replace_non_floats, df)
5×2 DataFrame
 Row │ a b
     │ Float64? Float64?
─────┼──────────────────────
   1 │ 1.0 4.0
   2 │ 2.0 missing
   3 │ missing 5.0
   4 │ 3.0 6.0
   5 │ missing 7.0

```

Which has the additional benefit that it creates a narrower vector type if possible (your code keeps the columns `Any` which as Joseph says is usually a bad idea).

---

<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:** [May 22, 2024, 9:17am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/5 "2024-05-22T09:17:58Z")

</div>

Try this sintax(not tested I am on phone)

```julia
df."$varname"

```

But nilshg advice is a better way to follows

---

<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:** [May 22, 2024, 9:32am UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/6 "2024-05-22T09:32:03Z")

</div>

Alternative solution with TableTransforms.jl:

```julia
df |> Replace(x -> !(x isa Float64) => missing)

```

---

<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:** [May 22, 2024, 12:34pm UTC](https://discourse.julialang.org/t/replacing-some-dataframe-values-based-on-their-type-for-multiple-columns-limits-of-the-df-colname-syntax/114541/7 "2024-05-22T12:34:36Z")

</div>

> [@mjmat](#):
>
> I am opened to solutions with `transform()`

```julia

julia> df=DataFrame((c1=[1,"b",2],c2=[21, 22, "cc"]))
3×2 DataFrame
 Row │ c1 c2  
     │ Any Any
─────┼──────────
   1 │ 1 21
   2 │ b 22
   3 │ 2 cc

julia> transform!(df, Cols(:).=>(c->[r isa Number ? r : missing for r ∈ c]).=>identity)
3×2 DataFrame
 Row │ c1 c2      
     │ Int64? Int64?
─────┼──────────────────
   1 │ 1 21
   2 │ missing 22
   3 │ 2 missing

```

```julia
julia> transform!(df, Cols(:).=>(c->[r isa Number ? r : missing for r ∈ c]),renamecols=false)
3×2 DataFrame
 Row │ c1 c2      
     │ Int64? Int64?
─────┼──────────────────
   1 │ 1 21
   2 │ missing 22
   3 │ 2 missing

```