how to use arrange(x,.by_group=TRUE) if group_by() is not supported

[alejandrohagan]

Hi,

I love the package!

I know that group_by() is not supported when prudence level is set to 'stingy' however how can we do arrange within groups?

previously you could use group_bu() and then arrange(x,.by_group=TRUE).

Is there an alternative in duckplyr

[krlmlr]

Thanks. How is .by_group = TRUE different from prepending the grouping variables?

Request

data %>%
  group_by(a, b) %>%
  arrange(c, .by_group = TRUE)

Alternative

data %>%
  arrange(a, b, c)

[alejandrohagan]

hi -- sorry for delay.

The main issue is that you want to have defined order within group so that if you use some downstream functions like cummean or fill() it respects the groups order and boundary. The below examples give different outputs

library(tidyverse)

dat <- tibble(
  group=c(1,1,2,2,2,3,3,3)
  ,values=c(1,NA,NA,NA,2,1,4,2)
)



## intended solution
dat |> 
  group_by(group) |>
  arrange(desc(group),.by_group = TRUE) |> 
  fill(values,.direction="down")

## same values but table is flipped

dat |> 
  group_by(group) |>
  arrange(desc(group)) |> 
  fill(values,.direction="down") 

## wrong values
dat |> 
  arrange(desc(group)) |> 
  fill(values,.direction="down")

without group_by() how would we sort within groups and define group boundaries?

[mgacc0]

The main issue is that {duckplyr} doesn't implement fill.
An SQL translation would be something like

CREATE TABLE sales (
    category VARCHAR,
    date DATE,
    sales_amount INTEGER
);

INSERT INTO sales VALUES
('Electronics', '2023-01-01', 100),
('Electronics', '2023-01-02', NULL),
('Electronics', '2023-01-03', NULL),
('Electronics', '2023-01-04', 150),
('Apparel', '2023-01-01', 50),
('Apparel', '2023-01-02', NULL),
('Apparel', '2023-01-03', 75),
('Apparel', '2023-01-04', NULL),
('Electronics', '2023-01-05', 200),
('Apparel', '2023-01-05', NULL);


WITH stepped_values AS (
    SELECT
        category,
        date,
        sales_amount,
        SUM(CASE WHEN sales_amount IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY category ORDER BY date) AS fill_group
    FROM
        sales
)
SELECT
    category,
    date,
    FIRST_VALUE(sales_amount) OVER (PARTITION BY category, fill_group ORDER BY date) AS filled_sales_amount
FROM
    stepped_values
ORDER BY
    category, date;

┌─────────────┬────────────┬─────────────────────┐
│  category   │    date    │ filled_sales_amount │
│   varchar   │    date    │        int32        │
├─────────────┼────────────┼─────────────────────┤
│ Apparel     │ 2023-01-01 │                  50 │
│ Apparel     │ 2023-01-02 │                  50 │
│ Apparel     │ 2023-01-03 │                  75 │
│ Apparel     │ 2023-01-04 │                  75 │
│ Apparel     │ 2023-01-05 │                  75 │
│ Electronics │ 2023-01-01 │                 100 │
│ Electronics │ 2023-01-02 │                 100 │
│ Electronics │ 2023-01-03 │                 100 │
│ Electronics │ 2023-01-04 │                 150 │
│ Electronics │ 2023-01-05 │                 200 │
├─────────────┴────────────┴─────────────────────┤
│ 10 rows                              3 columns │
└────────────────────────────────────────────────┘