How to transpose large amounts of datasets in excel

Question

[Vanceed]

Hey everyone,

I am trying to transpose large datasets in excel but it keeps giving me the message we can't paste because copy area and paste area aren't the same size. Is there a way to transpose all the data at one time instead of piece by piece? One dataset has a great amount of rows and columns. Thanks.

[Neilfws]

Expect answers which recommend you not use Excel. They are correct :)

But if you must, the correct option is "Paste Special", then check "transpose". It sounds like you are trying to paste into the same area from which you copied, hence the error message. Try pasting to a new area or worksheet.

[Vanceed]

Thanks for your response. I was told to use excel to first organize all the data then once ready to analyze I would be using software such as R and banjo but I am just using excel to post data on a forum with one of my committee members for a group of our PhD cohort to see.

Answer 1

[Deepak Tanwar]

Excel is not for Bioinformatics.

Use R to transpose the data matrix.

data <- read.table("your_file_location", sep = "\t", comment.char = "", stringAsFactors = F, header = T)

transpose_data <- t(data)

[Vanceed]

Thanks for your response. I am using excel to post transposed data onto a forum for my biostatistician to look at before running the analysis. I am just organizing the data first.

[Edalat]

dear Deepak how to change in the structure and the data types of the data frame after the transposition?

[Deepak Tanwar]

You can do as:

transpose <- data.frame(t(data))

I am not sure what are you trying to convert (list to dataframe?)

[Edalat]

Thank you for your reply.

I want rows become columns and columns become rows, but after transposition the frame of result is not normal. They are in a long row, is it normal?

Unfortunately it is not possible I sent pic of my result here.

[Deepak Tanwar]

Can you please paste your code and a snap of your output here?

[Edalat]

Excuse me, can I have your email and sent you screenshot? I do not know how I can send it here the output. the R function is:

data <- read.table("coding.csv", sep ="\t", comment.char = "", stringsAsFactors = FALSE, header = TRUE)
transpose_data <- t(data)

Answer 2

[Alex Reynolds]

Stack Overflow offers a solution that uses GNU awk and should run considerably faster than R (if you plan on doing a lot of transposing).

Also something to keep in mind is that Excel makes Microsoft-y text files. You may need a tool like dos2unix to convert them to something that works better with command-line tools.

[Vanceed]

Thanks for your response