# Expand row to multiple rows

**URL:** <https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872>\
**Category:** Data\
**Created:** [December 15, 2020, 1:26pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872 "2020-12-15T13:26:55Z")\
**Posts on this page:** 12\
**Page:** 1

<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:** [December 15, 2020, 1:26pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/1 "2020-12-15T13:26:55Z")

</div>

I have this data:

```
      df=DataFrame(name=["aaa","bbbb","ccc"],
                    frqty=["apple\\4\\melon\\5","plume\\12","graphes\\7\\mango\\9\\persic\\11"])

```

the goal is to get a group of rows for each name and two columns with the fruit name and quantity.

like this:

```julia

6×3 DataFrame
 Row │ name fr qty       
     │ String SubStrin… SubStrin… 
─────┼──────────────────────────────
   1 │ aaa apple 4
   2 │ aaa melon 5
   3 │ bbbb plume 12
   4 │ ccc graphes 7
   5 │ ccc mango 9
   6 │ ccc persic 11

```

I have found a solution but I would like to see (and learn) different ways of achieving the result.  
I also need some clarification on the solution I found.

```
             function odds(x,car)
                   split(x,car)[1:2:end]
             end

            function evens(x,car)
               split(x,car)[2:2:end]
           end

        transform!( df,:frqty=>(x->(zip(odds.(x,"\\"),evens.(x,"\\"))))=>[:fr,:qty])
       select!(df,Not(:frqty))
      ddff=vcat([DataFrame(:name=>repeat([r.name],length(r.fr)), :fr=>r.fr,:qty=>r.qty) for r in eachrow(df)]...)

```

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [December 15, 2020, 1:45pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/2 "2020-12-15T13:45:03Z")

</div>

Are you reading this data from some source file? If so, how are you handling that part? If it was me, I would prefer to deal with the `\\` delimiter when reading the file rather than storing it like this in a DataFrame and having to sort it out afterwards. Parsing the data appropriately at the point of ingestion should also eliminate the issue you now have with your fruit and quantity columns being `SubString`s.

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [December 15, 2020, 2:22pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/3 "2020-12-15T14:22:58Z")

</div>

How about this:

```julia
fruitinfo(s) = map(Iterators.partition(split(s, "\\"), 2)) do (f, n)
    (fruit = f, number = parse(Int, n))
end

combine(
    groupby(df, :name),
    :frqty => (fs -> reduce(vcat, fruitinfo.(fs))) => AsTable)

```

---

<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:** [December 15, 2020, 2:38pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/4 "2020-12-15T14:38:12Z")

</div>

I say this: 👏 👏

now I look at it well and try to understand how it works.

tanks!

---

<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:** [December 15, 2020, 2:43pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/5 "2020-12-15T14:43:53Z")

</div>

Hi.  
it’s not a real problem. I’m just trying to do exercises to get used to the Julian environment.

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [December 15, 2020, 3:07pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/6 "2020-12-15T15:07:52Z")

</div>

Ah actually the second part could just be

```julia
combine(
    groupby(df, :name),
    :frqty => fruitinfo ∘ only => AsTable)

```

The problem is that you need `combine` if you want to do a transformation that results in more output rows than input rows, while `transform` needs to return the same number.  
But then you want to run your function on every row, so you group by the variable `name`. Then you know you will receive a vector with only one element as the input in each row, so you extract that element with `only` (so it errors if your assumption is wrong) and then do `fruitinfo` on that one value. You get a list of named tuples back, and the sink argument `=> AsTable` directly converts that into two new columns.

---

<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:** [December 15, 2020, 4:52pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/7 "2020-12-15T16:52:34Z")

</div>

You want `flatten`, I think.

```julia
julia> df=DataFrame(name=["aaa","bbbb","ccc"],
                           frqty=["apple\\4\\melon\\5","plume\\12","graphes\\7\\mango\\9\\persic\\11"])
3×2 DataFrame
 Row │ name frqty                            
     │ String String                           
─────┼──────────────────────────────────────────
   1 │ aaa apple\\4\\melon\\5
   2 │ bbbb plume\\12
   3 │ ccc graphes\\7\\mango\\9\\persic\\11

julia> df.x = split.(df.frqty, "\\")
3-element Array{Array{SubString{String},1},1}:
 ["apple", "4", "melon", "5"]
 ["plume", "12"]
 ["graphes", "7", "mango", "9", "persic", "11"]

julia> flatten(df, :x)
12×3 DataFrame
 Row │ name frqty x         
     │ String String SubStrin… 
─────┼─────────────────────────────────────────────────────
   1 │ aaa apple\\4\\melon\\5 apple
   2 │ aaa apple\\4\\melon\\5 4
   3 │ aaa apple\\4\\melon\\5 melon
   4 │ aaa apple\\4\\melon\\5 5
   5 │ bbbb plume\\12 plume
   6 │ bbbb plume\\12 12
   7 │ ccc graphes\\7\\mango\\9\\persic\\11 graphes
   8 │ ccc graphes\\7\\mango\\9\\persic\\11 7
   9 │ ccc graphes\\7\\mango\\9\\persic\\11 mango
  10 │ ccc graphes\\7\\mango\\9\\persic\\11 9
  11 │ ccc graphes\\7\\mango\\9\\persic\\11 persic
  12 │ ccc graphes\\7\\mango\\9\\persic\\11 11

```

---

<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:** [December 15, 2020, 5:10pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/8 "2020-12-15T17:10:43Z")

</div>

Thank you very much for explaining how things work.

I hadn’t read the details of the combine function description yet. I think that in this example you have produced one will find a lot of the possibilities of this function and you have saved me a mountain of time studying it.

Things seem also work like this:

```
           combine(groupby(df, :name),:frqty => (fs -> fruitinfo(fs[1])) => AsTable)

```

or this:

```
           combine(groupby(df, :name), :frqty => fruitinfo ∘ first => AsTable)

```

One of the questions I wanted to have about my naive solution was how to convert the string format of the qty column to numeric.  
your solution solves this problem at the root.

A further lesson is that of the use of the do … end construct which I have to see because I did not know it.  
Wouldn’t a “simple” map (itr, func) suffice in this case?

---

<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:** [December 15, 2020, 5:22pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/9 "2020-12-15T17:22:23Z")

</div>

Flattening lists was an idea I had thought of, but I didn’t know how to put it into practice.  
This is Columbus’s egg!!!

```julia
julia> select!(df,Not(:frqty))
3×3 DataFrame
 Row │ name fr qty
     │ String Array… Array…
─────┼──────────────────────────────────────────────────────────────────────────────
   1 │ aaa SubString{String}["apple", "melo… SubString{String}["4", "5"]
   2 │ bbbb SubString{String}["plume"] SubString{String}["12"]
   3 │ ccc SubString{String}["graphes", "ma… SubString{String}["7", "9", "11"]

julia> flatten(df, [:fr,:qty])
6×3 DataFrame
 Row │ name fr qty
     │ String SubStrin… SubStrin…
─────┼──────────────────────────────
   1 │ aaa apple 4
   2 │ aaa melon 5
   3 │ bbbb plume 12
   4 │ ccc graphes 7
   5 │ ccc mango 9
   6 │ ccc persic 11

```

Now I can ask the two questions I wanted to ask.  
How do you convert the qty column to integers?  
And why does the transform function also put the type in front of the new column values?

```
       transform!( df,:frqty=>(x->(zip(odds.(x,"\\"),evens.(x,"\\"))))=>[:fr,:qty])

```

---

<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:** [December 15, 2020, 5:31pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/10 "2020-12-15T17:31:20Z")

</div>

1. You have to use `Parse`, or `tryparse` (be sure to convert to `missing` if `tryparse` falls back to `nothing`
2. Those are just for printing. So you can see the types of your columns, they aren’t the names of the columns.

---

<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:** [December 15, 2020, 5:48pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/11 "2020-12-15T17:48:26Z")

</div>

for 2. I am referring to the text circled in red

 ![immagine](https://global.discourse-cdn.com/julialang/original/3X/e/1/e14e15e53cb41e0e2edb4b3dd81b86c0e0e7118e.png)

for 1.

```julia

julia> ddff=vcat([DataFrame(:name=>repeat([r.name],length(r.fr)), :fr=>r.fr,:qty=>parse.(Int,r.qty)) for r in eachrow(df)]...)
6×3 DataFrame
 Row │ name fr qty   
     │ String SubStrin… Int64 
─────┼──────────────────────────
   1 │ aaa apple 4
   2 │ aaa melon 5
   3 │ bbbb plume 12
   4 │ ccc graphes 7
   5 │ ccc mango 9
   6 │ ccc persic 11

```

---

<div class="post-metadata">

**Author:** ![Henrique\_Becker](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/henrique_becker/32/15443_2.png) [@Henrique\_Becker](https://discourse.julialang.org/u/Henrique_Becker)\
**Post date:** [December 15, 2020, 5:50pm UTC](https://discourse.julialang.org/t/expand-row-to-multiple-rows/51872/12 "2020-12-15T17:50:44Z")

</div>

Yes, this is how `Vector`s of `SubString{String}` are printed (in certain conditions). In fact, `Vector`s in general are printed as `TypeValidForAllElements[element1, element2, ...]`.