use awk to replace multiple columns'values based on some conditions

Question

I have a csv file

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot,PID.1,PID2,FNAME.1,MNAME.1,LNAME.1,FNAME2,MNAME2,LNAME2
,,,,,,201211.0,2,6,6,J,J,D,,
,,,,,,201211.0,3,4,6,H,H,M,,
,,,,,,201211.0,6,7,8,d,d,d,,
,,,,,,201211.0,0,2,5,6,7,8,,
,,,,,,201211.0,,,,,,,,
,,,,,,201211.0,,,,,,,,

What I want to do is use information in columns PID.1, FNAME.1,MNAME.1,LNAME.1 to fill Columns D,FNAME,MNAME,LNAME if there are null value and then output all columns to a new csv file. So my expected output is

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot,PID.1,PID2,FNAME.1,MNAME.1,LNAME.1,FNAME2,MNAME2,LNAME2
2,6,J,J,,,201211.0,2,6,6,J,J,D,,
3,6,H,H,,,201211.0,3,4,6,H,H,M,,
6,8,d,d,,,201211.0,6,7,8,d,d,d,,
0,5,6,7,,,201211.0,0,2,5,6,7,8,,
,,,,,,201211.0,,,,,,,,
,,,,,,201211.0,,,,,,,,

I tried to use awk to do it my self. Here is my code.

awk -F, '{if ($1=="" && $8!="")$1=$8;print $0}' test4.csv | awk -F, '{if ($2=="" && $10!="")$2=$10;print $0}' | awk -F, '{if ($3=="" && $11!="")$3=$11;print $0}' | awk -F, '{if ($4=="" && $12!="")$4=$12;print $0}'

The output was

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot,PID.1,PID2,FNAME.1,MNAME.1,LNAME.1,FNAME2,MNAME2,LNAME2
2      201211.0 2 6 6 J J D  
3      201211.0 3 4 6 H H M  
6      201211.0 6 7 8 d d d  
0      201211.0 0 2 5 6 7 8  
,,,,,,201211.0,,,,,,,,
,,,,,,201211.0,,,,,,,,

So I did not make it. Is there anyone who can help me? Thank you.

Andrei · Accepted Answer · 2019-10-03 21:56:28Z

2

$ awk -F',' -vOFS=',' '($1=="" && $8!=""){$1=$8} ($2=="" && $10!=""){$2=$10} ($3=="" && $11!=""){$3=$11} ($4=="" && $12!=""){$4=$12} {print $0}' test4.csv

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot,PID.1,PID2,FNAME.1,MNAME.1,LNAME.1,FNAME2,MNAME2,LNAME2
2,6,J,J,,,201211.0,2,6,6,J,J,D,,
3,6,H,H,,,201211.0,3,4,6,H,H,M,,
6,8,d,d,,,201211.0,6,7,8,d,d,d,,
0,5,6,7,,,201211.0,0,2,5,6,7,8,,
,,,,,,201211.0,,,,,,,,
,,,,,,201211.0,,,,,,,,

edited Oct 3, 2019 at 21:56

answered Oct 3, 2019 at 21:47

Andrei

5203 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samson Over a year ago

Thank you. It works. Would you mind giving a brief explanation for -vOFS?

Andrei Over a year ago

in awk OFS is Output Field Separator Variable (by default it is a single space character); -v var=val - Assign the value val to the variable var, before execution of the program begins.

James Brown · Accepted Answer · 2019-10-04 09:22:17Z

Another which strives to be more generic:

awk '
BEGIN {
    FS=OFS=","                      # set delimiters
}
NR==1 {
    for(i=1;i<=NF;i++)              # store the header names to h hash with indexes
        h[$i]=i
}
NR>1 {                              # copy fields for all but the first record
    $h["D"]=$h["PID.1"]             # referencing with header names
    $h["FNAME"]=$h["FNAME.1"]       # misspelled header name leads to a catastrophy
    $h["MNAME"]=$h["MNAME.1"]       # if you cant spell, use ifs:
    if(h["LNAME"] && h["LNAME.1"])  # but now you have to spell correctly :D
        $h["LNAME"]=$h["LNAME.1"]    
}
1' file                             # output

Output:

D,FNAME,MNAME,LNAME,GENDER,DOB,snapshot,PID.1,PID2,FNAME.1,MNAME.1,LNAME.1,FNAME2,MNAME2,LNAME2
2,6,J,J,,,201211.0,2,6,6,J,J,D,,
3,6,H,H,,,201211.0,3,4,6,H,H,M,,
6,8,d,d,,,201211.0,6,7,8,d,d,d,,
0,5,6,7,,,201211.0,0,2,5,6,7,8,,
,,,,,,201211.0,,,,,,,,
,,,,,,201211.0,,,,,,,,

Collectives™ on Stack Overflow

use awk to replace multiple columns'values based on some conditions

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related