# Create DataFrame from data in DataFrameRow

**URL:** <https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431>\
**Category:** Data\
**Tags:** dataframesmeta\
**Created:** [November 8, 2024, 9:38pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431 "2024-11-08T21:38:02Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [November 8, 2024, 9:38pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/1 "2024-11-08T21:38:02Z")

</div>

I’m stuck on this data transformation.

I have a DataFrameRow that I looked up from another table.

```julia-repl
julia> using DataFramesMeta
julia> dfrow = DataFrame(
           "Carbon Min. (%)" => 0.1,
           "Carbon Max. (%)" => 0.4,
           "Phosphorus Min. (%)" => 0,
           "Phosphorus Max. (%)" => 0.015,
       )[1,:]
DataFrameRow
 Row │ Carbon Min. (%) Carbon Max. (%) Phosphorus Min. (%) Phosphorus Max. (%)
     │ Float64 Float64 Int64 Float64
─────┼────────────────────────────────────────────────────────────────────────────
   1 │ 0.1 0.4 0 0.015

```

I want to create a new DataFrame with the data in the form below.

```julia-repl
julia> df = DataFrame(
           "Element" => ["Carbon", "Phosphorus"],
           "Min. (%)" => [0.1, 0.0],
           "Max. (%)" => [0.4, 0.015],
       )
2×3 DataFrame
 Row │ Element Min. (%) Max. (%)
     │ String Float64 Float64
─────┼────────────────────────────────
   1 │ Carbon 0.1 0.4
   2 │ Phosphorus 0.0 0.015

```

I extracted the list of elements for the first DataFrame column, but I am struggling with the syntax to move the relevant values from `dfrow` into `df`.

```julia-repl
julia> elements = unique(first.(split.(names(dfrow)))
2-element Vector{SubString{String}}:
 "Carbon"
 "Phosphorus"

```

I should also mention that there are `missing` % values in the data.

---

<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:** [November 8, 2024, 10:13pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/2 "2024-11-08T22:13:40Z")

</div>

[Two](https://github.com/JuliaData/DataFrames.jl/issues/3237) [relevant](https://github.com/JuliaData/DataFrames.jl/issues/3237) issues in DataFrames about this.

I don’t know how I would do this without some sort of `loop` and `vcat`

---

<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:** [November 8, 2024, 10:59pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/3 "2024-11-08T22:59:42Z")

</div>

a bit of a tortuous path, but it seems to get the desired shape while remaining within the context of the DataFrames package

```julia
sdf=stack(DataFrame(dfrow),names(dfrow))

sdf1=select(sdf,:value, :variable => ByRow(r->split(r,limit=2))=>[:Element,:mM,])

unstack(sdf1,:mM,:value)

```

---

<div class="post-metadata">

**Author:** ![TZ.Neumann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tz.neumann/32/34438_2.png) [@TZ.Neumann](https://discourse.julialang.org/u/TZ.Neumann)\
**Post date:** [November 8, 2024, 11:51pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/4 "2024-11-08T23:51:31Z")

</div>

With @pdeffebach inputs, I have got this working.

```julia
elements = unique(first.(split.(names(row), ' '))) 
empty_vector = []

for elem in elements
    df = DataFrame(
        "Element" => String[],
        "Min. (%)" => Union{Float64,Missing}[],
        "Max. (%)" => Union{Float64,Missing}[]
    )
    min_key, max_key = "$elem Min. (%)", "$elem Max. (%)"
    push!(df, (elem, get(dfrow, min_key, missing), get(dfrow, max_key, missing)))
    push!(empty_vector, df)
end
df_new = vcat(empty_vector...)
```

---

<div class="post-metadata">

**Author:** ![alfaromartino](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alfaromartino/32/52986_2.png) [@alfaromartino](https://discourse.julialang.org/u/alfaromartino)\
**Post date:** [November 9, 2024, 3:57am UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/5 "2024-11-09T03:57:34Z")

</div>

I think the easiest solution is using `stack` and `unstack`.

```julia
using DataFrames
dfrow = DataFrame(
           "Carbon Min. (%)" => 0.1,
           "Carbon Max. (%)" => 0.4,
           "Phosphorus Min. (%)" => 0,
           "Phosphorus Max. (%)" => 0.015,
       )

# permute dimensions
dff = stack(dfrow, :)

# extract element and measures from columns
dff.element = [split(x, " ")[1] for x in dff.variable]
dff.measure = [split(x, " ")[2] for x in dff.variable]

# reshape
df = unstack(dff, :element, :measure, :value)

```

with output

```julia
2×3 DataFrame
 Row │ element Min. Max.     
     │ SubStrin… Float64? Float64? 
─────┼────────────────────────────────
   1 │ Carbon 0.1 0.4
   2 │ Phosphorus 0.0 0.015

```

EDIT: same as @rocco_sprmnt21 's solution

---

<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:** [November 9, 2024, 6:24pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/6 "2024-11-09T18:24:40Z")

</div>

> [@Nathan\_Boyer](#):
>
> I should also mention that there are `missing` % values in the data.

unstack also takes care of handling missed values

```julia
julia> dfrow = DataFrame(
                   "Carbon Min. (%)" => 0.1,
                   "Carbon Max. (%)" => 0.4,
                   #="Phosphorus Min. (%)" => 0,=#
                   "Phosphorus Max. (%)" => 0.015,
               )[1,:]
DataFrameRow
 Row │ Carbon Min. (%) Carbon Max. (%) Phosphorus Max. (%) 
     │ Float64 Float64 Float64
─────┼───────────────────────────────────────────────────────
   1 │ 0.1 0.4 0.015

julia> sdf=stack(DataFrame(dfrow))
3×2 DataFrame
 Row │ variable value   
     │ String Float64
─────┼──────────────────────────────
   1 │ Carbon Min. (%) 0.1
   2 │ Carbon Max. (%) 0.4
   3 │ Phosphorus Max. (%) 0.015

julia> sdf1=select(sdf,:value, :variable => ByRow(r->split(r,limit=2))=>[:Element,:mM,])
3×3 DataFrame
 Row │ value Element mM        
     │ Float64 SubStrin… SubStrin…
─────┼────────────────────────────────
   1 │ 0.1 Carbon Min. (%)
   2 │ 0.4 Carbon Max. (%)
   3 │ 0.015 Phosphorus Max. (%)

julia> unstack(sdf1,:mM,:value)
2×3 DataFrame
 Row │ Element Min. (%) Max. (%) 
     │ SubStrin… Float64? Float64?
─────┼─────────────────────────────────
   1 │ Carbon 0.1 0.4
   2 │ Phosphorus missing 0.015

julia> unstack(sdf1,:mM,:value,fill="unknown")
2×3 DataFrame
 Row │ Element Min. (%) Max. (%) 
     │ SubStrin… Any Any
─────┼────────────────────────────────
   1 │ Carbon 0.1 0.4
   2 │ Phosphorus unknown 0.015

```

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [November 10, 2024, 1:55am UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/7 "2024-11-10T01:55:36Z")

</div>

Nice solution. When trying it myself, also used `stack` like you did in:

> [@rocco\_sprmnt21](#):
>
> `julia> sdf=stack(DataFrame(dfrow))`

There might be a possible feature addition to `stack` which could be useful in this case. The idea is to use:

```julia
julia> stack(DataFrame(dfrow),r"([a-zA-Z]+) (.+)")

```

which selects columns using a RegEx. But there is additional information in this RegEx which are capture groups. We can make `stack` use extracted capture groups and make them into columns in the output, essentially getting with the above RegEx the extra processing done with the `select`. Note that:

```julia
julia> match.(r"([a-zA-Z]+) (.+)", names(dfrow))
4-element Vector{RegexMatch{String}}:
 RegexMatch("Carbon Min. (%)", 1="Carbon", 2="Min. (%)")
 RegexMatch("Carbon Max. (%)", 1="Carbon", 2="Max. (%)")
 RegexMatch("Phosphorus Min. (%)", 1="Phosphorus", 2="Min. (%)")
 RegexMatch("Phosphorus Max. (%)", 1="Phosphorus", 2="Max. (%)")

```

This can often be useful when parsing numbered columns, which come as `year1984` or some other weird format.

Is this a useful feature?

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [November 11, 2024, 4:44pm UTC](https://discourse.julialang.org/t/create-dataframe-from-data-in-dataframerow/122431/8 "2024-11-11T16:44:18Z")

</div>

Thanks all! I was having trouble figuring out the `stack` functions from the documentation, but these examples helped.

@pdeffebach you seem to have linked the same issue twice.