Convert row data to column data


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert row data to column data
# 1  
Old 02-08-2010
Convert row data to column data

Hi Guys,

I have a file as follows:

Code:
a 1
b 786
c 90709
d 99
a 9875
b 989
c 887
d 111

I want:

Code:
a 1 9875
b 786 989
c 90709 887
d 99 111

So I want the row data to appear in columns for a,b,c and d. I have more than two sets of (a,b,c,d) data so I am looking for a general solution with "n" sets of (a,b,c,d) data. Also (a,b,c,d) is just an example and I want to use the script for (n) number of groups (not just 4 i.e. a,b,c,d).

I tried the paste command but this data is in the same file.

Thanks.
# 2  
Old 02-08-2010
Code:
awk '{a[$1]=a[$1]?a[$1] FS $NF:$0}END{for(i in a) print a[i]}' infile | sort



---------- Post updated at 09:26 PM ---------- Previous update was at 09:25 PM ----------

Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.
# 3  
Old 02-09-2010
a perl solution which you can extend easily for similar other cases too..


Code:
$ cat t.pl 
while(<>)  {
    # take the first word, use it as key and build a hash.
    ( $key, $value ) = /^(\w+)\s+(.*)$/;

    push @{$hash{$key}}, $value;
}

while ( ($key, $value) = each %hash )  {
    print "$key ";
    print "@$value";
    print "\n";
}

Code:
$ cat input-file 
a 1
b 786
c 90709
d 99
a 9875
b 989
c 887
d 111
$ perl t.pl input-file
c 90709 887
a 1 9875
b 786 989
d 99 111

# 4  
Old 02-09-2010
Thanks guys.

Can I use cut and paste?

For example can I cut the values for the second set of a,b,c,d numbers and paste them in the second column, the third set of a,b,c,d numbers and paste them in the third column and so on?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Column to Row Data.

HI Guys, I have below Input :- X L1 5 Y L1 10 Z L1 15 X L2 20 Y L2 12 Z L2 15 X L3 100 Y L3 Z L3 300 Output:- ID L1 L2 L3 X 5 10 15 Y 20 12 15 Z 100 Null 300 (11 Replies)
Discussion started by: pareshkp
11 Replies

2. Shell Programming and Scripting

Replace First Column and First Row Data

HI Guys, I just want to replace data for First Column and Row Cell(1,1) Input :- Hello A B C X 1 2 3 Y 4 5 6 Z 7 8 9 Output:- Byee A B C X 1 2 3 Y 4 5 6 Z 7 8 9 From Hello to Byee .....And The Each file have Different String. (3 Replies)
Discussion started by: pareshkp
3 Replies

3. Shell Programming and Scripting

Convert Data from Column to Row

Hi FileA.txt E_TIM 16, ETE 15, EOND 26, EEC 81, E_1 un, E_2 un, E_3 un, E_4 284, E_TIM 17, ETE 15, EOND 29, EEC 82, E_1 un, E_2 un, E_3 un, E_4 249, (6 Replies)
Discussion started by: asavaliya
6 Replies

4. UNIX for Advanced & Expert Users

Convert column data to row data using shell script

Hi, I want to convert a 3-column data to 3-row data using shell script. Any suggestion in this regard is highly appreciated. Thanks. (4 Replies)
Discussion started by: sktkpl
4 Replies

5. Shell Programming and Scripting

By using AWK can I convert matrice shaped data to a row ?

Hello, I have output in the matrice form , for example: 1 2 3 4 a b c d jim joe sue tom how can I convert this line-column data into a row as follows 1 2 3 4 a b c d jim joe sue tom thank you (14 Replies)
Discussion started by: rpf
14 Replies

6. Shell Programming and Scripting

Moving data from a specified column/row to another column/row

Hello, I have an input file like the following: 11_3_4 2_1_35 3_15__ _16989 Where '_' is a space. The data is in a table. Is there a way for the program to prompt the user for x1,y1 and x2,y2, where x1,y1 is the desired number (for example x=6 y=4 is a value of 4) and move to a desired spot... (2 Replies)
Discussion started by: jl487
2 Replies

7. Shell Programming and Scripting

row to column and position data in to fixed column width

Dear friends, Below is my program and current output. I wish to have 3 or 4 column output in order to accomodate in single page. i do have subsequent command to process after user enter the number. Program COUNT=1 for MYDIR in `ls /` do VOBS=${MYDIR} echo "${COUNT}. ${MYDIR}" ... (4 Replies)
Discussion started by: baluchen
4 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

How to insert data befor some field in a row of data depending up on values in row

Hi I need to do some thing like "find and insert before that " in a file which contains many records. This will be clear with the following example. The original data record should be some thing like this 60119827 RTMS_LOCATION_CDR INSTANT_POSITION_QUERY 1236574686123083rtmssrv7 ... (8 Replies)
Discussion started by: aemunathan
8 Replies

10. Shell Programming and Scripting

Move data from Column to Row

Hi all, I have a file with several columns of data, eg: A B C D 1 2 5 1 2 2 2 2 8 4 4 4 4 2 3 4 10 9 4 4 9 7 1 2 I need to get the values from, say, column B and place them into a string separated by a semicolon, eg: 2;2;4;2;9;7 Does... (4 Replies)
Discussion started by: danhodges99
4 Replies
Login or Register to Ask a Question