Cannot fix `nothing` in summation

Specific Domains Data
1

Hello,

I am trying to perform the following transformation on a DataFrame:

@transform! df_20_P_raw[:data] begin
    # Calculate the sum of the relevant columns, replacing missing values with 0
    :pensions_balance_professional = sum(coalesce.((AsTable(r"^pensions_balance_professional_"))..., 0))
    :pensions_balance_voluntary    = sum(coalesce.((AsTable(r"^pensions_balance_voluntary_"))..., 0))
end;

I have tried several ways, getting different errors each time, but all related to summing nothing and missing values. Right no,w the error I am getting is:

ERROR: MethodError: no method matching +(::Nothing, ::Nothing)
The function `+` exists, but no method is defined for this combination of argument types.

Any suggestion about how to overcome it?

2

Well, what do you think adding nothing or missing values should give? It seems you want to replace missings with zero, so you might want to do the same with nothing. i.e. replace(vector, nothing => 0)

3

Thanks, but it does not work.
Prior I have a function that at some point has:

    println("  Fixing type of numeric variables...")
    for name in current_names
        if !(name in non_num_vars) && !(eltype(selected_df[!, name]) <: Number)
            replace!(selected_df[!, name], nothing => 0.0)
            println("    Replaced 'nothing' with zeros in column '$name'.")

            try
                selected_df[!, name] = coalesce.(tryparse.(Float64, selected_df[!, name]), 0.0)
            catch
                println("    Warning: Could not convert column '$name' to numeric.")
            end
        end
    end

I am receiving the substitution messages, but they do not actually substitute.
I have also tried selected_df[!, name] .= map(x -> x === nothing ? missing : x, selected_df[!, name]) instead of replace!, but it does not work either.

4

Some variation of this, maybe?

julia> df=DataFrame(a=[1.0, 2.0, nothing, 4.0, 5.0])
5×1 DataFrame
 Row │ a      
     │ Union…
─────┼────────
   1 │ 1.0
   2 │ 2.0
   3 │
   4 │ 4.0
   5 │ 5.0

julia> df.a = ifelse.(df.a .=== nothing, 0.0, df.a)
5-element Vector{Float64}:
 1.0
 2.0
 0.0
 4.0
 5.0
5

I think you’ll have to provide an MWE, because this straightforwardly works:

julia> using DataFrames

julia> df = DataFrame(x = rand(5), y = [1, 2, nothing, 4, 5])
5×2 DataFrame
 Row │ x         y
     │ Float64   Union…
─────┼──────────────────
   1 │ 0.545902  1
   2 │ 0.119543  2
   3 │ 0.373656
   4 │ 0.389469  4
   5 │ 0.396225  5

julia> for name ∈ names(df)
           replace!(df[!, name], nothing => 0)
       end

julia> df
5×2 DataFrame
 Row │ x         y
     │ Float64   Union…
─────┼──────────────────
   1 │ 0.545902  1
   2 │ 0.119543  2
   3 │ 0.373656  0
   4 │ 0.389469  4
   5 │ 0.396225  5
6

What about skipmissing

julia> df = DataFrame(a = [1, 2, missing], b = [4, missing, missing])
3×2 DataFrame
 Row │ a        b       
     │ Int64?   Int64?  
─────┼──────────────────
   1 │       1        4
   2 │       2  missing 
   3 │ missing  missing 

julia> @rtransform df :z = sum(skipmissing(AsTable([:a, :b]),))
3×3 DataFrame
 Row │ a        b        z     
     │ Int64?   Int64?   Int64 
─────┼─────────────────────────
   1 │       1        4      5
   2 │       2  missing      2
   3 │ missing  missing      0

I would guess that you are coming from Stata. Let us know what kind of syntax you think would be easiest to replicate rowtotal.

The following helper function might be nice for you

julia> function rowtotal(x, missingval = 0)
           sum(t -> coalesce(t, missingval), x)
       end;

julia> @rtransform df :z = rowtotal(AsTable([:a, :b]))
3×3 DataFrame
 Row │ a        b        z     
     │ Int64?   Int64?   Int64 
─────┼─────────────────────────
   1 │       1        4      5
   2 │       2  missing      2
   3 │ missing  missing      0
7

And it seems like your real issue is that nothing is being emitted from tryparse on failures, rather than missing. This is indeed an annoyance. I brought this issue up here and it was rejected. The recommendation is to use something(tryparse(...), missing), i.e.

julia> nums = ["5.00", "6.00%"]
2-element Vector{String}:
 "5.00"
 "6.00%"

julia> map(nums) do n
           tryparse(Float64, n)
       end
2-element Vector{Union{Nothing, Float64}}:
 5.0
  nothing

julia> map(nums) do n
           something(tryparse(Float64, n), missing)
       end
2-element Vector{Union{Missing, Float64}}:
 5.0
  missing
8

Another thing, is that you might be missing some nice utilities in DataFrames.jl for highlighting numeric columns.

julia> df = DataFrame(a = ["dog", "cat"], b = [1, 2], c = [5.0, 6.0])
2×3 DataFrame
 Row │ a       b      c       
     │ String  Int64  Float64 
─────┼────────────────────────
   1 │ dog         1      5.0
   2 │ cat         2      6.0

julia> names(df, Number)
2-element Vector{String}:
 "b"
 "c"