# Separating a column into a variable number of possible columns

**URL:** <https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [October 1, 2021, 10:48am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035 "2021-10-01T10:48:08Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 10:48am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/1 "2021-10-01T10:48:08Z")

</div>

Hi there.

Am trying to replicate some `R` code based on a movies dataset (available here) using `Julia`’s data wrangling utilities.

What am trying to achieve is to separate/split a column into two or more columns depending on how many commas there are in the cell value. If the cell value has two commas it is going to fill in 3 new columns and if only one comma, then it would fill 2 new columns while the third column will have a missing values.

The desired result is given in `R-tidyverse` with the following code:

```r
movies <- movies %>%  
  separate(col = genre,
           into = c("genre1", "genre2", "genre3"),
           sep = ",")

```

I know that there had been a discussion on the issue and that @bkamins posted on his blog about it [here](https://bkamins.github.io/julialang/2020/12/24/minilanguage.html). I tried to use the `ByRow` in combination with `select()` as you can see here, but it does not seem to work:

```julia
@pipe movies |>
    select(_, :genre => ByRow(x -> split(x, ",")) => [:genre1, :genre2, :genre3], [:genre])

```

Any help would be greatly appreciated!

Alex

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [October 1, 2021, 11:03am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/2 "2021-10-01T11:03:15Z")

</div>

> [@Alex\_Tantos](#):
>
> `select`

Just need to use `transform` instead of `select`.

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 11:05am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/3 "2021-10-01T11:05:18Z")

</div>

Thanks for the reply! I tried it and got the same error message:

```julia
@pipe movies |>
           transform(_, :genre => ByRow(x -> split(x, ",")) => [:genre1, :genre2, :genre3], [:genre])
ERROR: ArgumentError: keys of the returned elements must be identical
Stacktrace:
 [1] _expand_to_table(res::Vector{Vector{SubString{String}}})
   @ DataFrames ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:380
 [2] select_transform!(nc::Union{Function, Pair{var"#s267", var"#s266"} where {var"#s267"<:Union{Int64, AsTable, AbstractVector{Int64}}, var"#s266"<:(Pair{var"#s164", var"#s163"} where {var"#s164"<:Union{Function, Type}, var"#s163"<:Union{DataType, Symbol, AbstractVector{Symbol}}})}, Type}, df::DataFrame, newdf::DataFrame, transformed_cols::Set{Symbol}, copycols::Bool, allow_resizing_newdf::Base.RefValue{Bool})
   @ DataFrames ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:522
 [3] _manipulate(df::DataFrame, normalized_cs::Any, copycols::Bool, keeprows::Bool)
   @ DataFrames ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:1279
 [4] manipulate(::DataFrame, ::Any, ::Vararg{Any, N} where N; copycols::Bool, keeprows::Bool, renamecols::Bool)
   @ DataFrames ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:1209
 [5] #select#384
   @ ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:847 [inlined]
 [6] #transform#386
   @ ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:913 [inlined]
 [7] transform(::DataFrame, ::Any, ::Any)
   @ DataFrames ~/.julia/packages/DataFrames/3mEXm/src/abstractdataframe/selection.jl:913
 [8] top-level scope
   @ REPL[79]:1 

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [October 1, 2021, 11:05am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/4 "2021-10-01T11:05:57Z")

</div>

> [@Alex\_Tantos](#):
>
> `, [:genre]`

also remove that and try? it works for me

---

<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, 11:09am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/5 "2021-10-01T11:09:59Z")

</div>

> [@xiaodai](#):
>
> it works for me

Did you test with a case where not all strings have 3 genres?

I think you need something like this:

```julia
df = DataFrame(genre=["a,b,c", "a,b"])

select(df, :genre =>
           ByRow(x -> get.(Ref(split(x, ',')), 1:3, missing)) =>
           [:genre1, :genre2, :genre3])

```

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 11:17am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/6 "2021-10-01T11:17:52Z")

</div>

Unfortunately, it did not work either…However, the solution by @sijo works!

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 11:18am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/7 "2021-10-01T11:18:11Z")

</div>

Thanks a lot! Works like a charm!

---

<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, 11:24am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/8 "2021-10-01T11:24:48Z")

</div>

You’re welcome!

And for reference here’s a more general version, that also works when the maximum number of genres is different from 3 (this one doesn’t use `ByRow` since it needs the whole column to find the correct maximum number of genres):

```julia
df = DataFrame(genre=["a,b,c", "a,b"])

function split_uniformly(v)
    s = split.(v, ',')
    n = maximum(length.(s))
    [NamedTuple(Symbol.("genre", 1:n) .=> get.(Ref(genres), 1:n, missing))
     for genres in s]
end

julia> select(df, :genre => split_uniformly => AsTable)
2×3 DataFrame
 Row │ genre1 genre2 genre3     
     │ SubStrin… SubStrin… SubStrin…? 
─────┼──────────────────────────────────
   1 │ a b c
   2 │ a b missing   

```

---

<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, 11:30am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/9 "2021-10-01T11:30:52Z")

</div>

This can be done with DataFramesMeta relatively easily as well

```julia
@chain df begin
    @rtransform $[:genre1, :genre2, :genre3] = split(:genres, ",")
end

```

Though I guess this will fail with your exact example. To make it work I think you need the example above

```julia
@chain df begin
    @rtransform $[:genre1, :genre2, :genre3] = begin
        x = split(:genres, ",")
        get.(Ref(x), 1:3, missing)
    end
end

```

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 12:08pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/10 "2021-10-01T12:08:47Z")

</div>

Thanks a lot! I guess with great power comes great responsibility!

---

<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:** [October 1, 2021, 12:31pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/11 "2021-10-01T12:31:37Z")

</div>

We made expecting a constant number of columns produced decision on purpose (to catch bugs in the code). Do you think being flexible would be better and if so why?

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 12:53pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/12 "2021-10-01T12:53:49Z")

</div>

Hi!  
It is a very common use case scenario in the humanities to read in a spreadsheet file that has one or more columns with free text answers to a questionnaire. For instance, for the question “which languages do you speak?” we get responses with variable answers, e.g. some people answer “German, French” and others respond with “German, French, Greek” while others with just “English”. The ideal is to be able to transform the responses in a way that the answers that are “stored” in a single column are spread into separate columns with second, third, fourth language etc. while leaving missing values for those who only speak one language.  
I hope I was clear enough.

PS.: By the way, thanks a lot for the effort you have been putting on improving DataFrames.jl and the tutorial this summer!

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [October 1, 2021, 1:08pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/13 "2021-10-01T13:08:11Z")

</div>

Would it be ok to keep a column of vectors. Then count the unique languages in the vectors, then just expand them out using a function with a fixed set of languages? That feels a more natural way to solve this in a readable way.

---

<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:** [October 1, 2021, 1:10pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/14 "2021-10-01T13:10:24Z")

</div>

yes - either what @xiaodai describes is natural or keep a column of vectors (without expanding it into multiple columns). The problem with expansion into undefined number of generic columns is that later it is not fully clear how such data frame should be used.

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 1, 2021, 1:15pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/15 "2021-10-01T13:15:52Z")

</div>

Thanks to both you and @xiaodai ! The solutions you suggest may indeed be more natural to follow. It’s the R instincts that came out immediately to solve such a problem.  
The other thing is that the ranking of the languages play a role. People who prefer to write “English, German” are supposed to be different from people that respond with “German, English”. However, I suppose I could further manipulate the column of vectors to keep that in mind.  
Thanks again!

---

<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:** [October 3, 2021, 9:51am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/16 "2021-10-03T09:51:13Z")

</div>

just for fun

```julia

vcat(DataFrame.(permutedims.(split.(df1.genre,",")),:auto)..., cols=:union)

```

---

<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:** [October 3, 2021, 10:15am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/17 "2021-10-03T10:15:51Z")

</div>

This is a good point. In `vcat`, `append!` and `push!` we allow variable number of columns per item.

---

<div class="post-metadata">

**Author:** ![Alex\_Tantos](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alex_tantos/32/10636_2.png) [@Alex\_Tantos](https://discourse.julialang.org/u/Alex_Tantos)\
**Post date:** [October 3, 2021, 10:21am UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/18 "2021-10-03T10:21:08Z")

</div>

Thanks! Really cool! I get the logic, but I guess I will have to see exactly what `permutedims()` does, since I didn’t know it.

---

<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:** [October 3, 2021, 12:03pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/19 "2021-10-03T12:03:41Z")

</div>

I wonder if in a situation like df2, the expected result is the following and not the one that derives from the previous solutions …

```julia

df2 = DataFrame(genre=["a,b,c", "a,b", "a,c","b,d"])

## new split_uni

julia> select(df2, :genre => split_uniformly => AsTable)
4×4 DataFrame
 Row │ a b c d
     │ SubStrin…? SubStrin…? SubStrin…? SubStrin…? 
─────┼────────────────────────────────────────────────
   1 │ a b c missing    
   2 │ a b missing missing    
   3 │ a missing c missing    
   4 │ missing b missing d

```

---

<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:** [October 3, 2021, 12:08pm UTC](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035/20 "2021-10-03T12:08:26Z")

</div>

permutedims transforms a column vector into a row vector (== matrix).  
i tried to use the transpose function or the ’ but i didn’t get what i wanted

[Next page](https://discourse.julialang.org/t/separating-a-column-into-a-variable-number-of-possible-columns/69035.md?page=2)