-1

I am stuck in awk command to find and merge duplicate lines as one

here is how my file looks (I do not have header) and it is sorted on column 2 already:

1, abc, 123, , , , , , , , , ,
2, xyz, 123, , , , , , , , , ,
3, pqr, 123, , , , , , , , , ,
4, pqr, 123, , ,10, ,12, , , , ,
5, pqr, 123, , , , , , , ,1,2,
6, def, 123, , , , , , , , , ,
7, lmn, 123, , , , , , , , , ,
8, lmn, 123, , ,22, ,11, , , , ,
9, tuv, 123, , , , , , , , , ,
10, qrs, 123, , , , , , , , , ,

the output would be:

1, abc, 123, , , , , , , , , ,
2, xyz, 123, , , , , , , , , ,
3, pqr, 123, , ,10, ,12, , ,1,2,
6, def, 123, , , , , , , , , ,
7, lmn, 123, , , 22, 11, , , , , ,
9, tuv, 123, , , , , , , , , ,
10, qrs, 123, , , , , , , , , ,

Any help appreciated. Thanks in advance

  • It's much better to post the code you have, or what you have tried so far. I suspect that's why the downvote. (It's not my downvote.) – Wildcard Nov 26 '15 at 13:44
1

This could be done in a one liner, but it's tricky enough to deserve a full script:

#!/usr/bin/awk -f
# This shebang works on Mac; Linux boxes should use:
#!/bin/awk -f

BEGIN {
  FS = ", *";
  OFS = ", "
}

function printhold() {
  for (i=1; i<size; i++) {
    printf "%s", hold[i] OFS
  }
  print hold[size]
}

NR == 1 {
  size = split ($0, hold, ", *")
  next
}

hold[2] == $2 {
  for (i=4; i<=size; i++) {
    if (hold[i] == "") {
      hold[i] = $i
    }
  }
  next
}

{
  printhold()
  size = split ($0, hold, ", *")
} 

END {
  printhold()
}

What this does, at a high level, is:

  1. Hold a line in memory. (Don't print.)
  2. Look at the next line. If field 2 matches field 2 of the held line:
  3. For each field of the held line, if the field is blank, set it to the value of that field in the currently looked at line.
  4. Go to 2.
  5. When the next line's field 2 doesn't match the held field 2 (i.e. test in step 2 fails):
  6. Print the held line
  7. Replace the line held in memory with the next line (the one found not matching in 5 above).
  8. Go to 2.
  9. When the end of the file is reached, print the held line.

The key bit of code logic above (the part that actually merges lines) is:

hold[2] == $2 {
  for (i=4; i<=size; i++) {
    if (hold[i] == "") {
      hold[i] = $i
    }
  }
  next
}

This aligns to steps 2 through 4 of my high-level description.

  • Note that details haven't been provided in the question with regard to how to handle merge logic. For example, what to do if two lines with a matching field 2 BOTH have values in column 7, and the values don't match? My script defaults to the first value for column 7 in that case. Exercise for the Original Poster: Add a conditional test that will catch such a case, and exit the script with an error. Advanced exercise: Make it prompt the user to choose one of the values, rather than exiting. – Wildcard Nov 26 '15 at 13:49
  • Thanks Wildcard for code and very well explanation. Appreciated your quick and precise answer. You must be a great developer in Unix :) – user3749706 Nov 26 '15 at 14:10
  • No problem. :) If you found my answer helpful, you can mark it as "accepted" by clicking the checkmark on the left hand side. (See unix.stackexchange.com/help/someone-answers) – Wildcard Nov 26 '15 at 14:13
  • Done. You answer was very helpful. – user3749706 Nov 26 '15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.