Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Transpose large data in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Transpose large data in UNIX
# 1  
Old 01-28-2017
Transpose large data in UNIX
Hi

I have the following sample of data: my full data dimention is 900,000* 1119

Code:
rs987435        C       G       1       1       1       0       2
rs345783        C       G       0       0       1       0       0
rs955894        G       T       1       1       2       2       1
rs6088791       A       G       1       2       0       0       1
rs11180435      C       T       1       0       1       1       1
rs17571465      A       T       1       2       2       2       2
rs17011450      C       T       2       2       2       2       2
rs6919430       A       C       2       1       2       2       2
rs2342723       C       T       0       2       0       0       0
rs11992567      C       T       2       2       2       2       2

I would like to transpose this data so that the data looks like the following
Code:
rs345783    rs955894    
C                  G
G                  T 
1                   0
1                   0
1                   1
0                   0
2                  0

and so one

I appreciate your help
Last edited by marwah; 01-28-2017 at 04:27 PM..
# 2  
Old 01-28-2017
I don't understand why the 1st row of your input isn't wanted as the first column of your output, why you only want to columns of output from ten rows of input, nor what you mean by "and so one". Are you trying to transpose pairs of rows or are you trying to transpose the entire table?

What operating system are you using? Different systems have different limitations that might cause you problems.

Have you searched this forum for ways to transpose data (for example, looking at the 1st three related threads listed at the bottom of this page)?

What have you tried to solve this problem on your own? Where are you getting stuck? (Is it just because of excessive line lengths on input lines that are over 1.8Mb long, or are there other problems?)
# 3  
Old 01-28-2017
Quote:
Originally Posted by Don Cragun
I don't understand why the 1st row of your input isn't wanted as the first column of your output, why you only want to columns of output from ten rows of input, nor what you mean by "and so one". Are you trying to transpose pairs of rows or are you trying to transpose the entire table?

What operating system are you using? Different systems have different limitations that might cause you problems.

Have you searched this forum for ways to transpose data (for example, looking at the 1st three related threads listed at the bottom of this page)?

What have you tried to solve this problem on your own? Where are you getting stuck? (Is it just because of excessive line lengths on input lines that are over 1.8Mb long, or are there other problems?)
Im sorry there was a typo in the columns , I want to transpose the data I just wrote two columns as an example for the output of course it will have more columns.

I'm using terminal in mac , I don't know how to do it that is why I don't have a code for it
# 4  
Old 01-28-2017
As I suggested before, please look at the 1st three threads under the heading below More UNIX and Linux Forum Topics You Might Find Helpful and see if the suggestions provided there helps you do what you're trying to do.

If none of the suggestions provided there help you do what you're trying to do, please show us what you have tried and explain to us what is not working when you use those suggestions.
# 5  
Old 01-28-2017
Hi,
An example with perl (But completely loads the file into memory) :
Code:
$ perl -e 'while (<>){my $i;map {$t_a[$i++] .="$_;";} split;};print map { s/;*$/\n/ ; $_} @t_a;' file
rs987435;rs345783;rs955894;rs6088791;rs11180435;rs17571465;rs17011450;rs6919430;rs2342723;rs11992567
C;C;G;A;C;A;C;A;C;C
G;G;T;G;T;T;T;C;T;T
1;0;1;1;1;1;2;2;0;2
1;0;1;2;0;2;2;1;2;2
1;1;2;0;1;2;2;2;0;2
0;0;2;0;1;2;2;2;0;2
2;0;1;1;1;2;2;2;0;2

Regards.
# 6  
Old 01-29-2017
Hi.

Numerous solutions in many languages/applications at:

parsing - An efficient way to transpose a file in Bash - Stack Overflow

How do I efficiently transpose a matrix in R? - Stack Overflow

Best wishes ... cheers, drl
# 7  
Old 01-29-2017
Here is a simple awk approach the I did not find in the suggested threads..:
Code:
awk '{for(i=1; i<=NF; i++) A[i]=A[i] (NR>1?OFS:x) $i} END{for(i=1; i<=NR; i++) print A[i]}' OFS=\;  file
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Transpose the data

Hi All, I have sort of a case to transpose data from rows to column input data Afghanistan|10000|1 Albania|25000|4 Algeria|25000|7 Andorra|10000|4 Angola|25000|47 Antigua and Barbuda|25000|23 Argentina|5000|3 Armenia|100000|12 Aruba|20000|2 Australia|50000|2 I need to transpose... (3 Replies)
Discussion started by: radius
3 Replies

2. UNIX for Advanced & Expert Users

Transpose Messy Data

I have a messy, pipe-delimited ("|") input dataset. I would like to create a file of ID plus each component of field 4 which is delimited by ";" into a long, skinny shape for easier processing. A couple of complications are that field 4 may contain both commas and linefeed characters from the... (9 Replies)
Discussion started by: 91674io
9 Replies

3. Shell Programming and Scripting

Help with transpose data content

Hi, Below is my input file: c116_g1_i1 -,-,-,+ c118_g2_i1 +,+ c118_g3_i1 + c120_g1_i1 +,+,+,+ . . Desired Output File c116_g1_i1 - c116_g1_i1 - c116_g1_i1 - c116_g1_i1 + c118_g2_i1 + c118_g2_i1 + (3 Replies)
Discussion started by: perl_beginner
3 Replies

4. Shell Programming and Scripting

Transpose data as rows using awk

Hi I have below requirement, need help One file contains the meta data information and other file would have the data, match the column from file1 and with file2 and extract corresponding column value and display in another file File1: CUSTTYPECD COSTCENTER FNAME LNAME SERVICELVL ... (1 Reply)
Discussion started by: ravlapo
1 Replies

5. Shell Programming and Scripting

Transpose Column of Data to Rows

I can no longer find my commands, but I use to be able to transpose data with common fields from a single column to rows using a command line. My data is separated as follows: NAME=BOB ADDRESS=COLORADO PET=CAT NAME=SUSAN ADDRESS=TEXAS PET=BIRD NAME=TOM ADDRESS=UTAH PET=DOG I would... (7 Replies)
Discussion started by: docdave78
7 Replies

6. Shell Programming and Scripting

storing large data in unix array variable

Hi, I have table in sql ..from this table im storing the first coloumn values in shell array variable ... after this passing this variable as an arugument in SQL procedure. But the proc. is running fine only for 1024 values in array ... How to store more than 1024 values in the array... (5 Replies)
Discussion started by: ankitknit
5 Replies

7. Shell Programming and Scripting

Transpose Data from Columns to rows

Hello. very new to shell scripting and would like to know if anyone could help me. I have data thats being pulled into a txt file and currently have to manually transpose the data which is taking a long time to do. here is what the data looks like. Server1 -- Date -- Other -- value... (7 Replies)
Discussion started by: Mikes88
7 Replies

8. Shell Programming and Scripting

How to transpose a table of data using awk

Hi. I have this data below:- v1 28 14 1.72414 1.72414 1.72414 1.72414 1.72414 v2 77 7 7.47126 6.89655 6.89655 6.89655 6.89655 v3 156 3 21.2644 21.2644 20.6897 21.2644 20.6897 v4 39 3 1.72414 1.72414 1.72414 1.72414 1.72414 v5 155 1 21.2644 23.5632 24.1379 23.5632 24.1379 v6 62 2 2.87356... (2 Replies)
Discussion started by: ahjiefreak
2 Replies

9. Shell Programming and Scripting

How to transpose data elements in awk

Hi, I have an input data file :- Test4599,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,2,2,Rain Test90,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,1,0,Not Rain etc.... I wanted to transpose these data to:-... (2 Replies)
Discussion started by: ahjiefreak
2 Replies

10. HP-UX

Need to split a large data file using a Unix script

Greetings all: I am still new to Unix environment and I need help with the following requirement. I have a large sequential file sorted on a field (say store#) that is being split into several smaller files, one for each store. That means if there are 500 stores, there will be 500 files. This... (1 Reply)
Discussion started by: SAIK
1 Replies
Login or Register to Ask a Question