Need an R function to replicate X data by Y counts, where X contains some repeated values-CodePudding

I have a fairly large data set (18,000) rows with 2 columns off interest. I would like to treat one (X) as the quantitative values, and the other (Y) as counts, and repeat the X data based on the counts. Due to the nature off the data, there are repeat values in the X column, and I just want to create a new data set containing all X values and its repeated measurements. I have tried doing the following, but it returns an invalid times argument: rep, df$X, df$Y

I am not sure why this error is occurring, and don't know where to go from here. Any help is appreciated. Below is a small sample of my data.

CodePudding user response：

I can reproduce that error when one or more Y is NA (or negative):

df
#       X  Y
# 1  8.76  3
# 2 24.69 NA
# 3  6.24  2
# 4  1.17  0
# 5  6.54  3
# 6 10.29  0
# 7 11.04  1
# 8 16.71  1
rep(df$X, df$Y)
# Error in rep(df$X, df$Y) : invalid 'times' argument
df$Y[2] <-  -1
rep(df$X, df$Y)
# Error in rep(df$X, df$Y) : invalid 'times' argument

We can replace the NA with 0:

rep(df$X, pmax(0, df$Y, na.rm = TRUE))
#  [1]  8.76  8.76  8.76  6.24  6.24  6.54  6.54  6.54 11.04 16.71

Data

df <- structure(list(X = c(8.76, 24.69, 6.24, 1.17, 6.54, 10.29, 11.04, 16.71), Y = c(3L, NA, 2L, 0L, 3L, 0L, 1L, 1L)), row.names = c(NA, -8L), class = "data.frame")

CodePudding user response：

Maybe you are looking for uncount?

library(tidyr)
library(dplyr)

df %>% 
  uncount(count)

This returns

# A tibble: 10 x 1
   value
   <dbl>
 1  8.76
 2  8.76
 3  8.76
 4  6.24
 5  6.24
 6  6.54
 7  6.54
 8  6.54
 9 11.0 
10 16.7

CodePudding user response：

A base R alternative:

transform(df[rep(seq_len(nrow(df)), df$y),], y = sequence(df$y))

output:

        x y
1    8.76 1
1.1  8.76 2
1.2  8.76 3
3    6.24 1
3.1  6.24 2
5    6.54 1
5.1  6.54 2
5.2  6.54 3
7   11.04 1
8   16.71 1

data:

df <- structure(list(x = c(8.76, 24.69, 6.24, 1.17, 6.54, 10.29, 11.04, 
16.71), y = c(3L, 0L, 2L, 0L, 3L, 0L, 1L, 1L)), class = "data.frame", row.names = c(NA, 
-8L))