# Transform counts to binary variable in DataFrame

**URL:** <https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044>\
**Category:** General Usage\
**Tags:** dataframes, data\_structures\
**Created:** [October 1, 2021, 1:21pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044 "2021-10-01T13:21:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [October 1, 2021, 1:21pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/1 "2021-10-01T13:21:26Z")

</div>

I have a DataFrame containing counts of a binary variable (yes/no). I would like to code the counts as instances of 1 = yes and no = 0.

Here is a simple example of the Dataframe with the same structure as my dataset:

**initial DataFrame**

```julia
 Row │ subject response count 
     │ Int64 String Int64 
─────┼──────────────────────────
   1 │ 1 yes 2
   2 │ 1 no 0
   3 │ 2 yes 2
   4 │ 2 no 1

```

The example below shows the desired result after coding counts of yes to 1 and counts of no to 0

**desired DataFrame**

```julia
 Row │ subject y     
     │ Int64 Int64 
─────┼────────────────
   1 │ 1 1
   2 │ 1 1
   3 │ 2 1
   4 │ 2 1
   5 │ 2 0

```

How can this be accomplished with DataFrames.jl or another package?

Thank you.

**MWE**

```julia
using DataFrames

df1 = DataFrame(
    subject = [1,1,2,2],
    response = ["yes","no","yes","no"],
    count = [2,0,2,1]
)

df2 = DataFrame(
    subject = [1,1,2,2,2],
    y = [1,1,1,1,0],
)

```

---

<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:** [October 1, 2021, 1:59pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/2 "2021-10-01T13:59:39Z")

</div>

Here’s one way:

```julia
df2 = combine(df1, :subject, [:response, :count] =>
                             ByRow((r, c) -> fill(r == "yes", c)) =>
                             :y)

julia> df3 = flatten(df2, :y)
5×2 DataFrame
 Row │ subject y     
     │ Int64 Bool  
─────┼────────────────
   1 │ 1 true
   2 │ 1 true
   3 │ 2 true
   4 │ 2 true
   5 │ 2 false

```

This makes `Bool` values (which are also a type of integer). If you really want Int64, you can change `r == "yes"` to `Int64(r == "yes")` or `r == "yes" ? 1 : 0`.

---

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [October 1, 2021, 2:13pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/3 "2021-10-01T14:13:40Z")

</div>

Very nice! Thank you.

---

<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:** [October 1, 2021, 2:13pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/4 "2021-10-01T14:13:56Z")

</div>

Here is a solution with DataFramesMeta

```julia
julia> using DataFramesMeta

julia> df1 = DataFrame( subject = [1,1,2,2],
           response = ["yes","no","yes","no"],
           count = [2,0,2,1]
       );

julia> @chain df1 begin 
           @rselect begin   
               :subject
               :y = fill(:response == "yes", :count)
           end
           flatten(:y)
       end
5×2 DataFrame
 Row │ subject y     
     │ Int64 Bool  
─────┼────────────────
   1 │ 1 true
   2 │ 1 true
   3 │ 2 true
   4 │ 2 true
   5 │ 2 false

```

---

<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:** [October 1, 2021, 3:04pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/5 "2021-10-01T15:04:44Z")

</div>

One more approach, inspired by [this post](https://discourse.julialang.org/t/repeating-with-function-repeat-array-elements-using-a-vector-of-numbers/21435/2):

```julia
v1 = vcat(fill.(df1.subject, df1.count)...)
v2 = vcat(fill.(df1.response, df1.count)...) .== "yes"
df2 = DataFrame(subject = v1, y = Int64.(v2))

5×2 DataFrame
 Row │ subject y     
     │ Int64 Int64 
─────┼────────────────
   1 │ 1 1
   2 │ 1 1
   3 │ 2 1
   4 │ 2 1
   5 │ 2 0

```

---

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [October 1, 2021, 3:55pm UTC](https://discourse.julialang.org/t/transform-counts-to-binary-variable-in-dataframe/69044/6 "2021-10-01T15:55:34Z")

</div>

@rafael.guerra, @pdeffebach, thank you for your solutions. These are all great.