# Reshaping dataframe

**URL:** <https://discourse.julialang.org/t/reshaping-dataframe/78020>\
**Category:** Data\
**Tags:** question, unstack, stack, insertcols\
**Created:** [March 17, 2022, 10:54am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020 "2022-03-17T10:54:17Z")\
**Posts on this page:** 14\
**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:** [March 17, 2022, 10:54am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/1 "2022-03-17T10:54:17Z")

</div>

I show a way to transform the starting table to have only one “X” per row.  
I ask for comments on how to do the various steps differently and / or better.  
For example how to insert an “index” column or how to filter the rows that contain “X”, or other like the general setting of the solution.

```julia
df
7×7 DataFrame
 Row │ Name Date Hours Criteria 1 Criteria 2 Criteria 3 Criteria 4    
     │ String String Int64 String? String? String? String?       
─────┼────────────────────────────────────────────────────────────────────────────   
   1 │ Sander 1/1/2022 8 X missing missing missing       
   2 │ Sander 1/2/2022 5 missing X X missing       
   3 │ Sander 1/3/2022 7 missing missing X missing       
   4 │ Sander 1/4/2022 8 missing missing missing X
   5 │ Somebody 2/3/2022 4 X X missing X
   6 │ Somebody 4/3/2022 9 X missing X X
   7 │ Somebody 5/14/2022 2 missing missing X missing   

```

```julia
sdf=stack(df, r"Criteria")
insertcols!(sdf, 1, :Row => 1:nrow(sdf))
udf=unstack(sdf)
filter(r-> any("X" .=== values(r)) , udf)

```

PS  
Is it possible, in some way, to upload csv files?

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 10:55am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/2 "2022-03-17T10:55:27Z")

</div>

Instead of uploading a csv you can just copy/paste a string representation like here:

[https://stackoverflow.com/questions/61723347/how-to-provide-reproducible-sample-data-in-julia/61724167](https://stackoverflow.com/questions/61723347/how-to-provide-reproducible-sample-data-in-julia/61724167)

---

<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:** [March 17, 2022, 10:58am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/3 "2022-03-17T10:58:04Z")

</div>

tanks!  
this way?

```julia
string_representation = String(take!(CSV.write(IOBuffer(), df)))
"Name,Date,Hours,Criteria 1,Criteria 2,Criteria 3,Criteria 4\nSander,1/1/2022,8,X,,,\nSander,1/2/2022,5,,X,X,\nSander,1/3/2022,7,,,X,\nSander,1/4/2022,8,,,,X\nSomebody,2/3/2022,4,X,X,,X\nSomebody,4/3/2022,9,X,,X,X\nSomebody,5/14/2022,2,,,X,\n"

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 10:59am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/4 "2022-03-17T10:59:59Z")

</div>

Yep:

```julia
julia> CSV.read(IOBuffer(string_representation), DataFrame)
7×7 DataFrame
 Row │ Name Date Hours Criteria 1 Criteria 2 Criteria 3 Criteria 4
     │ String String Int64 String? String? String? String?
─────┼────────────────────────────────────────────────────────────────────────────
   1 │ Sander 1/1/2022 8 X missing missing missing
   2 │ Sander 1/2/2022 5 missing X X missing
   3 │ Sander 1/3/2022 7 missing missing X missing
   4 │ Sander 1/4/2022 8 missing missing missing X
   5 │ Somebody 2/3/2022 4 X X missing X
   6 │ Somebody 4/3/2022 9 X missing X X
   7 │ Somebody 5/14/2022 2 missing missing X missing

```

---

<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:** [March 17, 2022, 11:02am UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/5 "2022-03-17T11:02:20Z")

</div>

Tanks again.  
I add one more question.  
How can you do to correctly interpret the strings of the “Date” column?

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 12:13pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/6 "2022-03-17T12:13:11Z")

</div>

Same as with any other csv:

```julia
julia> CSV.read(IOBuffer(string_representation), DataFrame; dateformat = "m/d/y")

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 12:17pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/7 "2022-03-17T12:17:49Z")

</div>

Also sorry this was a complete distraction from your real question, which I admit I don’t understand.

Could you show the expected result? When I run your code I end up with:

```julia
julia> filter(r-> any("X" .=== values(r)) , udf)
0×8 DataFrame

```

which I assume isn’t what you’re after?

---

<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:** [March 17, 2022, 2:04pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/8 "2022-03-17T14:04:43Z")

</div>

the result should be, and to me it actually is, this:

```julia
julia> filter(r-> any("X" .=== values(r)) , udf)
12×8 DataFrame
 Row │ Row Name Date Hours Criteria 1 Criteria 2 Criteria 3 Crite ⋯
     │ Int64 String String Int64 String? String? String? Strin ⋯
─────┼───────────────────────────────────────────────────────────────────────────────
   1 │ 1 Sander 1/1/2022 8 X missing missing missi ⋯
   2 │ 5 Somebody 2/3/2022 4 X missing missing missi  
   3 │ 6 Somebody 4/3/2022 9 X missing missing missi  
   4 │ 9 Sander 1/2/2022 5 missing X missing missi  
   5 │ 12 Somebody 2/3/2022 4 missing X missing missi ⋯
   6 │ 16 Sander 1/2/2022 5 missing missing X missi  
   7 │ 17 Sander 1/3/2022 7 missing missing X missi  
   8 │ 20 Somebody 4/3/2022 9 missing missing X missi  
   9 │ 21 Somebody 5/14/2022 2 missing missing X missi ⋯
  10 │ 25 Sander 1/4/2022 8 missing missing missing X      
  11 │ 26 Somebody 2/3/2022 4 missing missing missing X      
  12 │ 27 Somebody 4/3/2022 9 missing missing missing X      
                   

```

julia\> versioninfo()  
Julia Version 1.7.0-beta3.0  
DataFrames v1.4.0 `[https://github.com/JuliaData/DataFrames.jl.git#main](https://github.com/JuliaData/DataFrames.jl.git#main)

---

<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:** [March 17, 2022, 2:07pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/9 "2022-03-17T14:07:53Z")

</div>

I tried to use the aprameter dateformat = “d / m / yyyy” but it didn’t give me the expected result.  
but even so it is no different

```julia
julia> df=CSV.read("unpivot pivot.CSV", DataFrame, dateformat="d/m/y")
7×7 DataFrame
 Row │ Name Date Hours Criteria 1 Criteria 2 Criteria 3 Criteria 4    
     │ String String Int64 String? String? String? String?       
─────┼────────────────────────────────────────────────────────────────────────────   
   1 │ Sander 1/1/2022 8 X missing missing missing       
   2 │ Sander 1/2/2022 5 missing X X missing       
   3 │ Sander 1/3/2022 7 missing missing X missing       
   4 │ Sander 1/4/2022 8 missing missing missing X
   5 │ Somebody 2/3/2022 4 X X missing X
   6 │ Somebody 4/3/2022 9 X missing X X
   7 │ Somebody 5/14/2022 2 missing missing X missing       

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 2:09pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/10 "2022-03-17T14:09:31Z")

</div>

That’s because the dateformat is `m/d/y`, no? One of the date strings is `5/14/2022`, so when you pass `d/m/y` as dateformat, it will try to parse this string and conclude that `14` is not a valid month so this isn’t a date column.

---

<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:** [March 17, 2022, 2:19pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/11 "2022-03-17T14:19:14Z")

</div>

> [@nilshg](#):
>
> That’s because the dateformat is `m/d/y` , no? One of the date strings is `5/14/2022` , so when you pass `d/m/y` as dateformat, it will try to parse this string and conclude that `14` is not a valid month so this isn’t a date column.

Thank you.  
Also for the patience you put into the explanations.  
I completely missed the reversal of the month with the day in the input format …

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 17, 2022, 2:33pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/12 "2022-03-17T14:33:53Z")

</div>

No worries - as for your actual question I now understand a bit better, but don’t think I’ve got a better solution.

Your final filter call doesn’t work for me on DataFrames 1.3.2, Julia 1.7.2, but I can replicate your result using the (imo slightly clearer)

```julia
filter(r -> any(isequal("X"), r), udf)

```

---

<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:** [March 17, 2022, 2:44pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/13 "2022-03-17T14:44:52Z")

</div>

Could you find an “easy” way to use the `subset ()` function in this case?  
Although, it comes to mind now, it is preferable to filter the datafreme sdf (stacked df) on the single column `:value`, rather than filtering at the end `udf`.

---

<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:** [March 17, 2022, 2:52pm UTC](https://discourse.julialang.org/t/reshaping-dataframe/78020/14 "2022-03-17T14:52:34Z")

</div>

@rocco_sprmnt21 Regarding your last question do you want a way to rewrite:

```julia
filter(r -> any(isequal("X"), r), udf)

```

using `subset`?

If this is what you are after then you can write:

```julia
subset(udf, AsTable(:) => ByRow(r -> any(isequal("X"), r))

```