# 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:** 1
### Author:  [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)
#### Post date: [September 7, 2021, 9:38pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/1 "2021-09-07T21:38:10Z")
# Overview
`DataFrames.jl` seems to be the preferred way to work with tabular data in Julia, but it never does what I want the first time. The syntax I need to contrive to do things I think would be simple is mind-boggling, especially when I try to explain what my code is doing to non-coders. It makes me wonder if there is a different package I should be using for my typical workflow, since the things I want to do often are so hard. I am usually joining CSVs together, filtering, sorting, and computing some new columns. These all sound like functions provided by DataFrames, but I don’t think I do it as explained in [The Split-Apply-Combine Strategy](https://dataframes.juliadata.org/latest/man/split_apply_combine/).
I have read the [documentation](https://dataframes.juliadata.org/latest/) and done the [tutorial on Github](https://github.com/bkamins/Julia-DataFrames-Tutorial). I nodded along as I went through, but using `DataFrames` on my own projects has been a different experience. I always think “No problem, I will whip this up in 10 minutes.” but an hour later am still sifting through error messages and documentation.
# 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.
> **Code for Example 3**
>
> ```julia-auto
> julia> using DataFrames
>
> julia> df = DataFrame(node=1:4, x=[1,0,9,5], y=[0,0,12,8])
> 4×3 DataFrame
> Row │ node x y
> │ Int64 Int64 Int64
> ─────┼─────────────────────
> 1 │ 1 1 0
> 2 │ 2 0 0
> 3 │ 3 9 12
> 4 │ 4 5 8
>
> ```
>
> Trying to use the previous StackOverflow answers as a guide:
>
> ```julia-auto
> 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"}}})
> Closest candidates are:
> !(::Bool) at bool.jl:33
> !(::Function) at operators.jl:968
> !(::Missing) at missing.jl:101
> Stacktrace:
> [1] filter!(f::Vector{Pair{String, ByRow{var"#204#206"}}}, df::DataFrame)
> @ DataFrames C:\Users\nboyer.AIP\.julia\packages\DataFrames\vuMM8\src\abstractdataframe\abstractdataframe.jl:1127
> [2] top-level scope
> @ REPL[106]:1
>
> ```
>
> This works but destroys `df`:
>
> ```julia-auto
> julia> filter!(row -> any(x -> x>0, row), df[!,Not(:node)])
> 3×2 DataFrame
> Row │ x y
> │ Int64 Int64
> ─────┼──────────────
> 1 │ 1 0
> 2 │ 9 12
> 3 │ 5 8
>
> julia> df
> Error showing value of type DataFrame:
> ERROR: AssertionError: Data frame is corrupt: length of column :x (3) does not match length of column 1 (4). The column vector has likely been resized unintentionally (either directly or because it is shared with another data frame).
> Stacktrace:
> [1] _check_consistency(df::DataFrame)
> @ DataFrames C:\Users\nboyer.AIP\.julia\packages\DataFrames\vuMM8\src\dataframe\dataframe.jl:447
>
> ```
>
> Finally, after much frustration, this works as expected (nested anonymous functions with a tuple splat that is part of a pair using no broadcasting dots):
>
> ```julia-auto
> julia> filter!(Not(:node) => (row...) -> any(x -> x>0, row), df)
> 3×3 DataFrame
> Row │ node x y
> │ Int64 Int64 Int64
> ─────┼─────────────────────
> 1 │ 1 1 0
> 2 │ 3 9 12
> 3 │ 4 5 8
>
> ```
# Final Thoughts
Perhaps the most frustrating part is that I am unable to reason my way through the error messages. It is usually impossible to pare the syntax problem down to something smaller and make sure the pieces that make up `args` are passing the types and data structures I expect (row, column, vector, scalar, etc.). You have to get it all right in one go, or you will just get a random method error somewhere.
I am not trying to complain that the package is bad. It seems very powerful and obviously many people use it successfully. I’m just not sure what I am doing wrong. After troubleshooting syntax for hours, I usually ask myself why I didn’t just use Excel (and I hate Excel). Do you all have any tips?
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 7, 2021, 9:45pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/2 "2021-09-07T21:45:05Z")
These are valid concerns, but why not use [DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl)? It aims to solve exactly these problems. The syntax for DataFrames transformations is definitely at times, but that is why DataFramesMeta exists and is overseen by the core contributors of DataFrames.jl.
Admittedly, some of these problems are not easily solved by DataFramesMeta, particularly cases where you need to work with many columns at once. But there is nothing stating you have to do everything in a single `transform` call. Some of these can be done in a for loop. Instead of
```julia
transform(df, names(df, r"Temp") .=> fun; rename = false)
```
do
```julia
celsius_names = names(df, r"Temp")
for cname in celsius_name
transform!(...)
end
```
---
### Author:  [@juliohm](https://discourse.julialang.org/u/juliohm)
#### Post date: [September 7, 2021, 9:45pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/3 "2021-09-07T21:45:45Z")
I rarely use DataFrames.jl directly because it is too low-level, I second the suggestion by @pdeffebach. DataFramesMeta.jl is clean, specially now that it has row-based operations like `@rtransform` etc.
---
### Author:  [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)
#### Post date: [September 7, 2021, 9:47pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/4 "2021-09-07T21:47:18Z")
> [@pdeffebach](#):
>
> why not use [DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl)?
… because it sounds scary. Is that not Advanced DataFrames?
---
### Author:  [@jzr](https://discourse.julialang.org/u/jzr)
#### Post date: [September 7, 2021, 9:48pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/5 "2021-09-07T21:48:21Z")
I prefer to avoid macro libraries myself (DataFramesMeta, DFMacros) because I feel they confuse more than they help if the functional API is well designed.
> [@Nathan\_Boyer](#):
>
> 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)
The solution to this one is `filter(row->(any(ismissing,row), df, view=true)`. DataFrames.jl differs from every other part of Julia in that it copies by default, because it is aimed at beginners who might not understand the difference. Personally I think that’s not a good approach for a high-performance library. I agree with you there.
> [@Nathan\_Boyer](#):
>
> - [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)
I would say
```julia
transform(df, :top => ByRow(ftoc) => :topc, :bottom => ByRow(ftoc) => :bottomc)
```
This seems fine to me, unless you want to retain each column name, in which case it’s annoying. Another function could do that job more easily, specifically for changing the value while retaining the name.
> [@Nathan\_Boyer](#):
>
> Filter out rows which are all zeros.
```julia
filter(row->all(==(0), row), df)
filter(row->all(==(0), row), df, view=true)
```
Seems ok, with the caveat about `view`.
The only macro I do want is Chain.jl, because it’s so simple and universal.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 7, 2021, 9:50pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/6 "2021-09-07T21:50:14Z")
No. It’s designed to be simpler. My ideal audience is probably a first year MPP student doing data analysis for the first time. Maybe work through [this tutorial](https://juliadata.github.io/DataFramesMeta.jl/stable/dplyr/) and see if it’s for you.
But as noted above in an edit, for some of these programmatic ones, working with many rows at once, a `for` loop with DataFramesMeta is what I would turn to.
One other thought, if you are using Chain.jl for piping, which is exported by DataFramesMeta, the `@aside` macro is suuuuuppper (super!) useful for mixing `dplyr`-esque split-apply-combine and more programming-based operations.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 7, 2021, 9:53pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/7 "2021-09-07T21:53:36Z")
> [@jzr](#):
>
> Personally I think that’s not a good approach for a high-performance library. I agree with you there.
I would disagree with this, take a look at the example OP provided of a scary error
```julia
julia> filter!(row -> any(x -> x>0, row), df[!,Not(:node)])
3×2 DataFrame
Row │ x y
│ Int64 Int64
─────┼──────────────
1 │ 1 0
2 │ 9 12
3 │ 5 8
julia> df
Error showing value of type DataFrame:
ERROR: AssertionError: Data frame is corrupt: length of column :x (3) does not match length of column 1 (4). The column vector has likely been resized unintentionally (either directly or because it is shared with another data frame).
```
This error arises because `df[!, Not(:node)]` is not the same object as `df`, but shares the same columns. The `filter!` operation modifies those arrays, leading to corruption of `df`. This is a direct consequence of need-for-speed non-copying.
@Nathan_Boyer my advice: only use `:` when indexing, instead of `!` and only use copying versions of functions, i.e. `filter` instead of `filter`, `@rtransform` instead of `@rtransform!` (from DataFramesMeta).
---
### Author:  [@jzr](https://discourse.julialang.org/u/jzr)
#### Post date: [September 7, 2021, 9:54pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/8 "2021-09-07T21:54:29Z")
I don’t use mutating calls in dataframes except when forced by some particular unusual constraint. They confuse the program state, introducing inconsistencies as shown above.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 7, 2021, 9:57pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/9 "2021-09-07T21:57:46Z")
I agree to some extent (though if you are coming from Stata, having a single object which is modified is more natural). This certainly seems to be the case for OP. But DataFrames.jl provides the option to have maximum performance. It caters to both demographics.
---
### Author:  [@jzr](https://discourse.julialang.org/u/jzr)
#### Post date: [September 7, 2021, 9:58pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/10 "2021-09-07T21:58:46Z")
Copying-style functions are so different in semantics and performance from view-style functions that they really shouldn’t be arguments on the same function, but two families of functions.
Having to write `view=true` and `copycols=false` everywhere doesn’t really suit my preference for concision either.
---
### Author:  [@johnmyleswhite](https://discourse.julialang.org/u/johnmyleswhite)
#### Post date: [September 7, 2021, 10:05pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/11 "2021-09-07T22:05:41Z")
> [@jzr](#):
>
> The solution to this one is `filter(row->(any(ismissing,row), df, view=true)` . DataFrames.jl differs from every other part of Julia in that it copies by default
This claim is unclear to me – I would say one of the most striking differences between Julia and Python/NumPy is that Julia almost always copies arrays by default whereas Python/NumPy almost never does. What did you have in mind?
---
### Author:  [@jzr](https://discourse.julialang.org/u/jzr)
#### Post date: [September 7, 2021, 10:20pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/12 "2021-09-07T22:20:14Z")
> [@johnmyleswhite](#):
>
> What did you have in mind?
Thinking of dataframes as a collection of collections, it would be unusual for a function that replaces the outer collection to also replace the inner collections. For example, consider a function that adds a key to a dict of vectors. `transform(df, :a => ByRow(identity) => :b)` allocates a new vector for each column `a:z`. It would be more typical to reuse the unchanged columns by reference. Likewise, filtering a vector of dicts wouldn’t allocate new dicts.
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 7, 2021, 11:41pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/13 "2021-09-07T23:41:50Z")
> [@Nathan\_Boyer](#):
>
> `DataFrames.jl` seems to be the preferred way to work with tabular data in Julia
Perhaps this video would help [https://www.youtube.com/watch?v=rDvpLFxcL84](https://www.youtube.com/watch?v=rDvpLFxcL84)
---
### Author:  [@oxinabox](https://discourse.julialang.org/u/oxinabox)
#### Post date: [September 8, 2021, 12:07am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/14 "2021-09-08T00:07:15Z")
> [@jzr](#):
>
> DataFrames.jl differs from every other part of Julia in that it copies by default
Sliced indexing on arrays in Julia always returns a copy.
Similar `filter` on arrays returns a newly allocated array not a view (unless you use `Iterators.filter`)
---
### Author:  [@jzr](https://discourse.julialang.org/u/jzr)
#### Post date: [September 8, 2021, 12:14am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/15 "2021-09-08T00:14:47Z")
That’s not what happens here. DataFrames.jl `filter` allocates new contents, not just new DataFrames. Likewise, `transform` allocates new column vectors for unchanged columns.
Edit: I see the source of confusion. Some people are thinking of dataframes as similar to matrices of values while I’m thinking of them as collections of collections. Copying a matrix is different from copying a vector of dicts or a dict of vectors: copying a matrix copies deeper. See the `copycols` argument of `transform` for detail.
---
### Author:  [@oxinabox](https://discourse.julialang.org/u/oxinabox)
#### Post date: [September 8, 2021, 9:41am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/16 "2021-09-08T09:41:18Z")
> [@jzr](#):
>
> Some people are thinking of dataframes as similar to matrices of values while I’m thinking of them as collections of collections.
This was once a matter of opinion. It is not any more.
One of the things done during the DataFrame stabalization process was basically to cement into dataframe’s concept that it was its own 2D type.
It wasn’t a collection of columns (or a collection of rows).
It’s also not a “matrix” because the semantics of a matrix largely only make sense for homogeneous types.
(Other Table.jl table types exist and more can be created, which happily _can_ hold the opinion that they are just a collecton of columns or of rows. And they will work happily with e.g. Query.jl, CSV.jl, LibPQ.jl etc, and be trivially convertable to and from DataFrames.)
Once one takes the view that it is its own collection type, and not a _wrapper_,
it is now deducable that it should `copy`:
From the [docs on mutable collection constructors (in this case in contrast to convert)](https://docs.julialang.org/en/v1/manual/conversion-and-promotion/#Mutable-collections):
> … if `T` is a mutable collection type then `T(x)` should always make a new collection (copying elements from `x` ).
A lot (all) of DataFrame’s copies are boiling down to ensuring they are safe.
It used to be very easy to corrupt a DataFrame by ending up sharing columns with another DataFrame and then call `unique!` and now ended up with a corrupted dataframe with columns of different lengths.
---
### Author:  [@mcreel](https://discourse.julialang.org/u/mcreel)
#### Post date: [September 8, 2021, 10:27am UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/17 "2021-09-08T10:27:49Z")
I am no DataFrames expert, but I have come to like it, as it allows me to reproduce my data preparation routines, rather than hacking together a solution. The manipulation tools take a little learning, but once a few important ones are learned, I find I can get done the things I need to do.
For Example3, a solution is
```julia
using DataFrames, DataFramesMeta
df = DataFrame(node=1:4, x=[1,0,9,5], y=[0,0,12,8])
@subset!(df, ((:x .!= 0) .| (:y .!= 0)))
```
For my own learning, I also looked at Example2, and came up with
```julia
using DataFrames, DataFramesMeta
fahrenheit_to_celsius(x) = Int(round((x - 32) * 5 / 9))
df= DataFrame(Time = [3, 4, 5], TopTemp = [70, 73, 100], BottomTemp = [50, 55, 80])
[df[!,v] = fahrenheit_to_celsius.(df[!,v]) for v in (:TopTemp, :BottomTemp)]
```
---
### Author:  [@KajWiik](https://discourse.julialang.org/u/KajWiik)
#### Post date: [September 8, 2021, 12:43pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/18 "2021-09-08T12:43:04Z")
I noticed that `DataFramesMeta @subset` is about ten times slower than direct indexing (OK, this is a small DataFrame):
```julia
julia> using DataFrames, DataFramesMeta
julia> df = DataFrame(node=1:4, x=[1,0,9,5], y=[0,0,12,8])
julia> @benchmark @subset(df, ((:x .!= 0) .| (:y .!= 0)))
BenchmarkTools.Trial: 10000 samples with 1 evaluation.
Range (min … max): 45.613 μs … 6.984 ms ┊ GC (min … max): 0.00% … 99.12%
Time (median): 47.490 μs ┊ GC (median): 0.00%
Time (mean ± σ): 49.393 μs ± 69.472 μs ┊ GC (mean ± σ): 1.40% ± 0.99%
▄█▆▂
▁▃▇████▆▄▃▂▂▂▂▂▃▃▃▂▂▂▂▂▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁ ▂
45.6 μs Histogram: frequency by time 65.2 μs <
Memory estimate: 9.67 KiB, allocs estimate: 167.
julia> @benchmark df[(df.x .!= 0) .| (df.y .!= 0), :]
BenchmarkTools.Trial: 10000 samples with 9 evaluations.
Range (min … max): 3.154 μs … 604.375 μs ┊ GC (min … max): 0.00% … 98.70%
Time (median): 3.387 μs ┊ GC (median): 0.00%
Time (mean ± σ): 3.724 μs ± 10.293 μs ┊ GC (mean ± σ): 4.76% ± 1.71%
▄██▆▁
▁▁▂▆█████▆▄▂▂▂▂▂▂▂▂▂▂▂▂▂▂▂▂▂▃▂▂▂▂▂▂▂▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁ ▂
3.15 μs Histogram: frequency by time 5 μs <
Memory estimate: 1.70 KiB, allocs estimate: 22.
```
I guess it is OK to use direct indexing?
---
### Author:  [@CameronBieganek](https://discourse.julialang.org/u/CameronBieganek)
#### Post date: [September 8, 2021, 1:11pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/19 "2021-09-08T13:11:20Z")
> [@oxinabox](#):
>
> From the [docs on mutable collection constructors (in this case in contrast to convert)](https://docs.julialang.org/en/v1/manual/conversion-and-promotion/#Mutable-collections):
>
> > … if `T` is a mutable collection type then `T(x)` should always make a new collection (copying elements from `x` ).
This seems to be an unreliable heuristic, even in Base.
### Counterexample 1
```julia
julia> x = "a" => [1, 2];
julia> d = Dict(x)
Dict{String, Vector{Int64}} with 1 entry:
"a" => [1, 2]
julia> x[2][2] = 100
100
julia> d
Dict{String, Vector{Int64}} with 1 entry:
"a" => [1, 100]
```
### Counterexample 2
```julia
julia> using LinearAlgebra
julia> m = [1 2
3 4]
2×2 Matrix{Int64}:
1 2
3 4
julia> sm = Symmetric(m)
2×2 Symmetric{Int64, Matrix{Int64}}:
1 2
2 4
julia> m[1, 2] = 100;
julia> sm
2×2 Symmetric{Int64, Matrix{Int64}}:
1 100
100 4
```
I agree with @jzr that it seems that Julia leans more toward _not copying_ than toward _copying_.
---
### Author:  [@Palli](https://discourse.julialang.org/u/Palli)
#### Post date: [September 8, 2021, 1:13pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/20 "2021-09-08T13:13:08Z")
> [@Nathan\_Boyer](#):
>
> The syntax I need to contrive to do things I think would be simple is mind-boggling, especially when I try to explain what my code is doing to non-coders.
> [@Nathan\_Boyer](#):
>
> > [@pdeffebach](#):
> >
> > why not use [DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl)?
>
> … because it sounds scary. Is that not Advanced DataFrames?
I admit I’ve never used DataFramesMeta.jl either, overlooked, thought not needed. I hope it resolves all your issues. If not there’s also Pandas.jl. Are there other conpeting packages? Maybe Query[verse].jl I’m not up-to-speed on.
I would hate for people to migrate to Python totally, based on a misunderstanding, or even partially to Python’s Pandas (it used PyCall.jl) if not needed. Many people are happy with Pandas, it’s an “all-in-one” package I understand, more of a think in Python ecosystem.
Should DataFramesMeta.jl be merged into DataFrames.jl (or added as its dependency)? I don’t know the history of it, but I at least assumed DataFrames.jl to be Julia’s go to package.
My PR to Pandas.jl got recently merged (after a long time, making it slightly better), maybe it’s not updated often, at least not as much as DF.jl, could be because it’s a wrapper, but I’m still not sure it covers all that Pandas can do.
[Next page](https://discourse.julialang.org/t/frustrated-using-dataframes/67833.md?page=2)