Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Transpose matrix, and rearrange columns common with another file
Top Forums UNIX for Dummies Questions & Answers Transpose matrix, and rearrange columns common with another file Post 302936774 by RudiC on Friday 27th of February 2015 02:35:57 PM
Old 02-27-2015
How come the C5 line is missing in your output sample? Try
Code:
awk     'NR==1          {for (i=1; i<=NF; i++) EXCL[$i]}
         FNR==NR        {HOLD[NR]=$0; MXROW=NR; next}

         !($1 in EXCL) &&
         !(FNR==1)      {next}

                        {for (i=1; i<=NF; i++) T[FNR,i]=$i
                         if (NF>MXCOL) MXCOL=NF
                         PRT[FNR]++
                         delete EXCL[$1]
                        }

         END            {for (i=1; i<=MXCOL; i++) {for (j=1; j<=NR; j++) if (PRT[j]) printf "%s\t", T[j,i]; print _}

                         n=split(HOLD[1], TMP); for (j=1; j<=n; j++) if (TMP[j] in EXCL) DEL[j]++
                         for (i=1; i<=MXROW; i++)
                                {n=split(HOLD[i], TMP)
                                 printf "%s\t", TMP[1]
                                 for (j=2; j<=n; j++) if (!(DEL[j])) printf "%s\t", TMP[j]
                                 print _
                                }

                        }
        ' FS="\t" file2 file1
Series  S2      S1      S3      S4
C1      NULL    1       45      5
C2      0       2       45.6    5
C3      NA      3       6       6.8
C4      1.2     4       6.7     5.6
C5      00.34   5
        S1      S2      S3      S4
M1      FOX     CAT     FOO     BAR1
M2      APP     OR      TOM     -1
M3      -       -       BASE    231

---------- Post updated at 20:35 ---------- Previous update was at 20:27 ----------

For the pipe after the row names:
Code:
awk     'NR==1          {for (i=1; i<=NF; i++) EXCL[$i]}
         FNR==NR        {HOLD[NR]=$0; MXROW=NR; next}

         !($1 in EXCL) &&
         !(FNR==1)      {next}

                        {for (i=1; i<=NF; i++) T[FNR,i]=$i
                         if (NF>MXCOL) MXCOL=NF
                         PRT[FNR]++
                         delete EXCL[$1]
                        }

         END            {for (i=1; i<=MXCOL; i++)       {printf "%s%s\t", T[1,i], SEP; SEP="|"
                                                         for (j=2; j<=FNR; j++) if (PRT[j]) printf "%s\t", T[j,i]
                                                         print _
                                                        }

                         n=split(HOLD[1], TMP); for (j=1; j<=n; j++) if (TMP[j] in EXCL) DEL[j]++
                         for (i=1; i<=MXROW; i++)
                                {n=split(HOLD[i], TMP)
                                 printf "%s\t", TMP[1]
                                 for (j=2; j<=n; j++) if (!(DEL[j])) printf "%s\t", TMP[j]
                                 print _
                                }

                        }
        ' FS="\t" file2 file1
Series  S2      S1      S3      S4
C1|     NULL    1       45      5
C2|     0       2       45.6    5
C3|     NA      3       6       6.8
C4|     1.2     4       6.7     5.6
C5|     00.34   5
        S1      S2      S3      S4
M1      FOX     CAT     FOO     BAR1
M2      APP     OR      TOM     -1
M3      -       -       BASE    231

This User Gave Thanks to RudiC For This Post:
abh.kumar
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help with a script to rearrange columns

I have a file that is semi-colon delimited and the column headers are always the same but the column number is totally random each time this file is generated. I don't have the skills to make a script for this so maybe someone can help. I would like to be able to take this file which has over... (11 Replies)
Discussion started by: n3al10
11 Replies

2. Shell Programming and Scripting

Rows to Columns - File Transpose

Hi I have an input file and I want to transpose it but I need to take care that if any field is missing for a record it should be popoulated with space for that field - using a shell script INFILE ---------- emp=1 sal=2 loc=abc emp=2 sal=21 sal=22 loc=xyz emp=5 loc=abc OUTFILE... (10 Replies)
Discussion started by: 46019
10 Replies

3. Shell Programming and Scripting

Extracting columns from a matrix and storing each column in a separate file

Hi All, I have a huge matrix file consisting some some millions rows and 6000 columns. The contents are just floating point numbers in the matrix. I want to extract each column (i.e. 6000 of them) and store each column in a separate file. For example, 1.dat will consist of elements from column... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

4. Shell Programming and Scripting

Transpose few columns alone in a Text file

Hi Friends, I am Stuck up with a problem on transposing Rows to Coloumns.. Though there are many threads on this my problem is little difficult.. I have a tab separated file like Below, computer selling_loc currency_type manufacturer_name salesweek-wk1 sales-wk2 ...wk-3 ..wk4 till... (7 Replies)
Discussion started by: heinz_holan
7 Replies

5. Shell Programming and Scripting

Matrix transpose

Hello to everyone! I'm pretty tired and I cannot concentrate properly, but I need some help!!! I have a matrix like the one in the attachment (matrice_prova) and I would like an output like this: L1 L1 0.0 L1 L2 2.1 L1 L3 3.1 L1 L4 3.2 .... How is it possible do that in Perl or Awk?? Any... (3 Replies)
Discussion started by: gabrysfe
3 Replies

6. Shell Programming and Scripting

Transpose whole file and specific columns

Hi, I have a file like this a b c d e f g h i j k l Case1: I want to transpose the whole file Output1 a d g j b e h k c f i l Case2 Transpose a specific column - Say 3rd (6 Replies)
Discussion started by: jacobs.smith
6 Replies

7. Shell Programming and Scripting

file merge based on common columns

I have two files 1.txt 34, ABC, 7, 8, 0.9 35, CDE, 6.5, -2, 0.01 2.txt 34, ABC, 9, 6, -1.9 35, CDE, 8.5, -2.3, 5.01 So in both files common columns are 1 and 2 so final o/p should look like 34, ABC, 7, 8, 0.9, 9, 6, -1.9 35, CDE, 6.5, -2, 0.01, 8.5, -2.3, 5.01 I tried using... (3 Replies)
Discussion started by: manas_ranjan
3 Replies

8. Shell Programming and Scripting

how to rearrange a matrix with awk

Hi, every one. I have two files ,one is in matrix like this, one is a list with the same data as the matrix. AB AE AC AD AA AF SA 3 4 5 6 4 6 SC 5 7 2 8 4 3 SD 4 6 5 3 8 3 SE 45 ... (5 Replies)
Discussion started by: xshang
5 Replies

9. Shell Programming and Scripting

To transpose columns + edit in a txt file

Hi, I have a txt file that looks like log2FoldChange Ontology_term 8.50624450251828 GO:0003700,GO:0003707,GO:0005634,GO:0006355,GO:0043401,GO:0003700,GO:0005634,GO:0006355,GO:0008270,GO:0043565 7.03936870356684 GO:0005515,GO:0008080 6.49606183738682 6.49525073909629 GO:0005515... (4 Replies)
Discussion started by: alisrpp
4 Replies

10. Shell Programming and Scripting

Transpose matrix based on second column using awk

Hi, Is it possible to transpose the matrix like this using awk ? Many thanks in advance Input abc Name_1 0 abc Name_2 1 abc Name_3 2 abc Name_4 0.4 def Name_1 0 def Name_2 9 def Name_3 78 def Name_4 1 Output abc def Name_1 0 ... (4 Replies)
Discussion started by: quincyjones
4 Replies
All times are GMT -4. The time now is 02:58 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy