How to transpose large amounts of datasets in excel
2
2
Entering edit mode
6.4 years ago
Vanceed ▴ 30

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.

bioinformatics Transpose excel datasets Forum • 15k views
ADD COMMENT
1
Entering edit mode

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.

ADD REPLY
0
Entering edit mode

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. 

ADD REPLY
9
Entering edit mode
6.4 years ago
Deepak Tanwar ★ 4.2k

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)
ADD COMMENT
0
Entering edit mode

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. 

ADD REPLY
0
Entering edit mode

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

ADD REPLY
1
Entering edit mode

You can do as:

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

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

ADD REPLY
0
Entering edit mode

thank you for a 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.

ADD REPLY
1
Entering edit mode

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

ADD REPLY
0
Entering edit mode

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)

ADD REPLY
5
Entering edit mode
6.4 years ago

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.

ADD COMMENT
0
Entering edit mode

Thanks for your response

ADD REPLY

Login before adding your answer.

Traffic: 2365 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6