0

I have an excel with multiple columns (20 in total) with each column containing multiple rows (5000 each). I want to replicate each cell from one row and all the columns 3 times and add them one below the other. Then I want to do the same with the next row and so on until I have one output column with all the cells (across columns) replicated 3 times and appended to each other.

The input data (sample)

v1 v2 v3
1  3  5 
2  4  6

and so on.

The output data (sample)

Output
1
1
1
3
3
3
5
5
5
2
2
2
4
4
4
6
6
6

I found a similar question here, which suggested using rep function. But the difference is that while the entire column in being replicated there, I need to replicate each cell from each column. Please suggest me some edits to make it work.

Thanks.

Looping through Columns replicating each column fetched six times

Ray
  • 56
  • 1
  • 7

1 Answers1

0

Instead of unlisting, you can transpose here :

data.frame(x = rep(t(df), each = 3))

#   x
#1  1
#2  1
#3  1
#4  3
#5  3
#6  3
#7  5
#8  5
#9  5
#10 2
#11 2
#12 2
#13 4
#14 4
#15 4
#16 6
#17 6
#18 6
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Just an extra query: Is there a way to replicate different column cells different number of times? Like cells in column 1 5 times, in column 2 4 times and so on. Rest of the question remains same. And also, I want to preserve NA values (i.e. they need to be repeated too) – Ray Jul 14 '20 at 04:33
  • I came across ```freq``` function but I am not able to apply it cleanly. Also, ```each``` function seems to treat ```NA``` values as 1 (and doesn't repeat it?) – Ray Jul 14 '20 at 04:45
  • Perhaps, this will help https://stackoverflow.com/questions/2894775/repeat-each-row-of-data-frame-the-number-of-times-specified-in-a-column – Ronak Shah Jul 14 '20 at 05:19
  • Thank you for reaching out. But I tried this one before. There's one issue. I don't have a column named ```freq```. I created a vector named ```repeatations```. What I wanted to do was repeat the first row, first column 59 times; first row, second column 55 times; first row, third column 60 times and so on. And then repeat the same process for every row, creating a one column output. ```repeatations = c(59, 55, 60, 74, 118, 34, 49)``` – Ray Jul 14 '20 at 09:20
  • Yes, so you can do `df[rep(1:nrow(df), repeatations), ]` – Ronak Shah Jul 14 '20 at 10:24
  • I did that. It is throwing an error. Code: ```smp.expand <- smp.df[rep(1:nrow(smp.df), repeatations), ]``` Error: ```Error in rep(1:nrow(smp.df), repeatations) : invalid 'times' argument``` – Ray Jul 14 '20 at 10:42
  • Is it due to ```NA``` values? Because there is quite a few of them there. – Ray Jul 14 '20 at 10:45
  • I think the problem is that since ```repeatations``` has 7 elements in it, it is not matching with the length of the first argument i.e. ```1:nrow(smp.df)```. If that's the case, what can be the solution? (I am just guessing that's the problem based on my limited understanding) – Ray Jul 14 '20 at 11:03
  • I think it will be better to ask this as a new question with all the details in it. – Ronak Shah Jul 14 '20 at 11:28