Merging multiple lines to columns with awk, while inserting commas for missing lines

09-02-2015

Moderator

3,105, 1,603

Join Date: May 2013

Last Activity: 31 August 2020, 1:46 AM EDT

Location: Chennai

Posts: 3,105

Thanks Given: 1,269

Thanked 1,603 Times in 1,369 Posts

Hello RalphNY,

If order doesn't matter for you, you can try as follows.

Code:

awk -F, '{A[$1 FS $2]=$1 FS $2;B[$1 FS $2]=$(NF-2) FS $(NF-1) FS $NF;C[$1 FS $2 FS $3]=$4;;A[$1 FS $2]=A[$1 FS $2] FS C[$1 FS $2 FS "WE"] FS C[$1 FS $2 FS "MO"] FS C[$1 FS $2 FS "CE"] FS C[$1 FS $2 FS "ES"] FS B[$1 FS $2]} END{for(i in A){print A[i]}}'  Input_file

Output will be as follows.

Code:

3333,Hortense Gable,,,25,14,Three Pinapple Ln,Somewhere,NY
1111,John Smith,105,23,,,Fourty Two Cherry Lane,Nowhere,NY
2222,Elane Boozler,70,24,30,8,Twelve Peach Court,Elsehere,NY

If you need to get output in sorting order then following may help you.

Code:

awk -F, '{A[$1 FS $2]=$1 FS $2;B[$1 FS $2]=$(NF-2) FS $(NF-1) FS $NF;C[$1 FS $2 FS $3]=$4;;A[$1 FS $2]=A[$1 FS $2] FS C[$1 FS $2 FS "WE"] FS C[$1 FS $2 FS "MO"] FS C[$1 FS $2 FS "CE"] FS C[$1 FS $2 FS "ES"] FS B[$1 FS $2]} END{for(i in A){print A[i]}}' Input_file | sort -k1

Output will be as follows.

Code:

1111,John Smith,105,23,,,Fourty Two Cherry Lane,Nowhere,NY
2222,Elane Boozler,70,24,30,8,Twelve Peach Court,Elsehere,NY
3333,Hortense Gable,,,25,14,Three Pinapple Ln,Somewhere,NY

EDIT: Adding a non one-liner for same solution now.

Code:

awk -F, '{
                A[$1 FS $2]=$1 FS $2
                B[$1 FS $2]=$(NF-2) FS $(NF-1) FS $NF;
                C[$1 FS $2 FS $3]=$4;
                A[$1 FS $2]=A[$1 FS $2] FS C[$1 FS $2 FS "WE"] FS C[$1 FS $2 FS "MO"] FS C[$1 FS $2 FS "CE"] FS C[$1 FS $2 FS "ES"] FS B[$1 FS $2]
         }
                END{
                        for(i in A){
                                        print A[i]
                                   }
                   }
        ' Input_file | sort -k1

Thanks,
R. Singh

Last edited by RavinderSingh13; 09-02-2015 at 03:17 AM.. Reason: Adding a non one-liner form for solution now. Removed a not used array named D in solution now.

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

09-02-2015

Registered User

4, 0

Join Date: Aug 2015

Last Activity: 14 September 2015, 4:25 PM EDT

Posts: 4

Thanks Given: 2

Thanked 0 Times in 0 Posts

Don,
Thanks for pointing those things out! I hadn't noticed the latest version was matching the name instead of the ID. In the real file, there definitely would be some people with identical names, so it would have to match the ID.

Just changing the $8 to $7, to match the ID rather than the name seems to have worked. Could you tell me if there is anything wrong with the code below?

Code:

awk -F, '
NR != 1 && p1 != $7 {
    print prev
    delete TMP
}
{
    p1 = $7
    TMP[$9] = $10
    prev = $2 FS $4 FS $7 FS $8 FS TMP["WE"] FS TMP["MO"] FS TMP["CE"] FS TMP["ES"] FS $11 FS $13
}
END {
    print prev
}' OFS="," file

I'm using cygwin awk / gnu awk. I don't see any errors when running with delete TMP, but that should be compatible with gnu awk, right? Thanks again : )

RalphNY

View Public Profile for RalphNY

Find all posts by RalphNY

09-02-2015

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

Quote:

Originally Posted by RalphNY

Code:

awk -F, '
NR != 1 && p1 != $7 {
    print prev
    delete TMP
}
{
    p1 = $7
    TMP[$9] = $10
    prev = $2 FS $4 FS $7 FS $8 FS TMP["WE"] FS TMP["MO"] FS TMP["CE"] FS TMP["ES"] FS $11 FS $13
}
END {
    print prev
}' OFS="," file

I'm using cygwin awk / gnu awk. I don't see any errors when running with delete TMP, but that should be compatible with gnu awk, right? Thanks again : )

I'm glad to have helped.

As I said before, the above code should be fine as long as it doesn't print any diagnostics (and GNU awk is one of many that have the extension that accepts delete array_name in addition to the delete array_element that is required by the standards). Just keep the delete element loop in the back of your mind if you ever need to port your code to a system that doesn't support that extension.

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

Shell Programming and Scripting

Merging multiple lines to columns with awk, while inserting commas for missing lines

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merging two lines into one (awk)

Discussion started by: sand1234

2. UNIX for Beginners Questions & Answers

Merging multiple lines into single line based on one column

Discussion started by: raju2016

3. Shell Programming and Scripting

Merging multiple lines

Discussion started by: Kanja

4. Shell Programming and Scripting

Merging multiple files using lines from one file

Discussion started by: iconig

5. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Discussion started by: jacobs.smith

6. Shell Programming and Scripting

merging multiple lines into single line

Discussion started by: bala123

7. Shell Programming and Scripting

inserting and replacing lines with awk

Discussion started by: tempestas

8. Shell Programming and Scripting

inserting multiple lines with awk

Discussion started by: sunrexstar

9. Shell Programming and Scripting

Matching lines across multiple csv files and merging a particular field

Discussion started by: Demosthenes

10. Shell Programming and Scripting

Merging non-repeating columns of lines

Discussion started by: menenuh