Merging rows in awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging rows in awk
# 1  
Old 01-15-2013
Merging rows in awk

Hello,

I have a data format as follows:

Code:
Ind1 0 1 2
Ind1 0 2 1
Ind2 1 1 0 
Ind2 2 2 0

I want to use AWK to have this output:

Code:
Ind1 00 12 21
Ind2 12 12 00

That is to merge each two rows with the same row names.

Thank you very much in advance for your help.
# 2  
Old 01-15-2013
What about searching this forum and trying it yourself? This type of request is abundant here!
# 3  
Old 01-15-2013
Indeed, but my real file has about 1000 rows and more than 400, 000 columns, I have found this:
Code:
{
    col2[$1] = col2[$1] $2
    col3[$1] = col3[$1] $3
    col4[$1] = col4[$1] $4
}

END {

    for ( i in col2 )
    {
        print i " " col2[i] " " col3[i] " " col4[i]
    }
}

but I need to extend the loop, such as

Code:
i in i<=NF

I need some help for that, thank you very much.
# 4  
Old 01-15-2013
Try:
Code:
awk '
{       if(NF > m) m = NF
        if(!((1,$1) in o)) {
                o[1,$1] = $1
                oo[++oc] = $1
        }
        for(i = 2; i <= NF; i++) o[i,$1] = o[i,$1] $i
}
END {   for(i = 1; i <= oc; i++) {
                for(j = 1; j <= m; j++)
                        printf("%s%s", o[j, oo[i]], j == m ? "\n" : " ")
        }
}' input

On Solaris systems, use /usr/xpg4/bin/awk or nawk, instead of awk.
# 5  
Old 01-15-2013
Standard awk only supports 100 fields per line so you're going to need gawk or mawk.

If performance is an issue and lines to be merged are always adjacent, the following should use a lot less resources:

Code:
awk '
function pl() {
    printf "%s",a;
    for(i=2;i in V;i++) printf " %s",V[i]
    printf "\n"
}
a!=$1 {
    if(a)pl()
    a=$1
    split("",V,",")
}
{
   for(i=2;i<=NF;i++) V[i]=V[i]$i
   next
}
END {pl()}' infile


Last edited by Chubler_XL; 01-15-2013 at 07:54 PM.. Reason: Formatting
# 6  
Old 01-16-2013
try also:
Code:
awk -f '{
  a[$1]=$1; for (i=2; i<=NF; i++) {ar[$1,i]=ar[$1,i] $i; if (NF>mx) mx=NF}
}
END {
  for (b in a) {
    printf b " ";
    for (c=2; c<=mx; c++) printf ar[b,c] " ";
    print "";
  }
}' infile

# 7  
Old 01-16-2013
Is it always two adjacent rows? Do they always have the same field count? Try this:
Code:
$ awk '{getline nx; j=split (nx, Ax); for (i=2;i<=j;i++) $i=$i Ax[i]}1' file

or, even shorter:
Code:
$ awk '{getline nx; for (i=2;i<=split(nx,Ax);i++) $i=$i Ax[i]}1' file
Ind1 00 12 21
Ind2 12 12 00

Don't know if it will work for 400000 columns, though ...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merging two lines into one (awk)

Hi, I am attempting to merge the following lines which run over two lines using awk. INITIAL OUTPUT 2019 Sep 28 10:47:24.695 hkaet9612 last message repeated 1 time 2019 Sep 28 10:47:24.695 hkaet9612 %ETHPORT-5-IF_DOWN_INTERFACE_REMOVED: Interfa ce Ethernet1/45 is down (Interface removed)... (10 Replies)
Discussion started by: sand1234
10 Replies

2. UNIX for Beginners Questions & Answers

Merging rows based on same ID in First column.

Hellow, I have a tab-delimited file with 3 columns : BINPACKER.13259.1.p2 SSF48239 BINPACKER.13259.1.p2 PF13243 BINPACKER.13259.1.p2 G3DSA:1.50.10.20 BINPACKER.13259.2.p2 SSF48239 BINPACKER.13259.2.p2 PF13243 BINPACKER.13259.2.p2 G3DSA:1.50.10.20... (7 Replies)
Discussion started by: anjaliANJALI
7 Replies

3. Shell Programming and Scripting

Merging rows after matching a pattern

Hi All, I have the below file where I want the lines to merged based on a pattern. AFTER CMMILAOJ CMMILAAJ AFTER CMDROPEJ CMMIMVIJ CMMIRNTJ CMMIRNRJ CMMIRNWJ CMMIRNAJ CMMIRNDJ AFTER CMMIRNTJ CMMIRNRJ CMMIRNWJ (4 Replies)
Discussion started by: varun22486
4 Replies

4. Shell Programming and Scripting

Merging rows using two common rows.

Hi.. My requirement is simple but unable to get that.. File 1 : 3 415 A G 4 421 G . 39 421 G A 2 421 G A,C 41 427 A . 4 427 A C 42 436 G . 3 436 G C 43 445 C . 2 445 C T 41 447 A . Output (4 Replies)
Discussion started by: empyrean
4 Replies

5. UNIX Desktop Questions & Answers

merging files and add missing rows

hello all, I have files that have a specific way for naming the first column they are make of five names in Pattern of 3 Y = (no case sensitive) so the files are names $Y-$Y-$Y or $X-$Y-$Z depending how we look they only exist of the pattern exist now I want to create a file from them that... (9 Replies)
Discussion started by: A-V
9 Replies

6. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

7. Shell Programming and Scripting

Merging rows with same column 1 value

I have the following space-delimited input: 1 11.785710 117.857100 1 15 150 1 20 200 1 25 250 3 2.142855 21.428550 3 25 250 22 1.071435 10.714350 The first field is the ID number, the second field is the percentage of the total points that the person has and the third column is the number... (3 Replies)
Discussion started by: mdlloyd7
3 Replies

8. Shell Programming and Scripting

merging files using awk

Hi, I have 2 files. File 1 chr1 1234 2468 ABC chr1 3456 4567 DEF chr2 5643 6154 XYZ : : : : so on.... File 2 chr1 1500 2500 positive chr1 2500 3500 negative chr1 3000 4500 neutral (10 Replies)
Discussion started by: Diya123
10 Replies

9. Shell Programming and Scripting

Merging together two awk scripts

I have two awk scripts shown below. checkTrvt.awk works on file format .xt, whereas checkData.awk workds on file format .dat I want to merge the two scripts together, if I find that the user passed .xt file I do the code for .xt file, whereas if user passes .dat file, I go through the code for... (9 Replies)
Discussion started by: kristinu
9 Replies

10. Shell Programming and Scripting

Merging of rows

Hi guys, Wish you all a very Happy New Year!!!. Thanks in advance. I want to read a file and merge the rows which have '\n' in it. The rows could be > 50,000 bytes. The script should merge all the rows till the next row starts with word 'Type|'. ex.... (24 Replies)
Discussion started by: ssachins
24 Replies
Login or Register to Ask a Question