Subset a dataframe into a list of dataframes in one go

Question

4 rows and 15 columns -> 12 rows and 5 columns. BUT IT IS NOT A SIMPLE TRANSPOSE.

I have tried the t(), transpose function. But that is not what I need.

DF1 <- as.data.frame( t( DF0))

Input:

> DF1
   V1  V2  V3  V4  V5  V6  V7  V8  V9 V10 V11 V12 V13 V14 V15
1 a01 b01 c01 d01 e01 f01 g01 h01 i01 j01 k01 l01 m01 n01 o01
2 a02 b02 c02 d02 e02 f02 g02 h02 i02 j02 k02 l02 m02 n02 o02
3 a03 b03 c03 d03 e03 f03 g03 h03 i03 j03 k03 l03 m03 n03 o03
4 a04 b04 c04 d04 e04 f04 g04 h04 i04 j04 k04 l04 m04 n04 o04

Expected:

> DF1
   V1  V2  V3  V4  V5
1 a01 b01 c01 d01 e01  
2 a02 b02 c02 d02 e02  
3 a03 b03 c03 d03 e03  
4 a04 b04 c04 d04 e04  
5 f01 g01 h01 i01 j01
6 f02 g02 h02 i02 j02
7 f03 g03 h03 i03 j03
8 f04 g04 h04 i04 j04
9 k01 l01 m01 n01 o01
10 k02 l02 m02 n02 o02
11 k03 l03 m03 n03 o03
12 k04 l04 m04 n04 o04

Actual:

> DF1
     V1  V2  V3  V4
V1  a01 a02 a03 a04
V2  b01 b02 b03 b04
V3  c01 c02 c03 c04
V4  d01 d02 d03 d04
V5  e01 e02 e03 e04
V6  f01 f02 f03 f04
V7  g01 g02 g03 g04
V8  h01 h02 h03 h04
V9  i01 i02 i03 i04
V10 j01 j02 j03 j04
V11 k01 k02 k03 k04
V12 l01 l02 l03 l04
V13 m01 m02 m03 m04
V14 n01 n02 n03 n04
V15 o01 o02 o03 o04

Csf · Accepted Answer · 2019-08-24 13:18:15Z

2

My simple way:

vn=c("V1","V2","V3","V4","V5")
x1=DF1[,6:10]
colnames(x1)=vn
x2=DF1[,11:15]
colnames(x2)=vn
x3=DF1[,1:5]
colnames(x3)=vn
df2=rbind(x3,x1,x2)
rownames(df2)=NULL

Sorry but I don't know how to show my own results. If somebody knows, edit my message.

answered Aug 24, 2019 at 13:18

Csf

234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VjSwamy Over a year ago

Hi, Thank you for your time and effort. But am trying to implement this on a very larger data set spanning 200k cols and 100s of rows. I do have a working sequence of commands, but it is a memory hog and takes more than 15 mins.. I am working on a newer approach based on the other two answers courtesy of magnanimous souls though..

VjSwamy Over a year ago

And I suppose you can cut and copy the results from the console and paste it within markdowns, the same way you posted the code.

G. Grothendieck · Accepted Answer · 2019-08-25 13:26:05Z

2

1) aperm Reshape DF1 into a 4 x 5 x 3 array, swap the last two dimensions and then reshape it back to a 12 x 5 matrix:

NC <- 5 # number of columns desired in result. Should be a divisor of nc

nr <- nrow(DF1) # 4
nc <- ncol(DF1) # 15

matrix(aperm(array(unlist(DF1), c(nr, NC, nc/NC)), c(1, 3:2)), ncol = NC)

giving this matrix:

      [,1]  [,2]  [,3]  [,4]  [,5] 
 [1,] "a01" "b01" "c01" "d01" "e01"
 [2,] "a02" "b02" "c02" "d02" "e02"
 [3,] "a03" "b03" "c03" "d03" "e03"
 [4,] "a04" "b04" "c04" "d04" "e04"
 [5,] "f01" "g01" "h01" "i01" "j01"
 [6,] "f02" "g02" "h02" "i02" "j02"
 [7,] "f03" "g03" "h03" "i03" "j03"
 [8,] "f04" "g04" "h04" "i04" "j04"
 [9,] "k01" "l01" "m01" "n01" "o01"
[10,] "k02" "l02" "m02" "n02" "o02"
[11,] "k03" "l03" "m03" "n03" "o03"
[12,] "k04" "l04" "m04" "n04" "o04"

2) by This also works. split t(DF1) by the indicated vector and transpose each element of the split rbinding them back together in the end. nc and NC are from above

do.call("rbind", by(t(DF1),  gl(nc/NC, NC), t))

3) split.default In this approach we split into every 5th column and then reshape that:

sapply(split.default(DF1, 1:NC), as.matrix)

3a) Also try this variation:

matrix(unlist(split.default(DF1, 1:NC)), ncol = NC)

4) permutation Another approach is to note that the output is a permutation of the input when the two are strung out as vectors so create that permutation vector, apply it and then reshape into a matrix.

ap <- c(aperm(array(1:(nr*nc), c(nr, NC, nc/NC)), c(1, 3:2)))
matrix(unlist(DF1)[ap], ncol = NC)

Note

This is DF1 in reproducible form:

Lines <- "   V1  V2  V3  V4  V5  V6  V7  V8  V9 V10 V11 V12 V13 V14 V15
1 a01 b01 c01 d01 e01 f01 g01 h01 i01 j01 k01 l01 m01 n01 o01
2 a02 b02 c02 d02 e02 f02 g02 h02 i02 j02 k02 l02 m02 n02 o02
3 a03 b03 c03 d03 e03 f03 g03 h03 i03 j03 k03 l03 m03 n03 o03
4 a04 b04 c04 d04 e04 f04 g04 h04 i04 j04 k04 l04 m04 n04 o04"
DF1 <- read.table(text = Lines)

edited Aug 25, 2019 at 13:26

answered Aug 24, 2019 at 12:43

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

5 Comments

VjSwamy Over a year ago

Hi, thanks for your reply. Is it possible to achieve the same result with a combination of Split() and Tibble()?

G. Grothendieck Over a year ago

See solution (2).

VjSwamy Over a year ago

Thank you Grothendieck. Working on it.

Ronak Shah Over a year ago

It is nice to have so many options but I would appreciate if you consider other people's answer when you edit your answer because they put in efforts equally for their answers. For example, here I already have an answer with split.default replacing unlist with as.matrix IMO doesn't make it as separate answer. Something similar here stackoverflow.com/questions/57328395/… where there was already a data.table answer and another with by. data.table answer is exactly same whereas in by you have changed nrow with length.

G. Grothendieck Over a year ago

Sorry if yours was first but I didn't notice it. I suspect you added it after you had already posted your main answer and it was hidden after the output of the first one in your answer. Also I was responding to the suggestion by the poster who specifically asked for split solutions and was focused on that. I have previously posted answers using the split.default idiom so I already use that in my answers. The version I posted is shorter.

Ronak Shah · Accepted Answer · 2019-08-24 13:13:23Z

1

Maybe you could try to select every 5th column. Using sapply, we could do

output <- as.data.frame(sapply(seq_len(ncol(df)/5), function(x) 
                        unlist(df[seq(x, ncol(df), by = 5)])))
rownames(output) <- NULL

output
#    V1  V2  V3  V4  V5
#1  a01 b01 c01 d01 e01
#2  a02 b02 c02 d02 e02
#3  a03 b03 c03 d03 e03
#4  a04 b04 c04 d04 e04
#5  f01 g01 h01 i01 j01
#6  f02 g02 h02 i02 j02
#7  f03 g03 h03 i03 j03
#8  f04 g04 h04 i04 j04
#9  k01 l01 m01 n01 o01
#10 k02 l02 m02 n02 o02
#11 k03 l03 m03 n03 o03
#12 k04 l04 m04 n04 o04

Or another option with split.default

sapply(split.default(df, rep(1:5, ncol(df)/5)), unlist)

edited Aug 24, 2019 at 13:13

answered Aug 24, 2019 at 12:37

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

2 Comments

VjSwamy Over a year ago

Hey, I appreciate your effort. Can I bother you for a little more help? Is it possible to achieve the same result with a combination of Split() and Tibble()?

Ronak Shah Over a year ago

@VjSwamy I added on option using split.default.

Collectives™ on Stack Overflow

Subset a dataframe into a list of dataframes in one go

3 Answers 3

2 Comments

Note

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Note

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related