Home > Software engineering >  Transforming Aggregate Count Data into Individual Data in R
Transforming Aggregate Count Data into Individual Data in R

Time:11-25

I have an aggregate count database, and I would like to reshape it so that each row corresponds to one individual:

in this example, column C represents the aggregate count data

Data set 1

    [A] [B] [C]
[1]  A1  B1  4
[2]  A2  B2  2
[3]  A3  B3  1

I would like to transform it to: Data set 2

    [A] [B] 
[1]  A1  B1 
[2]  A1  B1
[2]  A1  B1  
[3]  A1  B1
[4]  A2  B2
[5]  A2  B2
[6]  A3  B3

I have looked into the reshape package in R and I am not sure if there is a function that does exactly this task. I appreciate any help!

CodePudding user response:

Using base R

as.data.frame(lapply(df[1:2], rep, df$C))
   A  B
1 A1 B1
2 A1 B1
3 A1 B1
4 A1 B1
5 A2 B2
6 A2 B2
7 A3 B3

Data

df = data.frame('A' = c('A1', 'A2', 'A3'), 'B' = c('B1', 'B2', 'B3'), 'C' = c(4,2,1))

CodePudding user response:

Here is another solution using base R:

inds <- rep(seq_len(nrow(df1)), df1$C)
df2 <- df1[inds,]

Change the last line to

df2 <- df1[inds, 1:2]

to drop column C.

This gives different row names than the answer by Nad Pat. Which to use is probably a matter of personal preference.

CodePudding user response:

With a data.table approach

library(data.table)

data1 <- data.table(A=LETTERS[1:4], B=letters[1:4], C=1:4)

data1[, lapply(.SD, rep, C)][, -c("C")]
  • Related