How to repeat rows by their value by multiple columns and divide back

Question

Let's say I have this dataframe:

> df <- data.frame(A=1:5, B=c(0, 0, 3, 0, 0), C=c(1, 0, 0, 1, 0), D=c(0, 2, 0, 0, 1))
> df
  A B C D
1 1 0 1 0
2 2 0 0 2
3 3 3 0 0
4 4 0 1 0
5 5 0 0 1

How would I go about converting it to:

As you can see there are value 2 and 3, I want to repeat them by that length and change the values back to 1. How would I do that?

I also want to duplicate the the A column as you can see.

I tried:

replace(df[rep(rownames(df), select(df, -A)),], 2, 1)

But it gives me an error.

@RonakShah Yes! and also duplicate A, but on the B C and D columns I want the values to be evenly divided to become 1. — U13-Forward, Oct 13 '21 at 03:42
For a row would there always be only 1 value which is not 0 , ignoring column A ofcourse. — Ronak Shah, Oct 13 '21 at 03:44
another one: `df <- df[rep(1:nrow(df), rowSums(df[2:4])), ]; df[2:4] <- +!!df[2:4]; df` — rawr, Oct 13 '21 at 04:06
@rawr Just that doesn't floor the values 1, and if you could add that you could post an answer — U13-Forward, Oct 13 '21 at 04:07
@U12-Forward which part are you looking at? the result i get is the same as the one below — rawr, Oct 13 '21 at 04:09
@rawr The values should be come 1, not continue to be `3` or `2`. — U13-Forward, Oct 13 '21 at 04:11
@rawr Oh a typo from my end! That works! You should post as an answer, btw I just posted one too — U13-Forward, Oct 13 '21 at 04:14

score 3 · Accepted Answer · answered Oct 13 '21 at 03:46

One option would be to get max value from columns B, C and D using pmax, use uncount to repeat the rows. Use pmin to replace the values greater than 1 to 1.

library(dplyr)
library(tidyr)

df %>%
  mutate(repeat_row = pmax(B, C, D)) %>%
  uncount(repeat_row) %>%
  mutate(across(-A, pmin, 1))

#  A B C D
#1 1 0 1 0
#2 2 0 0 1
#3 2 0 0 1
#4 3 1 0 0
#5 3 1 0 0
#6 3 1 0 0
#7 4 0 1 0
#8 5 0 0 1

score 3 · Answer 2 · answered Oct 13 '21 at 05:42

Apparently, there's just one value > 0 in columns B to D, so we can exploit the partial rowSums for a replicate call on columns B to D binarized using > 0. So that we can use this in Map, we transpose twice. Rest is cosmetics.

t(do.call(cbind, Map(replicate,
                     rowSums(df[-1]), 
                     as.data.frame(t(cbind(df[1], df[-1] > 0)))))) |>
  as.data.frame() |>
  setNames(names(df))
#   A B C D
# 1 1 0 1 0
# 2 2 0 0 1
# 3 2 0 0 1
# 4 3 1 0 0
# 5 3 1 0 0
# 6 3 1 0 0
# 7 4 0 1 0
# 8 5 0 0 1

Note: R>=4.1 used.

@U12-Forward The `|>` is the new base R pipe which works similar to the magrittr `%>%` pipe but is much faster. See [there](https://michaelbarrowman.co.uk/post/the-new-base-pipe/). — jay.sf, Oct 13 '21 at 05:55

U13-Forward · Answer 3 · 2021-10-13T07:40:12.710

Just to modify Ronak Shah's answer a bit, I realized you could simply just do it with only dplyr:

library(dplyr)

df[rep(rownames(df), apply(select(df, -A), 1, max)),] %>%
  as.data.frame(row.names=1:nrow(.)) %>%
  mutate(across(-A, pmin, 1))

Output:

Or with rowSums:

library(dplyr)

df[rep(rownames(df), rowSums(select(df, -A)),] %>%
  as.data.frame(row.names=1:nrow(.)) %>%
  mutate(across(-A, pmin, 1))

How to repeat rows by their value by multiple columns and divide back

3 Answers3