How do I add extra rows to each column across all groups in tibble using tidyverse?-CodePudding

How do I add extra rows for all columns (except the grouping variable) for each group id in the data frame?

Data:

> library(tidyverse)
> df <- tibble(id = c("A", "B", "C"), day = c(3, 1, 2), station = c(10, 9, 2))
> df
# A tibble: 3 × 3
  id      day station
  <chr> <dbl>   <dbl>
1 A         3      10
2 B         1       9
3 C         2       2

Expected output:

# A tibble: 6 × 3
# Groups:   id [3]
  id      day station
  <chr> <dbl>   <dbl>
1 A         2       9
2 A         3      10
3 B         0       8
4 B         1       9
5 C         1       1
6 C         2       2

I could change day using:

> df %>% group_by(id) %>% complete(day = (day - 1):day)
# A tibble: 6 × 3
# Groups:   id [3]
  id      day station
  <chr> <dbl>   <dbl>
1 A         2      NA
2 A         3      10
3 B         0      NA
4 B         1       9
5 C         1      NA
6 C         2       2

But I couldn't use mutate appropriately to do this for station as well as since I don't know how to refer to each column inside complete properly:

Failed attempt:

> df %>% 
  group_by(id) %>% 
  mutate(across(c("day", "station"), complete((.x - 1):.x)))

CodePudding user response：

Simplifying ThomasIsCoding's answer:

df %>%
  group_by(id) %>%
  summarize(across(day:station, ~ .x - 1:0))

I don't think it has any advantage to use complete() here if you just want to increment values and do not have a specific range for each group.

CodePudding user response：

This works for your desired output:

df %>% bind_rows(df %>% mutate(across(c('day', 'station'), ~.x - 1 ))) %>% arrange(id)

# A tibble: 6 × 3
# id      day station
# <chr> <dbl>   <dbl>
# 1 A         3      10
# 2 A         2       9
# 3 B         1       9
# 4 B         0       8
# 5 C         2       2
# 6 C         1       1

Explanation:

Here I'm appending the same dataframe but with mutated columns, so:

df %>% mutate(across(c('day', 'station'), ~.x - 1 ))

Is:

# A tibble: 3 × 3
  id      day station
  <chr> <dbl>   <dbl>
1 A         2       9
2 B         0       8
3 C         1       1

Then with bind_rows I'm appending those rows to the original dataframe, which brings:

# A tibble: 6 × 3
  id      day station
  <chr> <dbl>   <dbl>
1 A         3      10
2 B         1       9
3 C         2       2
4 A         2       9
5 B         0       8
6 C         1       1

Finally I just arrange by id so the rows look like your example

CodePudding user response：

A data.table option simplifies the problem

> library(data.table)

> setDT(df)[, lapply(.SD, `-`, 1:0), id]
   id day station
1:  A   2       9
2:  A   3      10
3:  B   0       8
4:  B   1       9
5:  C   1       1
6:  C   2       2

Or, using list unnest

library(tidyr)

df %>%
  group_by(id) %>%
  mutate(across(day:station, ~ list(.x - (1:0)))) %>%
  unnest(day:station) %>%
  ungroup()

which gives

# A tibble: 6 × 3
  id      day station
  <chr> <dbl>   <dbl>
1 A         2       9
2 A         3      10
3 B         0       8
4 B         1       9
5 C         1       1
6 C         2       2