Create new rows based on numbers in a column

Question

I currently have a table with a quantity in it.

ID	Code	Quantity
1	A	1
2	B	3
3	C	2
4	D	1

Is there anyway to get this table?

ID	Code	Quantity
1	A	1
2	B	1
2	B	1
2	B	1
3	C	1
3	C	1
4	D	1

I need to break out the quantity and have that many number of rows.

Thanks!!!!

Anoushiravan R · Accepted Answer · 2021-05-21T11:22:48.477

Updated Now we have stored the separated, collapsed values into a new column:

library(dplyr)
library(tidyr)

df %>%
  group_by(ID) %>%
  uncount(Quantity, .remove = FALSE) %>%
  mutate(NewQ = 1)

# A tibble: 7 x 4
# Groups:   ID [4]
     ID Code  Quantity  NewQ
  <int> <chr>    <int> <dbl>
1     1 A            1     1
2     2 B            3     1
3     2 B            3     1
4     2 B            3     1
5     3 C            2     1
6     3 C            2     1
7     4 D            1     1

Updated In case we opt not to replace the existing Quantity column with the collapsed values.

df %>%
  group_by(ID) %>%
  mutate(NewQ = ifelse(Quantity != 1, paste(rep(1, Quantity), collapse = ", "),
                           as.character(Quantity))) %>%
  separate_rows(NewQ) %>%
  mutate(NewQ = as.numeric(NewQ))


# A tibble: 7 x 4
# Groups:   ID [4]
     ID Code  Quantity  NewQ
  <int> <chr>    <int> <dbl>
1     1 A            1     1
2     2 B            3     1
3     2 B            3     1
4     2 B            3     1
5     3 C            2     1
6     3 C            2     1
7     4 D            1     1

Thank you!!! It perfectly works. Instead of replacing the original Quantity variable, do you know how could I add a new one with the break out? I am trying to keep the original one for a visualization... — Laura Cuesta, May 21 '21 at 09:40
@LauraCuesta My pleasure. Yes of course, I just modified both solutions so that we have a new column for separated collapsed values. — Anoushiravan R, May 21 '21 at 09:47

score 1 · Answer 2 · answered May 21 '21 at 08:32

We could use slice

library(dplyr)
df %>% 
  group_by(ID) %>% 
  slice(rep(1:n(), each = Quantity)) %>% 
  mutate(Quantity= rep(1))

Output:

     ID Code  Quantity
  <dbl> <chr>    <dbl>
1     1 A            1
2     2 B            1
3     2 B            1
4     2 B            1
5     3 C            1
6     3 C            1
7     4 D            1

score 1 · Answer 3 · answered May 21 '21 at 11:09

A base R option using rep

transform(
  `row.names<-`(df[rep(1:nrow(df), df$Quantity), ], NULL),
  Quantity = 1
)

gives

  ID Code Quantity
1  1    A        1
2  2    B        1
3  2    B        1
4  2    B        1
5  3    C        1
6  3    C        1
7  4    D        1

Create new rows based on numbers in a column

3 Answers3