# How do I not skip missing values when using stack / unstack for pivot tables

**URL:** <https://discourse.julialang.org/t/how-do-i-not-skip-missing-values-when-using-stack-unstack-for-pivot-tables/43238>\
**Category:** General Usage\
**Created:** [July 17, 2020, 1:04pm UTC](https://discourse.julialang.org/t/how-do-i-not-skip-missing-values-when-using-stack-unstack-for-pivot-tables/43238 "2020-07-17T13:04:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Julia1](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Julia1](https://discourse.julialang.org/u/Julia1)\
**Post date:** [July 17, 2020, 1:04pm UTC](https://discourse.julialang.org/t/how-do-i-not-skip-missing-values-when-using-stack-unstack-for-pivot-tables/43238/1 "2020-07-17T13:04:31Z")

</div>

Hi how’s it going,

Here’s an example to better explain what I mean:

```julia
df = DataFrame(:user=>["bob","bob","phil","phil"],:count=>["one","two","two",missing])
var1 = :user
var2 = :count

long_df = by(df,[var1,var2],var2=>length)
wide_df = unstack(long_df,var2,Symbol(var2,:_length))

for col in names(wide_df)[2:end]
    wide_df[ismissing.(wide_df[col]), col] = 0
end

```

returns

![Screenshot from 2020-07-17 09-02-32](https://global.discourse-cdn.com/julialang/original/3X/f/6/f6cd754a1b2c1b2bcf9b00c2bfb5a37010704950.png)

whereas I need it to return

![Screenshot from 2020-07-17 09-04-06](https://global.discourse-cdn.com/julialang/original/3X/9/2/9201adc371870989ab802fbab3be33f30eec2855.png)

how would I go about doing this?

Thank you.

---

<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:** [July 17, 2020, 1:20pm UTC](https://discourse.julialang.org/t/how-do-i-not-skip-missing-values-when-using-stack-unstack-for-pivot-tables/43238/2 "2020-07-17T13:20:34Z")

</div>

So.

Firstly, FreqTables.jl were designed to exactly handle the case you have:

```julia
julia> using FreqTables

julia> freqtable(df, var1, var2)
2×3 Named Array{Int64,2}
user ╲ count │ one two missing
─────────────┼──────────────────────────
bob │ 1 1 0
phil │ 0 1 1

```

the benefit is that you can go for more than two dimensions.

Now, if you want to stick to DataFrames.jl just do:

```julia
julia> combine(groupby(df, var1), [var2 => (x -> count(isequal(u), x)) => string(u) for u in unique(df[!, var2])])
2×4 DataFrame
│ Row │ user │ one │ two │ missing │
│ │ String │ Int64 │ Int64 │ Int64 │
├─────┼────────┼───────┼───────┼─────────┤
│ 1 │ bob │ 1 │ 1 │ 0 │
│ 2 │ phil │ 0 │ 1 │ 1 │

```

Finally if you wanted to start with `long_df` you can do (but indeed it is cumbersome):

```julia
julia> coalesce.(unstack(transform(long_df, var2 => ByRow(string) => var2), var2, Symbol(var2, "_length")), 0)
2×4 DataFrame
│ Row │ user │ missing │ one │ two │
│ │ String │ Int64 │ Int64 │ Int64 │
├─────┼────────┼─────────┼───────┼───────┤
│ 1 │ bob │ 0 │ 1 │ 1 │
│ 2 │ phil │ 1 │ 0 │ 1 │

```

---

<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:** [July 17, 2020, 1:26pm UTC](https://discourse.julialang.org/t/how-do-i-not-skip-missing-values-when-using-stack-unstack-for-pivot-tables/43238/3 "2020-07-17T13:26:22Z")

</div>

I think the easiest thing would just be to impute some value and rename the column after, which is cumbersome for sure. Maybe file an issue for a feature request so this doesn’t get lost.

No guarantee it will be implemented, but we can discuss the merits in an issue.