# Filling in missing values within a group using values carried forward and backward

**URL:** <https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [August 4, 2023, 9:13pm UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505 "2023-08-04T21:13:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![George9000](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/george9000/32/23619_2.png) [@George9000](https://discourse.julialang.org/u/George9000)\
**Post date:** [August 4, 2023, 9:13pm UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505/1 "2023-08-04T21:13:01Z")

</div>

Given a DataFrame with fields `:a` and `:b` such that `:a` is a group variable and `:b` is a value variable which has many missing values. What is the optimal method to fill the missing values in `:b` to satisfy the following condition? Replace missing `:b` value for each group in `:a` with the closest non-missing row value in `:b` _within that group_.

Here is one method. Are there better alternatives? The method employs a function defined within a loop over groups. The function references and updates a default value within the loop. A `transform!` within the loop uses the function to update the group/parent data frame.

```julia
# first, the df
let
    df = DataFrame(a=repeat([1, 2, 3, 4], inner=[10]), b = repeat([[missing, missing, 50, missing, missing]; 10; repeat([missing], 4)], outer=[4])) 
end
# now the update method
let
    df = DataFrame(a=repeat([1, 2, 3, 4], inner=[10]), b = repeat([[missing, missing, 50, missing, missing]; 10; repeat([missing], 4)], outer=[4])) 
    gdf = groupby(df, :a)
    for g in gdf
        dvalue = coalesce(g.b...)
        function cvalue(v)
            cvalue = coalesce(v, dvalue)
            dvalue = cvalue
        end
        transform!(g, :b => ByRow(cvalue) => :c)
    end
    return df
end

```

Thanks.

---

<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:** [August 4, 2023, 9:50pm UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505/2 "2023-08-04T21:50:38Z")

</div>

```julia
filldown(v)=accumulate((x,y)->coalesce(y,x), v,init=v[1])
fillup(v)=reverse(filldown(reverse(v)))

combine(combine(gdf, :b=>filldown =>:b),:b=>fillup)

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [August 4, 2023, 11:40pm UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505/3 "2023-08-04T23:40:21Z")

</div>

Your example does not do “the closest non-missing row value” but rather carries forward values and falls back to backward if the first observations are missing. If this is what you want apart from what @rocco_sprmnt21 proposed you can use a combination of `locf` and `nocb` functions from Impute.jl.

---

<div class="post-metadata">

**Author:** ![George9000](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/george9000/32/23619_2.png) [@George9000](https://discourse.julialang.org/u/George9000)\
**Post date:** [August 5, 2023, 3:15am UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505/4 "2023-08-05T03:15:52Z")

</div>

Both solutions work. And in working through them, I learned more base Julia from @rocco_sprmnt21 and about a neat package from @bkamins who is also correct in restating my problem.

Thanks so much. What a great community!

Slightly modified for clarity and summarized for future seekers:

```julia
using DataFrames, Impute

filldown(v) = accumulate( (x, y) -> coalesce(y, x), v, init = v[1] )
fillup(v) = reverse(filldown(reverse(v)))

let
    df = DataFrame(a=repeat([1, 2, 3, 4], inner=[10]), b = repeat([[missing, missing, 50, missing, missing]; 10; repeat([missing], 4)], outer=[4]))

    gdf = groupby(df, :a)
    for g in gdf
        transform!(g, :b => filldown => :c)
        transform!(g, :c => fillup => :c)
    end
    return df
end

let
    df = DataFrame(a=repeat([1, 2, 3, 4], inner=[10]), b = repeat([[missing, missing, 50, missing, missing]; 10; repeat([missing], 4)], outer=[4]))
    gdf = groupby(df, :a)
    for g in gdf
        transform!(g, :b => Impute.locf => :c)
        transform!(g, :c => Impute.nocb => :c)
    end
    return df
end

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [August 5, 2023, 11:54am UTC](https://discourse.julialang.org/t/filling-in-missing-values-within-a-group-using-values-carried-forward-and-backward/102505/5 "2023-08-05T11:54:10Z")

</div>

Note that you can also do:

```julia
df = DataFrame(a=repeat([1, 2, 3, 4], inner=[10]), b = repeat([[missing, missing, 50, missing, missing]; 10; repeat([missing], 4)], outer=[4]))
transform!(groupby(df, :a), :b => Impute.nocb∘Impute.locf => :c)

```