Transpose data at particular row w python,r,sql?

Question

Lets say I've got following data (about 50k of rows in real example)

A
B
C
D
E
X
A
B
C
D
E
F
G
H
X

And I want it to look

A,B,C,D,E,X

A,B,C,D,E,F,G,H,X

So technically I want to transpose data, but cut at particular row.

How can this be achieved in Excel, R, SQL or python?

Answer 1

Using R here are a few possible answers with slight differences since I'm not sure exactly what you want

# Just a step to read in an extended version of your sample data

dat <- as.matrix(read.table(text=
"A
B
C
D
E
A
B
C
D
E
F
A
B
C
D
E
F
G
H
A
B
C
D
E
F"))

This is one way you could do the splitting. You could make an index by which to split the groups. Then split the lines based on the groupings.

splitgrp <- cumsum(ave(dat=="A", dat)) # group index
splitlist <-split(dat,splitgrp) # if you want a list

You can then make that list into different kinds of objects if you want, like so:

vecofstrings <- sapply(splitlist,paste0,collapse="") # if you want a vector
df <- data.frame(vecofstrings) # if you want a data frame
mat <- matrix(vecofstrings) # if you want a matrix

Finally, here are a few ways to save those objects:

write.table(mat,"mat.csv")
write.table(mat,"mat.csv", quote=F, row.names=F)

# Here are a few ways to save a data frame.
write.table(df,"df.txt")
write.table(df,"df.txt", quote=F)  # no quotes in the saved file
write.table(df,"df.txt", row.names=F)  # no row names in the saved file
write.table(df,"df.txt", row.names=F, col.names=F)  # no row or column names in the saved file
write.table(df,"df.txt",row.names=F, col.names=F, quote=F)  # no row or columns names and no quotes in the saved file