# 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:** 2

<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, 1:48pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/21 "2021-09-08T13:48:08Z")

</div>

> [@Nathan\_Boyer](#):
>
> Filter out rows which are all zeros.

If you want an in-place operation I would write it:

```julia
filter!(row -> !all(x -> x == 0, row[Not(:node)]), df)

```

and I find it a natural way to do it, as it says:

> for each row drop `:node` column and for all other columns if they pass the `all` test drop the row

Two additional comments:

1. As @pdeffebach said - DataFramesMeta.jl is intended as an entry level package covering most of simple operations more easily.
2. DataFrames.jl gives one a full control over all natural options what you could want to do with data (copy, in-place update, view), but since we give the user full control this requires learning. Therefore the “default mode” is to perform a copy as it is safest as it was commented in the answers above.

Having said that I think the crucial thing is what @Nathan_Boyer started with - it would be great if you would expand on what you would find helpful to learn to handle the DataFrames.jl efficiently (as then we would add it to the package).

---

<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, 1:50pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/22 "2021-09-08T13:50:47Z")

</div>

It seems like Pandas.jl tries to replicate Python style and DataFrames.jl tries to replicate R style. I don’t have much experience with Python or R, so those similarities are of no benefit to me.

I typically have some CSV data files which are joined into one DataFrame by some ID column. A given ID may have many columns of data associated with it, so a lot of my operations are multi-column e.g. operate on all columns but ID or operate on all columns with “temp” in the header. This seems to complicate the required syntax.

I do prefer to mutate a single DataFrame in place, adding columns as necessary. It keeps my workspace clean and avoids me having to rename stuff and keep track of multiple DataFrames. I like it mostly for organization, but speed is a great benefit too.

---

<div class="post-metadata">

**Author:** ![mbauman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mbauman/32/31082_2.png) [@mbauman](https://discourse.julialang.org/u/mbauman)\
**Post date:** [September 8, 2021, 1:50pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/23 "2021-09-08T13:50:52Z")

</div>

Oooh, that’s a tricky one. I’d say that your counterexamples 1 ~~and 3~~ aren’t actually counter examples. The text of the docs is fairly confusing: we should probably strike that “copying the elements” parenthetical. It’s not clear if that means calling `copy(elt)` on each element or if it’s just putting the elements into a _new_ collection? The intention is the latter.

Julia doesn’t _deep_ copy, almost ever. And creating new names for the same object doesn’t make copies, either. And putting that object inside new collections doesn’t copy. But taking one `collection` of type `T` and calling `T(collection)` on it should make a copy (of the _outer_ collection).

---

<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, 1:57pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/24 "2021-09-08T13:57:06Z")

</div>

> [@Nathan\_Boyer](#):
>
> This seems to complicate the required syntax.

Indeed currently in some cases it does. [Add support for All, Between and Not broadcating · Issue #2171 · JuliaData/DataFrames.jl · GitHub](https://github.com/JuliaData/DataFrames.jl/issues/2171) and [Fast row aggregation in DataFrames.jl · Issue #2768 · JuliaData/DataFrames.jl · GitHub](https://github.com/JuliaData/DataFrames.jl/issues/2768) are aimed to fix this case (one is for simplicity, the other is for performance). They will both land in 1.3 release.

> [@Nathan\_Boyer](#):
>
> I do prefer to mutate a single DataFrame in place, adding columns as necessary.

Doing in-place operations is fully supported. It just requires more care when columns are aliased between data frames.

---

<div class="post-metadata">

**Author:** ![oxinabox](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oxinabox/32/206603_2.png) [@oxinabox](https://discourse.julialang.org/u/oxinabox)\
**Post date:** [September 8, 2021, 1:57pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/25 "2021-09-08T13:57:38Z")

</div>

That is fair, and does kinda disgree with my claim that this behavour is natural.  
but not entirely.

I think in particular the reason is that a dataframe is its own collection.  
How it represent that data it its business.  
And the new allocations are made to hold the elements of the passed in vectors.  
The fact that those allocations happen to be copies of the original vectors is an implementation detail (or at least an advanced API feature).  
Just like if i call `Set([1, 2, 3])` A vector will infact be allocated behind the hood (two infact) which will be filled with the contents of the `[1,2,3]` – an even betteer example might be `OrderedSet` which will have multiple vectors behind it, one of which will in this case be exactly the same content as `[1,2,3]` in the same order.

---

<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, 2:00pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/26 "2021-09-08T14:00:17Z")

</div>

Chain.jl (again, re-exported by DataFramesMeta.jl) might give you the convenience of mutating operations without some of the safety pitfalls you’ve been seeing.

---

<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, 2:02pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/27 "2021-09-08T14:02:39Z")

</div>

> [@oxinabox](#):
>
> The fact that those allocations happen to be copies of the original vectors is an implementation detail (or at least an advanced API feature).

This is exactly the intended design:

- entry level user: `DataFrame` is a black box that is designed to be safe to be used; just remember not to ever use `!` on your keyboard while you are an entry level user 😃
- advanced user: you can do all you want (and there is an API for this) however at your own risk

---

<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, 2:26pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/28 "2021-09-08T14:26:46Z")

</div>

> [@bkamins](#):
>
> it would be great if you would expand on what you would find helpful to learn to handle the DataFrames.jl efficiently (as then we would add it to the package).

> [@bkamins](#):
>
> Doing in-place operations is fully supported.

I am sure DataFrames is capable of everything I need it to do. My frustration comes from figuring out what is and isn’t needed/allowed inside the function syntax: `ByRow()`, `eachcol()`, `names()`, `Cols()`, `:`, `.=>`, `.()`, `...` etc.

> [@bkamins](#):
>
> `filter!(row -> !all(x -> x == 0, row[Not(:node)]), df)`

For instance, I did not realize that you can index rows with column selectors inside the function definition.

> [@Nathan\_Boyer](#):
>
> 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.

---

<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, 2:27pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/29 "2021-09-08T14:27:01Z")

</div>

> [@pdeffebach](#):
>
> Chain.jl (again, re-exported by DataFramesMeta.jl) might give you the convenience of mutating operations without some of the safety pitfalls you’ve been seeing.

I will look into Chains.jl. It should be noted that Example 3b (which caused the data corruption) was not my first instinct. I just started getting creative once 3a wasn’t working.

---

<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, 2:40pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/30 "2021-09-08T14:40:11Z")

</div>

For rather complicated things, sometimes a special purpose language is useful. Such as SQL. In which case you can try SQLiteDF which is a package I wrote. It will let you hand it a few data frames, and then load them into SQLite and run a particular query against them. It’s not going to be super fast necessarily, but it’ll get the job done.

---

<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, 2:49pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/31 "2021-09-08T14:49:57Z")

</div>

We could maybe make tools to help users check the inputs. If you do, for example,

```julia
eachcol(df) => fun => AsTable

```

this _won’t_ work, but it’s not obvious why (eachcol is an iterator of actual columns, not column _names_).

But maybe we can do something like `check_valid_transform` to separate the construction of the `source => fun => dest` call from the actual operation.

---

<div class="post-metadata">

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

</div>

> [@pdeffebach](#):
>
> why not use [DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl)?

Another vote for this. I use it in a really large portion of my projects and I’ve never really felt frustrated with syntax at all. Quite the opposite actually - I use the `@chain` macro (previously `@linq`) all the time and find that it makes my code very clean and easy to understand by others as well as by myself when I come back to it at a later date since I usually perform one operation per line.

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [September 8, 2021, 3:32pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/33 "2021-09-08T15:32:25Z")

</div>

I was long frustrated by dataframes - not DataFrames.jl specifically, just dataframes in general, in any language. It’s often much more natural and convenient to represent data as something other than a flat 2d table of plain values.

Languages like Python are slow and working with arbitrary objects quickly becomes infeasible - that’s one reason dataframes are required there. But not in Julia! Built-in data structures like vectors, tuples, dicts, and custom problem-specific structs work together without any issues and slowdowns. They smoothly operate with the Tables.jl ecosystem, and with other packages such as SplitApplyCombine.jl.

Depending on your usecases, built-in structures can outperform DataFrames.jl. Syntax for common operations is reasonably convenient as-is, and can be made even smoother with a thin macro package - see [[ANN] DataPipes.jl - #41 by aplavin](https://discourse.julialang.org/t/ann-datapipes-jl/60734/41) for an example.

---

<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, 3:57pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/34 "2021-09-08T15:57:40Z")

</div>

> [@aplavin](#):
>
> But not in Julia! Built-in data structures like vectors, tuples, dicts, and custom problem-specific structs work together without any issues and slowdowns. They smoothly operate with the Tables.jl ecosystem, and with other packages such as SplitApplyCombine.jl.

I was wondering about this myself: if I would be better off saving each column as a vector variable or creating some custom struct instead of a DataFrame. However, sorting, filtering, and printing as a table all seem pretty inherent to the DataFrame structure. Can you show how you would implement some of my examples?

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [September 8, 2021, 4:10pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/35 "2021-09-08T16:10:28Z")

</div>

It’s easy to work with tables using only Base julia structures - vectors of namedtuples. Examples from your first post, _untested, typos and silly errors are likely_.

1. Select rows with missings.

```julia
using DataPipes

tbl = [(a=…, b=…), …]
@p tbl |> filter(any(ismissing, _))

```

1. Convert temperatures.

```julia
tbl = [(top=1.0, bottom=0.5, label=…), …]
@p tbl |> mutate(top=farenheit_to_celsius(_.top), bottom=farenheit_to_celsius(_.bottom))

```

This is an example where a nested structure is more natural:

```julia
tbl = [(label=…, temps=(0.5, 1.0)), …]
@p tbl |> mutate(temps=farenheit_to_celsius.(_.temps))

```

1. Select all-zero rows: same as above, `@p tbl |> filter(all(==(0), _))`.

---

<div class="post-metadata">

**Author:** ![lmiq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lmiq/32/18314_2.png) [@lmiq](https://discourse.julialang.org/u/lmiq)\
**Post date:** [September 8, 2021, 4:20pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/36 "2021-09-08T16:20:25Z")

</div>

StructArrays is also a neat possibility in many cases:

```julia
julia> using StructArrays

julia> struct Data
           name::String
           weight::Float64
       end

julia> data = StructArray([Data("Maguila",130.0), Data("Tyson",120.0)])
2-element StructArray(::Vector{String}, ::Vector{Float64}) with eltype Data:
 Data("Maguila", 130.0)
 Data("Tyson", 120.0)

julia> kg_to_g(x) = 1000*x
kg_to_g (generic function with 1 method)

julia> data.weight .= kg_to_g.(data.weight)
2-element Vector{Float64}:
 130000.0
 120000.0

julia> data
2-element StructArray(::Vector{String}, ::Vector{Float64}) with eltype Data:
 Data("Maguila", 130000.0)
 Data("Tyson", 120000.0)

julia> data = filter(x -> x.weight < 125000, data)
1-element StructArray(::Vector{String}, ::Vector{Float64}) with eltype Data:
 Data("Tyson", 120000.0)

```

(inplace `filter!` does not work, though).

---

<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, 5:34pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/37 "2021-09-08T17:34:42Z")

</div>

> [@Nathan\_Boyer](#):
>
> # Examples
> 
> 1. What rows are missing data?
> 2. Convert temperature data to Celsius.
> 3. Filter out rows which are all zeros.
> 
> ```julia
> 
> ```

**Answers**

**1. What rows are missing data?**

```julia
using DataFrames, SQLdf

df = DataFrame(node=1:4, x=[1,missing,9,5], y=[0,0,missing,8])

@sqldf "select * from df where x is NULL or y is NULL"

2×3 DataFrame
 Row │ node x y       
     │ Int64 Int64? Int64?  
─────┼─────────────────────────
   1 │ 2 missing 0
   2 │ 3 9 missing 

```

**2. Convert temperature data to Celsius.**

```julia
df = DataFrame(node=1:4, x=[1,0,9,5], F=[0,0,12,8])

@sqldf "select * , (F-32)/1.8 as Celsius from df"

4×4 DataFrame
 Row │ node x F Celsius  
     │ Int64 Int64 Int64 Float64  
─────┼───────────────────────────────
   1 │ 1 1 0 -17.7778
   2 │ 2 0 0 -17.7778
   3 │ 3 9 12 -11.1111
   4 │ 4 5 8 -13.3333

```

**3. Filter out rows which are all zeros.**

```julia
df = DataFrame(node=1:4, x=[1,0,9,5], y=[0,0,12,8])

@sqldf "select * from df where x !=0 and y != 0"

2×3 DataFrame
 Row │ node x y     
     │ Int64 Int64 Int64 
─────┼─────────────────────
   1 │ 3 9 12
   2 │ 4 5 8

```

---

<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, 5:55pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/38 "2021-09-08T17:55:32Z")

</div>

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.

---

<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, 6:13pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/39 "2021-09-08T18:13:10Z")

</div>

Yes, it seems like all the suggestions so far would still require a `for` loop.

```julia
for name in names(df, r"Temp")
  # Transform one column at a time (corrected)
end

```

It would be nice to be able to broadcast the transformations instead. (I’m not sure if even the former is possible with SQL since it parses a big string.) However, I appreciate seeing these other packages in action nonetheless.

---

<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, 6:15pm UTC](https://discourse.julialang.org/t/frustrated-using-dataframes/67833/40 "2021-09-08T18:15:34Z")

</div>

Not one row at a time, one column at a time.

I don’t see any big issue with using a `for` loop. The only issue is that you have to use mutating functions, i.e. `@rtransform!`, which are less safe.

But in general, something like

```julia
for name in names(df, r"Temp")
    @rtransform! df $name = fahrenheit_to_celsius($name)
end

```

is very Stata-esque. And Stata is a battle tested data-cleaning framework that a lot of people enjoy, so it’s not that bad.

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

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