# How to explode a DataFrame cell containing multiple values into separate rows?

**URL:** <https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677>\
**Category:** General Usage\
**Created:** [July 7, 2020, 1:26pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677 "2020-07-07T13:26:27Z")\
**Posts on this page:** 8\
**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 7, 2020, 1:26pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/1 "2020-07-07T13:26:27Z")

</div>

Hi, I’m working on a project where sometimes I get data coming in with multiple values in one cell. Part of my feature engineering is to calculate the counts per user, and I’m looking for an effective way to explode out cells with multiple values into rows of their own, without double counting on the other rows.

An example will make things much clearer:

```julia
initial = DataFrame(:day=>["monday","monday","monday"],:name=>["bob","mike","phillis"],:temp=>["medium","high","low, medium"],:gender=>["male","male","female"])

```

![Screenshot from 2020-07-07 09-23-57](https://global.discourse-cdn.com/julialang/original/3X/4/a/4a0cf12d8e832fe60490f8551dfd62f8e0ff8739.png)

```julia
transformed = DataFrame(:day=>["monday","monday","monday","duplicate"],:name=>["bob","mike","phillis","phillis"],:temp=>["medium","high","low","medium"],:gender=>["male","male","female","duplicate"])

```

![Screenshot from 2020-07-07 09-24-55](https://global.discourse-cdn.com/julialang/original/3X/1/c/1c9ab53dd97a855980b672690ed4fbfc978925a2.png)

Notice how the OTHER row values, the ones without multiple entries, are replaced with "“duplicate” in the newly exploded row. The reason I need this is because I’m calculating counts per user (pivot tables), and don’t want to double count for everything else.

Thanks!

---

<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 7, 2020, 1:35pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/2 "2020-07-07T13:35:14Z")

</div>

You want to use `flatten`

```julia
julia> function make_longer(initial)
       initial[!, "temp2"] = split.(initial.temp)
       flatten(initial, "temp2")
       end

```

---

<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 7, 2020, 1:53pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/3 "2020-07-07T13:53:41Z")

</div>

Thank you, what would be the quickest way to scan the dataframe and check if any columns indeed have a cell with more than one value? I need to generalize this so that I can create a preprocessing function that scans all columns of the dataframe and splits/flattens appropriately if needed, otherwise leaves it alone.

---

<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 7, 2020, 2:00pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/4 "2020-07-07T14:00:28Z")

</div>

Probably checking for commas or some other delimiter. That might be hard to generalize, of course.

If you want more performance you can also do a filter for rows where the value can be split, then do `flatten` on that subset and `append!` the new data frame onto the old one.

---

<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 7, 2020, 2:48pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/5 "2020-07-07T14:48:35Z")

</div>

Ok thank you. And then the last part is, the way the implementation is now, I’ll inevitably double count every other column that didn’t need to be exploded out. Is there anyway that when calling the flatten function, I can replace all the values of the newly created row that AREN’T the exploded column with a string value of “duplicate”. Like this:

![Screenshot from 2020-07-07 09-24-55](https://global.discourse-cdn.com/julialang/original/3X/1/c/1c9ab53dd97a855980b672690ed4fbfc978925a2.png)

---

<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 7, 2020, 3:16pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/6 "2020-07-07T15:16:03Z")

</div>

I don’t think so. You would have to do that replace manually.

If the goal is really just to have counts per user and _not_ get the data frame in the shape you requested, you can do

```julia
julia> initial.temp2 = split.(initial.temp)
3-element Array{Array{SubString{String},1},1}:
 ["medium"]
 ["high"]
 ["low,", "medium"]

julia> initial
3×5 DataFrame
│ Row │ day │ name │ temp │ gender │ temp2 │
│ │ String │ String │ String │ String │ Array… │
├─────┼────────┼─────────┼─────────────┼────────┼────────────────────┤
│ 1 │ monday │ bob │ medium │ male │ ["medium"] │
│ 2 │ monday │ mike │ high │ male │ ["high"] │
│ 3 │ monday │ phillis │ low, medium │ female │ ["low,", "medium"] │

julia> initial.count = length.(initial.temp2)
3-element Array{Int64,1}:
 1
 1
 2

julia> count = sum(initial.count)
4

```

---

<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 7, 2020, 4:23pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/7 "2020-07-07T16:23:59Z")

</div>

ok thank you.

whats the quickest way to strip the commas off of the split method applied on the dataframe?

![Screenshot from 2020-07-07 12-22-37](https://global.discourse-cdn.com/julialang/original/3X/c/c/cc14c22bb9bdf45413b2a6be74cf9f156b4bc6a9.png)

I dont want male and male, lol

---

<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 7, 2020, 4:25pm UTC](https://discourse.julialang.org/t/how-to-explode-a-dataframe-cell-containing-multiple-values-into-separate-rows/42677/8 "2020-07-07T16:25:34Z")

</div>

`strip`, `replace` using regular expressions`. There are many options, see the manual [here](https://docs.julialang.org/en/v1/manual/strings/).