awk to merge lines based on column

user_3214143 0
Viewed 620

Would like to merge the lines based on first column $1 into row and formatted the output. While printing the header need to generate Max Unique count of first field. For example, Angola appears count=3 , Brazil appears count=5 , Zambia appears count=1 . Max unique count of field $1 is 5 , so need to print the header 5 times to have a proper header for all the fields.

While print the output, want to keep the original input file line Orders. My actual input files used to be vary like 10 fields, 12 fields etc.

Input.csv

Country,Network,Details,Amount
Angola,voda,xxx,10
Angola,at&t,xxx,20
Angola,mtn,xxx,30
Brazil,voda,yyy,40
Brazil,voda,yyy,50
Brazil,at&t,yyy,60
Brazil,mtn,yyy,70
Brazil,voda,yyy,80
Zambia,tcl,zzz,90

Desired Output.csv

Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount
Angola,voda,xxx,10,Angola,at&t,xxx,20,Angola,mtn,xxx,30
Brazil,voda,yyy,40,Brazil,voda,yyy,50,Brazil,at&t,yyy,60,Brazil,mtn,yyy,70,Brazil,voda,yyy,80
Zambia,tcl,zzz,90

Presently , I am using the below two commands to get the desired output and changing the count manually each time based on number of fields in the actual input file.

Step:#1

awk 'BEGIN { while (count++<5) header=header "Country,Network,Details,Amount,"; print header }' > output.csv

Step:#2 

awk -F, '
    /.+/{
        if (!($1 in Val)) { Key[++i] = $1; }
        Val[$1] = Val[$1]  $0 ",";
    }
    END{
        for (j = 1; j <= i; j++) {
            print(Val[Key[j]] );
        }
    }' input.csv >> output.csv

Looking for your suggestions ...

awk
1 Answers

awk One-liner :

awk 'BEGIN{FS=OFS=","}FNR==1{n=$0;next}{a[$1]=($1 in a ? a[$1] OFS:"")$0; if(!($1 in b)){o[++i]=$1}; b[$1]++; mx=mx>b[$1]?mx:b[$1] }END{for(i=1; i<=mx; i++)printf("%s%s",n,i==mx?RS:OFS); for(i=1; i in o; i++)print a[o[i]]}' infile

Input:

$ cat infile
Country,Network,Details,Amount
Angola,voda,xxx,10
Angola,at&t,xxx,20
Angola,mtn,xxx,30
Brazil,voda,yyy,40
Brazil,voda,yyy,50
Brazil,at&t,yyy,60
Brazil,mtn,yyy,70
Brazil,voda,yyy,80
Zambia,tcl,zzz,90

Output:

$ awk 'BEGIN{FS=OFS=","}FNR==1{n=$0;next}{a[$1]=($1 in a ? a[$1] OFS:"")$0; if(!($1 in b)){o[++i]=$1}; b[$1]++; mx=mx>b[$1]?mx:b[$1] }END{for(i=1; i<=mx; i++)printf("%s%s",n,i==mx?RS:OFS); for(i=1; i in o; i++)print a[o[i]]}' infile
Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount,Country,Network,Details,Amount
Angola,voda,xxx,10,Angola,at&t,xxx,20,Angola,mtn,xxx,30
Brazil,voda,yyy,40,Brazil,voda,yyy,50,Brazil,at&t,yyy,60,Brazil,mtn,yyy,70,Brazil,voda,yyy,80
Zambia,tcl,zzz,90

Better Readable:

awk 'BEGIN{
            FS=OFS=","
     }
     FNR==1{
            n=$0;
            next
     }
     {
           a[$1]=($1 in a ? a[$1] OFS:"")$0;
           if(!($1 in b)){ o[++i]=$1 }; 
           b[$1]++; 
           mx=mx>b[$1]?mx:b[$1] 
     }
    END{
           for(i=1; i<=mx; i++)
               printf("%s%s",n,i==mx?RS:OFS); 

            for(i=1; i in o; i++)
                print a[o[i]]
     }' infile

For comment :

Would like to know, where to change the code, to print "Country" in the Output only first time , if I don't need to print the same country name 2nd time , third time

$ awk 'BEGIN{FS=OFS=","}FNR==1{n=$0;next}{a[$1]=($1 in a ? a[$1] OFS substr($0,index($0,",")+1) : $0); if(!($1 in b)){o[++i]=$1}; b[$1]++; mx=mx>b[$1]?mx:b[$1] }END{for(i=1; i<=mx; i++)printf("%s%s",i==1?n:substr(n,index(n,",")+1),i==mx?RS:OFS); for(i=1; i in o; i++)print a[o[i]]}' infile
Country,Network,Details,Amount,Network,Details,Amount,Network,Details,Amount,Network,Details,Amount,Network,Details,Amount
Angola,voda,xxx,10,at&t,xxx,20,mtn,xxx,30
Brazil,voda,yyy,40,voda,yyy,50,at&t,yyy,60,mtn,yyy,70,voda,yyy,80
Zambia,tcl,zzz,90

Modified-code:

awk 'BEGIN{
            FS=OFS=","
     }
     FNR==1{
            n=$0;
            next
     }
     {
           # this line modified
           # look for char pos of comma, 

           a[$1]=($1 in a ? a[$1] OFS substr($0,index($0,",")+1) : $0);

           if(!($1 in b)){ o[++i]=$1 }; 

           b[$1]++; 
           mx=mx>b[$1]?mx:b[$1] 
     }
    END{
           for(i=1; i<=mx; i++)
              # this line modified
              printf("%s%s",i==1?n:substr(n,index(n,",")+1),i==mx?RS:OFS);

            for(i=1; i in o; i++)
                print a[o[i]]
     }' infile

Explanation related to modification:

  • index(in, find)

Search the string in for the first occurrence of the string find, and return the position in characters where that occurrence begins in the string in.

  • substr(string, start [, length ])

    Return a length-character-long substring of string, starting at character number start.

user_31619930
Related