Transpose Data form Different form


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose Data form Different form
# 1  
Old 10-17-2012
MySQL Transpose Data form Different form

HI Guys,

I have data in File A.txt

Code:
RL03       RL03_A_1  RL03_B_1  RL03_C_1  
RL03       -119.8   -119.5     -119.5         
RL07       RL07_A_1  RL07_B_1  RL07_C_1  
RL07       -119.3    -119.5    -119.5         
RL15       RL15_A_1   RL15_C_1  
RL15       -120.5    -119.4         
RL16       RL16_A_1  RL16_B_1    
RL16       -119.4    -119.1

I want output Like Below :

Code:
RL03	RL03_A_1	-119.8
RL03	RL03_B_1	-119.5
RL03    RL03_C_1        -119.5 
RL07	RL07_A_1        -119.3
RL07	RL07_B_1	-119.5
RL07    RL07_C_1 	-119.5	
RL15	RL15_A_1	-120.5 
RL15	RL15_C_1	-119.4 
RL16 	RL16_A_1	-119.4
RL16 	RL16_B_1	-119.1

I know it's vary complex ....Thanks in advance Smilie

Last edited by asavaliya; 10-17-2012 at 09:42 PM..
# 2  
Old 10-18-2012
The below code should work with data like in your example:
Code:
$ cat A.txt
RL03    RL03_A_1        RL03_B_1        RL03_C_1
RL03    -119.8  -119.5  -119.5
RL07    RL07_A_1        RL07_B_1        RL07_C_1
RL07    -119.3  -119.5  -119.5
RL15    RL15_A_1        RL15_C_1
RL15    -120.5  -119.4
RL16    RL16_A_1        RL16_B_1
RL16    -119.4  -119.1

$ perl -ane 'BEGIN{$lc=0;} if ($lc==0) {@r=@F;$lc++;} else {for $i (1 .. $#r) {print "$F[0]\t$r[$i]\t$F[$i]\n";}$lc=0;@r=();}' A.txt
RL03    RL03_A_1        -119.8
RL03    RL03_B_1        -119.5
RL03    RL03_C_1        -119.5
RL07    RL07_A_1        -119.3
RL07    RL07_B_1        -119.5
RL07    RL07_C_1        -119.5
RL15    RL15_A_1        -120.5
RL15    RL15_C_1        -119.4
RL16    RL16_A_1        -119.4
RL16    RL16_B_1        -119.1

This User Gave Thanks to spacebar For This Post:
# 3  
Old 10-18-2012
also:
Code:
awk '{
   if ($1 in a) ao[ac++]=$1;
   a[$1]=$1;
   ++l[$1];
   cnt[$1]=NF - 1;
   for (i=2; i<=NF; i++) {
      arr[$1 (l[$1]) (i-1)]=$i;
   }
}
END {
  for (i=0; i<ac; i++) {
    for (j=1; j<=cnt[ao[i]]; j++) {
      printf("%s\t%s\t%s\n", ao[i], arr[ao[i] 1 j], arr[ao[i] 2 j]);
    }
  }
}' infile

This User Gave Thanks to rdrtx1 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Getting data in table form

Hi, I have a csv file from which i am fetching few columns as below: IFILE=/home/home1/Report1.csv OFILE=/home/home1/`date +"%m%d%y%H%M%S"`.dat if #Checks if file exists and readable then awk -F "," '(NR>4) {print $1,$6,$2,$3,$4,$5,$6}' ${IFILE} >> ${OFILE} fi cat $OFILE | mail... (7 Replies)
Discussion started by: Vivekit82
7 Replies

2. Shell Programming and Scripting

Remove x lines form top and y lines form bottom using AWK?

How to remove x lines form top and y lines form bottom. This works, but like awk only cat file | head -n-y | awk 'NR>(x-1)' so remove last 3 lines and 5 firstcat file | head -n-3 | awk 'NR>4' (5 Replies)
Discussion started by: Jotne
5 Replies

3. UNIX for Dummies Questions & Answers

Put data in tabular form..

Dear Friends, I have a file as under : +++ ME 12-06-13 18:16:20 A RED FEW AND ROW1 1MN FEL AS HI FI BV LR TS HR ES MR * 0 13296 0 120 1 15 KS RR 10 0 +++ ME 12-06-13 18:26:20 A RED FEW AND ROW2 1MN FEL AS... (2 Replies)
Discussion started by: vanand420
2 Replies

4. Shell Programming and Scripting

Reformatting data in matrix form

Hi, Some assistance with respect to the following problem will be very helpful. I want to reformat my dataset in the following manner for subsequent analysis. I have first column values (which repeat for each value of 2nd column) which are names, the second column specifies position ad the... (1 Reply)
Discussion started by: newbie83
1 Replies

5. UNIX and Linux Applications

Get the data in my required form using gawk

i had the data in the following form: Branch : 3379 As On : 31-JAN-2009 Page : 1 User Id : OPER1 Date & Time : 01-FEB-2009 04:02:37 ... (2 Replies)
Discussion started by: KANNI786
2 Replies

6. Shell Programming and Scripting

convert one form of xml data to other

I would like to convert one form of xml tag data to another <DescriptionList> <DescriptionExt language="en" shortDesc="ITALIAN SAUSAGE SUB" longDesc="" sizeDesc="" smallImage="Pictures\sub-italian-sausage.png" largeImage="" forceImageUpdate="yes" /> ... (1 Reply)
Discussion started by: saisus
1 Replies

7. UNIX for Dummies Questions & Answers

changing data into matrix form

Hi, I have a file whose structure is like this 7 7 1 2 3 4 5 1 3 4 8 6 1 4 5 6 0 2 6 8 3 8 2 5 7 8 0 5 7 9 4 1 3 8 0 2 2 3 5 6 8 basically first two row tell the number of rows and column but the data following them are not arranged in that format. now i want to create another... (1 Reply)
Discussion started by: g0600014
1 Replies

8. UNIX for Dummies Questions & Answers

data form

I am user Of linux 9.0 Operating System and I had data form unix Operating system and I am not abel to open it in linux 9.0 Please tell me how can i use unix data in linux . (3 Replies)
Discussion started by: harsh_guru
3 Replies

9. UNIX for Advanced & Expert Users

Changing Unix form to Microsoft Word form to be able to email it to someone.

Please someone I need information on how to change a Unix form/document into a microsoft word document in order to be emailed to another company. Please help ASAP. Thankyou :confused: (8 Replies)
Discussion started by: Cheraunm
8 Replies
Login or Register to Ask a Question

Featured Tech Videos