# Summing and counting values in a df by row

**URL:** <https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753>\
**Category:** New to Julia\
**Tags:** dataframes\
**Created:** [May 8, 2021, 2:14am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753 "2021-05-08T02:14:39Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Geoffysicist](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/geoffysicist/32/24859_2.png) [@Geoffysicist](https://discourse.julialang.org/u/Geoffysicist)\
**Post date:** [May 8, 2021, 2:14am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/1 "2021-05-08T02:14:39Z")

</div>

I have a dataframe of the form:

```julia
4×4 DataFrame
 Row │ ID Q1 Q2 Q3
     │ Int64 Int64 Int64? Int64?
─────┼────────────────────────────────
   1 │ 1 1 0 1
   2 │ 2 1 1 0
   3 │ 3 0 0 missing
   4 │ 4 0 missing 1

```

The data are quiz answers, and `missing` indicates that the person did not attempt the question.

I want to add two columns:

- `:score` for the number of correct responses; a\* nd,
- `:attempt` for the number of questions attempted.

Expected result

```julia
4×6 DataFrame
 Row │ ID Q1 Q2 Q3 score attempts
     │ Int64 Int64 Int64? Int64? Int64 Int64
─────┼────────────────────────────────────────────────
   1 │ 1 1 0 1 2 3
   2 │ 2 1 1 0 2 3
   3 │ 3 0 0 missing 0 2
   4 │ 4 0 missing 1 1 2

```

My best effort so far is:

```julia
julia> df1 = transform(df, r"Q" => (+) => :score)

4×5 DataFrame
 Row │ ID Q1 Q2 Q3 score
     │ Int64 Int64 Int64? Int64? Int64?
─────┼─────────────────────────────────────────
   1 │ 1 1 0 1 2
   2 │ 2 1 1 0 2
   3 │ 3 0 0 missing missing
   4 │ 4 0 missing 1 missing

```

which doesn’t give me totals for rows with missing values. I have tried various permutations of `skipmissing` without success. Also I can’t quite work out an expression to count the non-missing values in each row.

Thanks in advance.

EDIT:  
@pdeffebach provides an efficient solution to the `:score` problem. I also found a possibly less efficient solution which I can also apply to the `:attempts` problem.

```julia
transform(df, r"Q" => ByRow((x...) -> sum(skipmissing(x))) => :sum)
transform(df1, r"Q" => ByRow((x...) -> count(>(-1),skipmissing(x))) => :attempts)

```

---

<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:** [May 8, 2021, 3:02am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/2 "2021-05-08T03:02:41Z")

</div>

This is a tricky one, and is only really answered by an in depth understanding of the `src => fun => dest` syntax.

you want

```julia
julia> transform(df, AsTable([:a, :b]) => ByRow(sum ∘ skipmissing) => :score)
2×3 DataFrame
 Row │ a b score
     │ Int64 Int64? Int64
─────┼───────────────────────
   1 │ 1 3 4
   2 │ 2 missing 2

```

the `AsTable` combined with `ByRow` means the function takes a named tuple. `skipmissing` works just fine on a named tuple.

---

<div class="post-metadata">

**Author:** ![Geoffysicist](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/geoffysicist/32/24859_2.png) [@Geoffysicist](https://discourse.julialang.org/u/Geoffysicist)\
**Post date:** [May 8, 2021, 6:32am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/3 "2021-05-08T06:32:55Z")

</div>

Thanks again @pdeffebach  
It’s clear I have to get my head around the `src => fun => dest` syntax.

Your solution worked, though I did just find an alternative solution which also worked for me. Not sure if it is as efficient but for me it is more comprehensible.

```julia
transform(df, r"Q" => ByRow((x...) -> sum(skipmissing(x))) => :sum)

```

---

<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:** [May 8, 2021, 10:04am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/4 "2021-05-08T10:04:10Z")

</div>

Another way:

```julia
using DataFrames

ID = 1:4
Q1 = [1, 1, 0, 0]
Q2 = [0, 1, 0, missing]
Q3 = [1, 0, missing, 1]
df = DataFrame(ID=ID, Q1=Q1, Q2=Q2, Q3=Q3)

df[:score] = sum(eachcol(coalesce.(df[Not(:ID)],0)))
df[:attempts] = sum((eachcol(.~ismissing.(df[[:Q1,:Q2,:Q3]]))))

julia> df
4×6 DataFrame
│ Row │ ID │ Q1 │ Q2 │ Q3 │ score │ attempts │
│ │ Int64 │ Int64 │ Int64? │ Int64? │ Int64 │ Int64 │
├─────┼───────┼───────┼─────────┼─────────┼───────┼──────────┤
│ 1 │ 1 │ 1 │ 0 │ 1 │ 2 │ 3 │
│ 2 │ 2 │ 1 │ 1 │ 0 │ 2 │ 3 │
│ 3 │ 3 │ 0 │ 0 │ missing │ 0 │ 2 │
│ 4 │ 4 │ 0 │ missing │ 1 │ 1 │ 2 │

```

---

<div class="post-metadata">

**Author:** ![Jake](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jake/32/46007_2.png) [@Jake](https://discourse.julialang.org/u/Jake)\
**Post date:** [January 10, 2023, 3:02am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/5 "2023-01-10T03:02:21Z")

</div>

I am trying these functions out. When all the values of a row are missing, pdeffebach’s solution works as shown:

```julia
id = 1:5
q1 = [1,1,0,0,missing]
q2 = [0,1,0,missing,missing]
q3 = [missing, missing, missing,missing, missing]
df = DataFrame(ID=id, q1=q1, q2=q2, q3=q3)
transform(df, AsTable(r"q") => ByRow(sum ∘ skipmissing) => :score)
5×5 DataFrame
 Row │ ID q1 q2 q3 score 
     │ Int64 Int64? Int64? Missing Int64
─────┼─────────────────────────────────────────
   1 │ 1 1 0 missing 1
   2 │ 2 1 1 missing 2
   3 │ 3 0 0 missing 0
   4 │ 4 0 missing missing 0
   5 │ 5 missing missing missing 0

```

A nice to have would be to have the sum of all missings also be a missing rather than a zero. This would only affect row 5 in this example. Is this possible?

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 10, 2023, 4:23am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/6 "2023-01-10T04:23:47Z")

</div>

This method seems to work:

```julia
julia> dorow(v) = foldl((r,t)->coalesce(r+t,t,r),v; init=missing);

julia> transform(df, AsTable(r"q") => ByRow(dorow) => :score)
5×5 DataFrame
 Row │ ID q1 q2 q3 score   
     │ Int64 Int64? Int64? Missing Int64?  
─────┼───────────────────────────────────────────
   1 │ 1 1 0 missing 1
   2 │ 2 1 1 missing 2
   3 │ 3 0 0 missing 0
   4 │ 4 0 missing missing 0
   5 │ 5 missing missing missing 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:** [January 10, 2023, 4:36pm UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/7 "2023-01-10T16:36:21Z")

</div>

```julia
julia> df = DataFrame(ID=ID, Q1=Q1, Q2=Q2, Q3=Q3)
5×4 DataFrame
 Row │ ID Q1 Q2 Q3      
     │ Int64 Int64? Int64? Int64?
─────┼──────────────────────────────────
   1 │ 1 1 0 1
   3 │ 3 0 0 missing
   4 │ 4 0 missing 1
   5 │ 5 missing missing missing

julia> df.score=[sum(filter(!ismissing, values(r))) for r in collect.(eachrow(df[:,Not(:ID)]))]5-element Vector{Union{Missing, Int64}}:
 2
 2
 0
 1
  missing

julia> df
5×5 DataFrame
 Row │ ID Q1 Q2 Q3 score   
     │ Int64 Int64? Int64? Int64? Int64?
─────┼───────────────────────────────────────────
   1 │ 1 1 0 1 2
   2 │ 2 1 1 0 2
   3 │ 3 0 0 missing 0
   4 │ 4 0 missing 1 1
   5 │ 5 missing missing missing missing

```

I propose a different solution. However, I would like to understand why the use of collect is necessary.

I note that a filtered missing vector produces an empty vector of type Missing.  
Why doesn’t the same thing happen for the missing tuple?

```julia

julia> v=[missing,missing,missing]
3-element Vector{Missing}:
 missing
 missing
 missing

julia> typeof(filter(!ismissing,v))
Vector{Missing} (alias for Array{Missing, 1})

julia> t=(missing,missing,missing)
(missing, missing, missing)

julia> typeof(filter(!ismissing,t))
Tuple{}

julia> sum(filter(!ismissing,v))
missing

julia> sum(filter(!ismissing,t))
ERROR: ArgumentError: reducing with add_sum over an empty collection of element type Union{} is not allowed.

```

---

<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:** [January 10, 2023, 5:54pm UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/8 "2023-01-10T17:54:26Z")

</div>

[Missings.jl](https://github.com/JuliaData/Missings.jl) has a new function called `emptymissing` which can help in this case. But it was only just merged and a new version hasn’t been released yet.

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [January 11, 2023, 9:43am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/9 "2023-01-11T09:43:30Z")

</div>

> [@rocco\_sprmnt21](#):
>
> I propose a different solution. However, I would like to understand why the use of collect is necessary.
> 
> I note that a filtered missing vector produces an empty vector of type Missing.  
> Why doesn’t the same thing happen for the missing tuple?

As you say, with `collect` you make a vector such as `[missing, missing]` of type `Vector{Missing}`. After filtering this becomes an empty vector of type `Vector{Missing}`. The sum of an empty vector is simply the “zero value” for the element type of the vector. In this case the zero value is defined as `missing`:

```julia
julia> zero(Missing) # Zero value for type Missing
missing

julia> sum(Missing[]) # Sum of empty vector of type Missing
missing

```

Without `collect`, what you have is a tuple such as `(missing, missing)`. This has type `Tuple{Missing, Missing}`. When you filter-out one value you get a tuple `(missing,)` of type `Tuple{Missing}`. When you filter-out the last value you get a tuple `()` of type `Tuple{}`. The `sum` function should return the zero value for the element type, but there is no element type left, so `sum` doesn’t work:

```julia
julia> sum(())
ERROR: ArgumentError: reducing with add_sum over an empty collection of element type Union{} is not allowed.

```

---

<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:** [January 11, 2023, 9:46am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/10 "2023-01-11T09:46:37Z")

</div>

yes.  
But why?  
I guess there are some very good reasons why this is the case, but I would like to know why the empty tuple can’t have a specific type.

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [January 11, 2023, 9:53am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/11 "2023-01-11T09:53:12Z")

</div>

A tuple is an immutable list of values, where each value can have any type. `(1, 2.0, "three")` has type `Tuple{Int64, Float64, String}`. If there is no value, there is no value type!

By the way you also cannot compute the sum of an empty vector of “unspecified” type `Any` because there is no “zero value” defined for `Any`:

```julia
julia> sum([])
ERROR: MethodError: no method matching zero(::Type{Any})

```

---

<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:** [January 12, 2023, 8:00am UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/12 "2023-01-12T08:00:45Z")

</div>

Some additional considerations that may clarify a few more details on the particular handling of tuple types.

```julia
julia> vi=[missing,missing]
2-element Vector{Missing}:
 missing
 missing

julia> typeof(vi)
Vector{Missing} (alias for Array{Missing, 1})

julia> vi2=[missing,missing,missing]
3-element Vector{Missing}:
 missing
 missing
 missing

julia> typeof(vi)==typeof(vi2)
true

julia> tm=(missing,missing)
(missing, missing)

julia> typeof(tm)
Tuple{Missing, Missing}

julia> tm2=(missing,missing,missing)
(missing, missing, missing)

julia> typeof(tm2)
Tuple{Missing, Missing, Missing}

julia> typeof(tm)==typeof(tm2)
false

```

---

<div class="post-metadata">

**Author:** ![Jake](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jake/32/46007_2.png) [@Jake](https://discourse.julialang.org/u/Jake)\
**Post date:** [January 18, 2023, 8:10pm UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/13 "2023-01-18T20:10:26Z")

</div>

Thanks all for your help. Julia is indeed a rich language, especially when coupled with experts that frequent discourse!

Here is what I ended up using, where the column to sum starts with the letter “q”:

```julia
# sum, count & rank the quizzers and write the results
dfquizzersum.Total=[sum(filter(!ismissing, values(r))) for r in collect.(eachrow(dfquizzersum[:,r"q"]))]
dfquizzersum.Count=[length(filter(!ismissing, values(r))) for r in collect.(eachrow(dfquizzersum[:,r"q"]))]
dfquizzersum.Rank = competerank(-1 .* dfquizzersum.Total) # use tiedrank for "1 2.5 2.5 4" ranking, competerank for "1 2 2 4"

```

and competerank is found in StatsBase.

---

<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:** [January 18, 2023, 10:00pm UTC](https://discourse.julialang.org/t/summing-and-counting-values-in-a-df-by-row/60753/14 "2023-01-18T22:00:44Z")

</div>

Staying within the mini-language (and complying with the title request) you could get the same result like this (I don’t know if it’s more efficient).

```julia
passmiss(r)=filter(!ismissing, collect(r))
transform(df, Cols(r"Q")=>ByRow((r...)->[sum(passmiss(r)), length(passmiss(r))] )=>[:total, :count])

```

PS  
But if you have the two columns of the total and the count, why do you complicate your life by also using the missing (instead of the 0) in the total column?  
What additional information does the use of missing give you?