dataframes-rows.qmd

---
# jupyter: julia-1.10
engine: julia
---

# Operations on rows

In this chapter we will see operations that deal with rows, be it ordering or throwing some rows away.

The following is necessary to run all examples:

```{julia}
using DataFrames, PalmerPenguins
using Tidier
import DataFramesMeta as DFM

penguins = PalmerPenguins.load() |> DataFrame;
@slice_head(penguins, n = 10)
```

## Filtering (or: throwing rows away)

To *filter* a dataframe means keeping only the rows that satisfy a certain criteria (ie. a boolean condition).

To filter in Tidier, we use the macro `@filter`. You can use it in the form

```{julia}
@filter(penguins, species == "Adelie")
```

or without parentesis as in 

```{julia}
@filter penguins species == "Adelie"
```

Notice that the columns are typed as if they were variables on the Julia environment. This is inspired by the `tidyverse` behaviour of data-masking: inside a tidyverse verb, the columns are taken as "statistical variables" that exist inside the dataframe as columns.

In DataFramesMeta, we have two macros for filtering: `@subset` and `@rsubset`. Use the first when you have some criteria that uses a whole column, for example:

```{julia}
DFM.@subset penguins :body_mass_g .>= mean(skipmissing(:body_mass_g))
```

Notice the broadcast on >=. We need it because *each variable is interpreted as a vector (the whole column)*. Also, notice that we refer to columns as _symbols_ (i.e. we append `:` to it).

In the above example, we needed the whole column `body_mass_g` to take the mean and then filter the rows based on that. If, however, your filtering criteria only uses information about each row (without needing to see it in context of the whole column), then `@rsubset` (**r**ow subset) is easier to use: it interprets each columns as a value (not an array), so no broadcasting is needed:

```{julia}
DFM.@rsubset penguins :species == "Adelie"
```

In both Tidier and DataFramesMeta, only the rows to which the criteria is `true` are returned. This means that `false` and `missing` are thrown away.

In pure DataFrames, we use the `subset` function, and the criteria is passed with the notation

```{julia}
#| eval: false

subset(penguins, :column => boolean_function)

```

where `boolean_function` is a boolean (with possibly `missing` values) function on 1 variable (the `:column` you passed). Add the kwarg `skipmissing=true` if you want to get rid of missing values.

### Filtering with one criteria

**Problem:** Filtering all the rows with `species` == "Adelie".

::: {.panel-tabset}

## Tidier

```{julia}
@filter penguins species == "Adelie"
```

## DataFramesMeta

```{julia}
DFM.@rsubset penguins :species == "Adelie"
```

## DataFrames

```{julia}
subset(penguins, :species => x -> x .== "Adelie", skipmissing=true)
```

:::

### Filtering with several criteria

**Problem:** Filtering all the rows with `species` == "Adelie", `sex` == "male" and `body_mass_g` > 4000.

::: {.panel-tabset}

## Tidier

```{julia}
@filter penguins species == "Adelie" sex == "male" body_mass_g > 4000
```

## DataFramesMeta

```{julia}
DFM.@rsubset penguins :species == "Adelie" :sex == "male" :body_mass_g > 4000
```

## DataFrames

```{julia}
subset(
    penguins
    , [:species, :sex, :body_mass_g] => 
    (x, y, z) -> (x .== "Adelie") .& (y .== "male") .& (z .> 4000)
    ,skipmissing=true
)
```

:::

**Problem:** Filtering all the rows with `species` == "Adelie" OR `sex` == "male".

::: {.panel-tabset}

## Tidier

```{julia}
@filter penguins (species == "Adelie") | (sex == "male")
```

## DataFramesMeta

```{julia}
DFM.@rsubset penguins (:species == "Adelie") | (:sex == "male")
```

## DataFrames

```{julia}
subset(penguins, [:species, :sex] => (x, y) -> (x .== "Adelie") .| (y .== "male"), skipmissing=true)
```

:::

### Filtering with metadata

By metadata here we mean data that is inside the dataframe, as the mean/max/min of a column.

**Problem:** Filtering all the rows where the `flipper_length_mm` is greater than the mean.

::: {.panel-tabset}

## Tidier

```{julia}
@filter penguins flipper_length_mm > mean(skipmissing(flipper_length_mm))
```

## DataFramesMeta

```{julia}
DFM.@subset penguins :flipper_length_mm .>= mean(skipmissing(:flipper_length_mm))
```

## DataFrames

```{julia}
subset(penguins, :flipper_length_mm => x -> x .> mean(skipmissing(x)), skipmissing=true)
```

:::

### Filtering with a variable column name

Suppose the column you want to filter is a variable, let's say a symbol

```{julia}
my_column = :species;
```

**Problem:** Filtering all the rows where the column stored in `my_column` is "Adelie".

::: {.panel-tabset}

## Tidier

```{julia}
@eval @filter penguins $my_column == "Adelie"
```

## DataFramesMeta

```{julia}
DFM.@rsubset penguins $my_column == "Adelie"
```

## DataFrames

```{julia}
subset(penguins, my_column => x -> x .== "Adelie")
```

:::

In case the column is a string

```{julia}
my_column_string = "species";
```

instead of a symbol, we can write in the same way, just taking care in Tidier to convert it to a symbol

::: {.panel-tabset}

## Tidier

```{julia}
@eval @filter penguins $(Symbol(my_column_string)) == "Adelie"
```


## DataFramesMeta

```{julia}
DFM.@rsubset penguins $(my_column_string) == "Adelie"
```

## DataFrames

```{julia}
subset(penguins, my_column_string => x -> x .== "Adelie")
```

:::

## Arranging

To *arrange* a dataframe means to reorder the rows according to the order of some columns. The rows are first arranged by the first column, then by the second (if any), and so on. In Tidier, when we want to invert the ordering, just put the column name inside a `desc()` call.

### Arranging by one column

**Problem:** Arrange by `body_mass_g`.

::: {.panel-tabset}

## Tidier

```{julia}
@arrange penguins body_mass_g
```

## DataFramesMeta

```{julia}
DFM.@orderby penguins :body_mass_g
```

## DataFrames

```{julia}
sort(penguins, :body_mass_g)
```

:::

### Arranging by two columns, with one reversed

**Problem:** First arrange by `island`, then by reversed `body_mass_g`.

::: {.panel-tabset}

## Tidier

```{julia}
@arrange penguins island desc(body_mass_g)
```

## DataFramesMeta

```{julia}
# works only when the reversed column is numeric?

DFM.@orderby penguins :island :body_mass_g .* -1
```

## DataFrames

```{julia}
sort(penguins, [order(:island), order(:body_mass_g, rev=true)])
```

:::

### Arranging by one variable column

**Problem:** Arrange by a column stored in a variable `my_arrange_column`.

```{julia}
my_arrange_column = :body_mass_g;
```

::: {.panel-tabset}

## Tidier

```{julia}
@eval @arrange penguins $my_arrange_column
```

## DataFramesMeta

```{julia}
DFM.@orderby penguins $my_arrange_column
```

## DataFrames

```{julia}
sort(penguins, my_arrange_column)
```

:::