Return the multiply of a column

user_11418708 0
Viewed 123

In the below, the data frame index denotes the value while t1:t2 denotes the number of times that specific value was recorded at a specific point in time. For example index 10 at t1 equals 1 suggesting that it was made 1 records; at t2 there are 4 records, whole at t3 and t4 just 1. I would like to return the values from columns t1:t4 based on an index column

Input:

index t1 t2 t3 t4
   10  1  4  1  1
   20  2  5  1  0
   30  3  6  1  0
   40  0  0  0  2 

Output:

       t1 t2  t3 t4
       10 10  10 10
       20 10  20 40
       20 10  30 40
       30 10  NA NA
       30 20  NA NA
       30 20  NA NA
       NA 20  NA NA
       NA 20  NA NA
       NA 30  NA NA  
       NA 30  NA NA
       NA 30  NA NA
       NA 30  NA NA
       NA 30  NA NA
       NA 30  NA NA

Sample data:

df<-structure(list(index=c (10,20,30,40), 
                   t1 = c(1, 2, 3, 0), 
                   t2 = c(4, 5, 6, 0), 
                   t3 = c(1, 1,1,  0),
                   t4 = c(1, 0, 0, 2)), row.names = c(NA,4L), class = "data.frame")
                                                            
df
dataframe r
4 Answers

One dplyr, tidyr and purrr solution could be:

map(.x = names(df)[-1],
    ~ df %>%
     uncount(get(.x)) %>%
     select(!!.x := index) %>%
     rowid_to_column()) %>%
 reduce(full_join)

   rowid t1 t2 t3 t4
1      1 10 10 10 10
2      2 20 10 20 40
3      3 20 10 30 40
4      4 30 10 NA NA
5      5 30 20 NA NA
6      6 30 20 NA NA
7      7 NA 20 NA NA
8      8 NA 20 NA NA
9      9 NA 20 NA NA
10    10 NA 30 NA NA
11    11 NA 30 NA NA
12    12 NA 30 NA NA
13    13 NA 30 NA NA
14    14 NA 30 NA NA
15    15 NA 30 NA NA
user_59645570

Base R and one line of code.

Map(function(x) rep(df$index, x), df[,-1])

After update:

maxy <- max(apply(df[,-1], 2, sum))
data.frame(Map(function(x) c(rep(df$index, x), rep(NA, maxy - sum(x))), df[,-1]))
user_54425270

Using base R with lapply

lst1 <- lapply(df[-1], function(x) rep(df$index, x))
data.frame(lapply(lst1, `length<-`, max(lengths(lst1))))

-output

#   t1 t2 t3 t4
#1  10 10 10 10
#2  20 10 20 40
#3  20 10 30 40
#4  30 10 NA NA
#5  30 20 NA NA
#6  30 20 NA NA
#7  NA 20 NA NA
#8  NA 20 NA NA
#9  NA 20 NA NA
#10 NA 30 NA NA
#11 NA 30 NA NA
#12 NA 30 NA NA
#13 NA 30 NA NA
#14 NA 30 NA NA
#15 NA 30 NA NA
user_37322710

Here is a base R option

list2DF(
  lapply(
    df[-1],
    function(x) `length<-`(rep(df$index, x), max(colSums(df[-1])))
  )
)

which gives

   t1 t2 t3 t4
1  10 10 10 10
2  20 10 20 40
3  20 10 30 40
4  30 10 NA NA
5  30 20 NA NA
6  30 20 NA NA
7  NA 20 NA NA
8  NA 20 NA NA
9  NA 20 NA NA
10 NA 30 NA NA
11 NA 30 NA NA
12 NA 30 NA NA
13 NA 30 NA NA
14 NA 30 NA NA
15 NA 30 NA NA
user_121587570
Related