# Frustrated using DataFrames

**URL:** <https://discourse.julialang.org/t/frustrated-using-dataframes/67833>\
**Category:** New to Julia\
**Tags:** dataframes, data\_structures\
**Created:** [September 7, 2021, 9:38pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833 "2021-09-07T21:38:10Z")\
**Posts on this page:** 20\
**Page:** 3

<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:** [September 8, 2021, 7:01pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/41 "2021-09-08T19:01:45Z")

</div>

That is pretty clean to read and reason through. I will have to get used to `df` just floating there in the middle and the interpolated variables, but that’s not unique to `DataFramesMeta.jl`. I’m still afraid of [BenchmarkTools.jl](https://github.com/JuliaCI/BenchmarkTools.jl) too.

---

<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:** [September 8, 2021, 7:20pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/42 "2021-09-08T19:20:45Z")

</div>

Does `@subset!` from `DataFramesMeta` essentially replace `filter!` from `DataFrames`? Why do the names differ?

---

<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:** [September 8, 2021, 7:25pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/43 "2021-09-08T19:25:33Z")

</div>

The names differ for a few reasons

1. `subset` acts on full columns, while the operations on `filter` act on rows. We can’t make `filter` act on columns because a DataFrame is conceptualized as a collection of rows, where possible. The contract of `Base.filter` is that the operation `f` in `filter(f, x)` will act on elements of `x`.
2. Similarly, we want operations on a `GroupedDataFrame` to be split-apply-combine style transformations. Since a `GroupedDataFrame` iterates through sub-dataframes, to keep a contract with `Base.filter`, we would need `filter` to return only some groups.

Therefore we need a different function with it’s own name. This gives us the flexibility to have consistent sub-setting behavior with `transform` and `select`.

---

<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:** [September 8, 2021, 7:44pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/44 "2021-09-08T19:44:29Z")

</div>

@Nathan_Boyer: note that in DataFrames.jl we have both `filter!` and `subset!`. In general it would be great if you put your concerns in an issue in DataFrames.jl and I will think how to handle them (probably via docs updates).

I already have opened [https://github.com/JuliaData/DataFrames.jl/issues/2866](https://github.com/JuliaData/DataFrames.jl/issues/2866).

---

<div class="post-metadata">

**Author:** ![viraltux](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/viraltux/32/15236_2.png) [@viraltux](https://discourse.julialang.org/u/viraltux)\
**Post date:** [September 8, 2021, 8:05pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/45 "2021-09-08T20:05:12Z")

</div>

> [@Nathan\_Boyer](#):
>
> Yes, it seems like all the suggestions so far would still require a `for` loop.

Not really,

```julia
df = DataFrame(Time = [3., 4., 5.], TopTemp = [70., 73., 100.], BottomTemp = [50., 55., 80.])
df[!, :id] = 1:nrow(df)
sdf = stack(df)

usdf = @sqldf """
           select variable,
           (case when variable like '%Temp' then (value-32)/1.8 else value end) as value,
           id
           from sdf
       """

unstack(usdf)

3×4 DataFrame
 Row │ id Time TopTemp BottomTemp 
     │ Int64 Float64? Float64? Float64?   
─────┼───────────────────────────────────────
   1 │ 1 3.0 21.1111 10.0
   2 │ 2 4.0 22.7778 12.7778
   3 │ 3 5.0 37.7778 26.6667

```

However, in general I would say we should not expect DataFrames.jl or any equivalent package to offer direct solutions to all possible transformations we need.

In my case I follow these guidelines when it comes to transformations:

1. Simple → DataFrames.jl
2. Complex but SQL simple → DataFrames.jl with SQLdf.jl
3. Complex and Beyond SQL simple → Julia Code

---

<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:** [September 8, 2021, 8:15pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/46 "2021-09-08T20:15:22Z")

</div>

I want to push back on this.

They key feature of DataFrames is that all of it’s columns are just plain old normal Julia vectors. Everything you can do with Julia vectors you can do in DataFrames. The `source => fun => dest` syntax (or alternatively, DataFramesMeta.jl) provides type-stable, performant ways to work with these columns.

But there is _no_ different in performance between working with DataFrames and plain Julia vectors. Additionally, as long as transformations go in a function, you can define as complicated a function you want `fun` without ever thinking about DataFrames.jl, and then use it with `source => fun => dest` syntax.

This is not necessarily true when working with `dplyr` or Pandas.

Additionally, the macro `@with` in DataFrames.jl creates an anonymous function which acts on the DataFrames columns and executes it.

Consider the following

```julia
@with df begin 
    z = :x + :y 
    t = mean(z) * 100
end

```

Though `@with` acts on a `DataFrame`, the actual code it produces and executes has nothing to do with any implementation of DataFrames.jl.

---

<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:** [September 8, 2021, 8:21pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/47 "2021-09-08T20:21:05Z")

</div>

> [@bkamins](#):
>
> In general it would be great if you put your concerns in an issue in DataFrames.jl and I will think how to handle them (probably via docs updates).

I will work on that.

In general:

1. Docs are hard to read for those whose data does not benefit from split-apply-combine. (My elements are all unique, so there is nothing to split or groupby.)
2. I have trouble figuring out what is and isn’t needed/allowed inside the function syntax: `ByRow()` , `eachcol()` , `names()` , `Cols()` , `:` , `.=>` , `.()` , `...` etc.
3. General confusion about whether a function operates on a row, column, elements of a row, or elements of a column.
4. There is no way to test if an argument is valid or test individual pieces of an argument. I would like to be able to print, check type, save to variable, etc. the input and output from each of x =\> y =\> z; maybe even deeper if y becomes complicated with nesting.
5. Multi-column broadcasting is limited.

```julia
julia> filter!(names(df, Not(:node)) .=> ByRow(row -> any(x -> x>0, row)), df)
ERROR: MethodError: no method matching !(::Vector{Pair{String, ByRow{var"#204#206"}}})

julia> transform!(df, r"Temp" .=> ByRow.(fahrenheit_to_celsius), renamecols = false)
ERROR: LoadError: MethodError: no method matching fahrenheit_to_celsius(::Int64, ::Int64)

```

1. Function broadcasting is limited.

```julia
julia> filter(x -> ismissing(eachcol(x)), df)
ERROR: MethodError: no method matching eachcol(::DataFrameRow{DataFrame,DataFrames.Index})

julia> filter(x -> ismissing.(x), df)
ERROR: ArgumentError: broadcasting over `DataFrameRow`s is reserved

```

**Note:**  
I would like to emphasize again that my initial assumption was either:

- I am reasoning about this whole thing wrong.
- DataFrames is built for a different type of data processing and I should be using something else.

**Not** DataFrames is broken and I’m mad about it.

---

<div class="post-metadata">

**Author:** ![viraltux](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/viraltux/32/15236_2.png) [@viraltux](https://discourse.julialang.org/u/viraltux)\
**Post date:** [September 8, 2021, 8:28pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/48 "2021-09-08T20:28:04Z")

</div>

Oh, I myself I think I would use Julia code in this case, I was just showing an example with no loops. However, when it comes to transformations performance is not always relevant but rather accuracy and simplicity.

---

<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:** [September 8, 2021, 8:54pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/49 "2021-09-08T20:54:00Z")

</div>

> [@Nathan\_Boyer](#):
>
> - I am reasoning about this whole thing wrong.

I do think a lot of the problems could be solved by changing the way you reason about these calls. More emphasis should be placed on creating a valid `source => fun => dest` object.

`source` is a collection of names, i.e. `String`s or `Symbol`s

`fun`, is just a function. `ByRow(fun)` is a wrapper function, i.e. `f` is a function _and_ `ByRow(f)` is a function. Since a function is not iterable, `ByRow.(f)` makes no sense.

`dest` is most commonly a single column name, like a `String` or `Symbol`. But can also be a collection of `String`s or `Symbol`s.

### `.=>`:

Similarly, `r"Temp"` is a regular expression. Because regular expressions are not iterable, `r"Temp" .=>` doesn’t make sense.

Since functions are not iterable, we know we have the following behavior

```julia-auto
julia> foo(x) = x;

julia> [:a, :b] .=> foo
2-element Vector{Pair{Symbol, typeof(foo)}}:
 :a => foo
 :b => foo

```

the `fun` will get repeated.

This is all super complicated and requires a lot of pretty detailed knowledge of Julia’s broadcasting rules. In no way is this meant to answer your specific questions or invalidate your frustrations, which definitely make sense.

I mean only to show that a validation function (which Bogumil has already filed an issue for) might go a long way. If I were to give advice about this, I would say you should put more effort into understanding the way common Julia idioms, like broadcasting and regular expressions, are used in the construction of the `source => fun => dest` pairs.

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [September 8, 2021, 9:00pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/50 "2021-09-08T21:00:34Z")

</div>

> [@pdeffebach](#):
>
> One thing to note in all of these replies is OP specifically asked for working with many columns _programatically_ , i.e. `transform(df, names(df, r"Temp") .=> ...)` .
> 
> As far as I know, none of the more specialized packages listed head make that easy, including DataFramesMeta.jl. DataFrames’s mini-language is the best at making many modifications in one call.

This is a good point. DataFrames.jl could benefit from looking at dplyr’s functions for this: [Apply a function (or functions) across multiple columns — across • dplyr](https://dplyr.tidyverse.org/reference/across.html)

---

<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:** [September 8, 2021, 9:06pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/51 "2021-09-08T21:06:54Z")

</div>

> [@pdeffebach](#):
>
> Similarly, `r"Temp"` is a regular expression. Because regular expressions are not iterable, `r"Temp" .=>` doesn’t make sense.

Does `Not(:node)` create an iterable vector? That is another element I wish I could have inspected for type, but it only makes sense in the context of the DataFrame call.

---

<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:** [September 8, 2021, 9:28pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/52 "2021-09-08T21:28:25Z")

</div>

No, `Not(:node)` does not create an iterable vector. And neither do `Between` or `Cols` at the moment. Or, it was just fixed on `master` and I don’t know when the next release will be. For now use `names(df, Not(:node))`.

This is definitely frustrating. I’m glad the JuliaData maintainers have fixed it though. It will make it so people don’t experience that frustration in the future.

In general, I would say, make the pair first, especially if it’s complicated, and then inspect the result with `print`. DataFrames is not doing any special evaluation, so if the pair you constructed looks bad, it probably won’t work.

The following is a complicated pair, but you can see pretty clearly that it’s doing what you want.

```julia
julia> p1 = [:a, :b] .=> f .=> [:z1, :z2]
2-element Vector{Pair{Symbol, Pair{typeof(f), Symbol}}}:
 :a => (f => :z1)
 :b => (f => :z2)

```

---

<div class="post-metadata">

**Author:** ![dlakelan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dlakelan/32/8491_2.png) [@dlakelan](https://discourse.julialang.org/u/dlakelan)\
**Post date:** [September 8, 2021, 9:36pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/53 "2021-09-08T21:36:57Z")

</div>

> [@Nathan\_Boyer](#):
>
> # Examples
> 
> 1. [What rows are missing data?](https://stackoverflow.com/questions/64823551/return-copy-of-a-dataframe-that-contains-only-rows-with-missing-data-in-julia)
> 2. [Convert temperature data to Celsius.](https://stackoverflow.com/questions/65241599/what-is-the-right-way-to-transform-multiple-columns-of-a-dataframe-in-place-in-j)
> 3. Filter out rows which are all zeros.

Although there are lots of great macros and other tricks, sometimes it’s easy to just handle stuff using regular iteration and higher order functions like map, filter. Also remember you can create a column by assigning to it

```julia
df.hasmissing = map(x->any(ismissing,x),eachrow(df))

```

Convert temperatures:

```julia
df.tempC = map(ftoc, df.tempF)

```

Filter out rows that are all zero:

```julia
filter(x -> all(==(0),x),foo)

```

---

<div class="post-metadata">

**Author:** ![RobertGregg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robertgregg/32/22105_2.png) [@RobertGregg](https://discourse.julialang.org/u/RobertGregg)\
**Post date:** [September 9, 2021, 5:40am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/54 "2021-09-09T05:40:02Z")

</div>

I equally find DataFrames frustrating at times, especially coming from R / tidyverse. Take the second example you gave. In R this would be one very readable line of code:

```julia
mutate(df, across(contains("Temp"), ~ (.x - 32)*(5/9)))

```

No need to worry about calling the `names` function, regular expressions, creating a separate `fahrenheit_to_celsius` function, or giving a rename option.

---

<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:** [September 9, 2021, 5:46am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/55 "2021-09-09T05:46:23Z")

</div>

Yes it’s readable because it’s not a lot of code, but my questions would be what does `contains("Temp")` return, is that composable or is it a nonstandard evaluation kind of thing, also what does `~` do in this context and what’s `.x`. Just pointing out that this is a DSL that’s also not understandable with just R knowledge.

---

<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:** [September 9, 2021, 6:35am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/56 "2021-09-09T06:35:50Z")

</div>

What @jules says above together with

> [@pdeffebach](#):
>
> DataFrames is not doing any special evaluation

are key to me: DataFrames has built an infinitely composable mini-language based on standard Julia syntax. Admittedly, because it is so powerful and composable, it requires good knowledge of how Julia works and a good mental model of what is happening in the mini-language, but it does **not** require learning a new language/DSL.

---

<div class="post-metadata">

**Author:** ![RobertGregg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robertgregg/32/22105_2.png) [@RobertGregg](https://discourse.julialang.org/u/RobertGregg)\
**Post date:** [September 9, 2021, 7:26am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/57 "2021-09-09T07:26:12Z")

</div>

I agree R syntax is usually problematic, but in this case I think it wins. What does it matter what the `contains` function returns? All I need to know is that it selects columns that contain a string I provide (you could also swap in the `endswith` function in this case). As for the ~ and .x they are useful but kind of ugly shorthand for an anonymous function. I prefer Julia’s `x -> f(x)` syntax but you could just as easily provide a named function instead. 🤷🏻‍♂️

I would argue that this DSL is more intuitive as I read the code: “mutate this data frame across columns that contain “Temp” with this provided function”.

---

<div class="post-metadata">

**Author:** ![KajWiik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kajwiik/32/199_2.png) [@KajWiik](https://discourse.julialang.org/u/KajWiik)\
**Post date:** [September 9, 2021, 8:03am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/58 "2021-09-09T08:03:11Z")

</div>

I made some more benchmarks with larger vectors and added StructArray:

```julia
julia> node = rand(Int8, 10000); x = rand(Int8, 10000); y = rand(Int8,10000);
julia> sa = StructArray(node = node, x = x, y = y)
julia> df = DataFrame(node = node, x = x, y = y)

julia> @benchmark $sa[($sa.x .== 0) .| ($sa.y .== 0),:]
BenchmarkTools.Trial: 10000 samples with 8 evaluations.
 Range (min … max): 3.542 μs … 494.832 μs ┊ GC (min … max): 0.00% … 94.94%
 Time (median): 3.756 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 4.266 μs ± 10.912 μs ┊ GC (mean ± σ): 7.69% ± 3.04%

   ▄▇██▇▇▆▄▃▃▄▄▄▃▂▂▂▁▂▂▂▁▁▂▂▂▂▂▁▁▁▁▁ ▂
  ▇██████████████████████████████████▇███▇▇▆▇▇▅▆▅▅▅▆▆▆▄▅▅▅▅▅▆ █
  3.54 μs Histogram: log(frequency) by time 5.82 μs <

 Memory estimate: 6.02 KiB, allocs estimate: 6.

julia> @benchmark $df[($df.x .== 0) .| ($df.y .== 0),:]
BenchmarkTools.Trial: 10000 samples with 1 evaluation.
 Range (min … max): 11.897 μs … 3.326 ms ┊ GC (min … max): 0.00% … 99.04%
 Time (median): 12.627 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 13.714 μs ± 33.332 μs ┊ GC (mean ± σ): 2.40% ± 0.99%

   ▄▇██▇▆▅▃▂▂▂▁ ▁▃▅▅▅▄▄▃▂▁ ▁▂▂▂▂▂▁▁ ▃
  ███████████████████████████████████▇███▇▇▇▆▇▆▆▄▁▄▅▅▆▆▅▄▄▃▃▅ █
  11.9 μs Histogram: log(frequency) by time 20.4 μs <

 Memory estimate: 7.92 KiB, allocs estimate: 25.

julia> @benchmark @subset($df, ((:x .== 0) .| (:y .== 0)))
BenchmarkTools.Trial: 10000 samples with 1 evaluation.
 Range (min … max): 64.454 μs … 5.446 ms ┊ GC (min … max): 0.00% … 93.48%
 Time (median): 66.507 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 69.103 μs ± 75.999 μs ┊ GC (mean ± σ): 1.48% ± 1.33%

    ▁█▇▁                                                       
  ▁▃████▅▃▃▃▃▃▂▂▂▂▂▂▂▂▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁ ▂
  64.5 μs Histogram: frequency by time 89.7 μs <

 Memory estimate: 21.28 KiB, allocs estimate: 170.

```

It looks like StructArrays is a clear winner at least for speed.

---

<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:** [September 9, 2021, 9:11am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/59 "2021-09-09T09:11:35Z")

</div>

It’s certainly intuitive to read for a lot of people. Some people value a “from first principles” approach higher, where hopefully nothing works “magically” but everything can be derived from the simple building blocks of the language. That’s what base DataFrames does, but of course it trades off syntactic simplicity. I agree that the macro packages DataFramesMeta / DataFrameMacros might take another look at this `across` scenario, maybe there are improvements to be made there.

---

<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:** [September 9, 2021, 9:11am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/60 "2021-09-09T09:11:43Z")

</div>

> [@RobertGregg](#):
>
> In R this would be one very readable line of code:
> 
> ```julia
> mutate(df, across(contains("Temp"), ~ (.x - 32)*(5/9)))
> 
> ```
> 
> No need to worry about calling the `names` function, regular expressions, creating a separate `fahrenheit_to_celsius` function, or giving a rename option.

As others have said, it’s a bit of a special case that applying a function to multiple columns is currently not covered by the macro packages.

But I still think DataFrames.jl wins here because you don’t need to learn all this fancy stuff (whether `ByRow` and `=>` and `cpycols` in Julia, or `accross`, `contains`, `~` and `.x` for R) to get the job done. You can just write:

```julia
df[:, r"Temp"] = (df[:, r"Temp"] .- 32) .* (5/9)

```

Sprinkle in some `!` and `.` for performance, but that’s not necessary.

(To be honest there is one non obvious trick to know if the original columns where `Int`: you cannot put `Float64` values in tem so you need to write `df[!, r"Temp"] = ...` to replace the columns with the right hand side, rather than overwrite them.)

[Previous page](https://discourse.julialang.org/t/frustrated-using-dataframes/67833.md?page=2)

[Next page](https://discourse.julialang.org/t/frustrated-using-dataframes/67833.md?page=4)