3

I have an file whose fields are ID, Designation, ParentID, and ParentDesignation. The file content is the following.

A1  M.D-Sales    0    UmbrellaCorp
a1  Sr.Sales    A1
b1  Sr.R&D      B1
b2  Jr.SR&D     B1
a2  Jr.Sales    A1
B1  M.D-R&D      0    UmbrellaCorp

I want to get ParentDesignation for those lines that are missing the fourth column, which would essentially mean to:

  • Read each line
  • Get ParentID from the third column
  • Match it with the value in the first column
  • Insert it into the fourth column4 in front of that child.

The result would be the following one.

A1  M.D-Sales    0  UmbrellaCorp
a1  Sr.Sales    A1  M.D-Sales
b1  Sr.R&D      B1  M.D-R&D
b2  Jr.SR&D     B1  M.D-R&D
a2  Jr.Sales    A1  M.D-Sales
B1  M.D-R&D      0  UmbrellaCorp

I know how I could do the same task in Excel with vlookup, but I need to use a script.

Improve this question
3
  • 1
    What is your field separator? Is it spaces or tabs? How can we know if a line has three fields whether it is missing the 4th field or the 3rd field (but has the 4th)?
    – terdon ♦
    Jul 30, 2021 at 15:04
  • @terdon it's tab separated, has 4 fields, thosse lines with 3 fields has 4th missing, that needs be vlookedup
    – Sollosa
    Jul 30, 2021 at 15:34
  • Please edit your question and include that. So, you basically just need to print those lines that have <4 fields?
    – terdon ♦
    Jul 30, 2021 at 15:37

3 Answers 3

Reset to default
6

Final answer given more comments below and updated sample input/output in question:

I'd sort the data first so the act of filling in the missing values is more efficient and uses less memory than doing a 2-pass approach within awk and the final output is much better organized than the input was for readability:

$ cat tst.sh
#!/usr/bin/env bash

awk '
    BEGIN { FS=OFS="\t" }
    { print (NR>1), ($4=="" ? $3 : $1), $4, $1, NR, $0 }
' "${@:--}" |
sort -t$'\t' -k1,1n -k2,2 -k3,3r -k4,4 -k5,5n |
cut -f6- |
awk '
    BEGIN { FS=OFS="\t" }
    $4 != "" { d = $2 }
    $4 == "" { $4 = d }
    { print }
'


$ ./tst.sh file | column -s$'\t' -t
ID  Designation  ParentID  ParentDesignation
A1  M.D-Sales    0         UmbrellaCorp
a1  Sr.Sales     A1        M.D-Sales
a2  Jr.Sales     A1        M.D-Sales
B1  M.D-R&D      0         UmbrellaCorp
b1  Sr.R&D       B1        M.D-R&D
b2  Jr.SR&D      B1        M.D-R&D

The first call to awk just decorates the input so it can be sorted by:

  1. (NR>1) = header-or-not 0-or-1 indicator to ensure the header line remains first after sorting,
  2. ($4=="" ? $3 : $1) = the ID or ParentID for each row to group related rows together
  3. $4 = the ParentDesignation so we can sort it such that rows with a ParentDesignation come before those that don't for the same ID/ParentID,
  4. $1 = the ID so we can sort children alphabetically by their ID,
  5. NR = so if everything else is common we can print the lines in the same order as they occurred in the input (probably not necessary in this case as every ID appears to be unique but good practice for other similar situations).

Then we just sort by the above fields and then remove the decorations using cut before passing to the final awk script to actually do the $4 population.

If you're not sure what any of those steps do, just change each | to | cat; exit one at a time and then you'll see what's happening at each step.

Previous answer:

Given the comments below, this might be what you want, assuming a parent (if it exists) always occurs before a child in your data:

$ cat tst.awk
BEGIN { FS=OFS="\t" }
$4 != "" {
    id2des[$1] = $2
}
$4 == "" {
    $4 = id2des[$3]
}
{ print }


$ awk -f tst.awk file
ID      Designation     ParentID        ParentDesignation
A1      M.D-Sales       0       UmbrellaCorp
a1      Sr.Sales        A1      M.D-Sales
a2      Jr.Sales        A1      M.D-Sales
B1      M.D-R&D 0       UmbrellaCorp
b1      Sr.R&D  B1      M.D-R&D
b2      Jr.SR&D B1      M.D-R&D

Original answer:

Your problem actually seems to be simpler than you specified as you appear to have a parent row with all info followed by children rows missing $4 in which case you don't need to look up anything, all you need is:

$ awk 'BEGIN{FS=OFS="\t"} $4!=""{d=$2} $4==""{$4=d} 1' file
ID      Designation     ParentID        ParentDesignation
A1      M.D-Sales       0       UmbrellaCorp
a1      Sr.Sales        A1      M.D-Sales
a2      Jr.Sales        A1      M.D-Sales
B1      M.D-R&D 0       UmbrellaCorp
b1      Sr.R&D  B1      M.D-R&D
b2      Jr.SR&D B1      M.D-R&D


$ awk 'BEGIN{FS=OFS="\t"} $4!=""{d=$2} $4==""{$4=d} 1' file | column -s$'\t' -t
ID  Designation  ParentID  ParentDesignation
A1  M.D-Sales    0         UmbrellaCorp
a1  Sr.Sales     A1        M.D-Sales
a2  Jr.Sales     A1        M.D-Sales
B1  M.D-R&D      0         UmbrellaCorp
b1  Sr.R&D       B1        M.D-R&D
b2  Jr.SR&D      B1        M.D-R&D
Improve this answer
10
  • No Ed, from given data it may look simpler, but in actuality this data is totally scattered, so parent row maybe there, or may not. Also, this is sample data, just to elaborate a bigger puzzle, idea is still excel like vlookup, if you can undestand me. P.S: (btw you're one of my fav-to-read-answer-from)
    – Sollosa
    Jul 30, 2021 at 15:54
  • 2
    Then fix the example in your question so it's more truly representative of your real data, i.e. doesn't have all children directly under their parent, and includes a case where there is no parent. If it''s possible for a child to occur before a parent then make sure to include that too.. We need you to provide an example that we can test a potential solution against to get a pass/fail result. btw - thanks!
    – Ed Morton
    Jul 30, 2021 at 15:56
  • now should do with updated data, so parent of a child maybe anywhere. even at bottom in infile
    – Sollosa
    Jul 30, 2021 at 16:08
  • 1
    Do you have such an entry in your example? If so I don't see it. If not, then add it. Again - we need you to provide an example that we can test a potential solution against to get a pass/fail result and that case is a game-changer for what might be a potential solution. Also - do you care about the order of the rows in the output or would it be OK to sort the input first such that parents DO always appear before children?
    – Ed Morton
    Jul 30, 2021 at 16:12
  • 2
    Right, this answers the question you asked but if you want to do something similar in the context of a larger awk script and can't figure out how to do it then just ask a new question and make sure to state that and provide a minimal script that this functionality would need to fit into.
    – Ed Morton
    Jul 30, 2021 at 16:54
3

If the parent always appears before the child ....

awk -F"\t" -v OFS="\t" '
    {dad[$1]=$2}
    !$4{$4=dad[$3]}1
    ' file

And if not then run through twice...

awk -F"\t" -v OFS="\t" '
    NR==FNR{dad[$1]=$2;next}
    !$4{$4=dad[$3]}1
    ' file file
Improve this answer
0
3

Just another very similar solution: a (gnu)awk multiline script, visiting the input file twice, trying to use vertical symmetries. $ cat awklookup

#!/usr/bin/awk -f
BEGIN{FS=OFS="\t";  
      ARGC=3; ARGV[2] = ARGV[1]}       ## visit the input file twice 
ARGIND==1        { tab[$1]=$2      }
ARGIND==2 && !$4 { $4=tab[$3]      }
ARGIND==2        { print           }

then:

$ chmod 755 awklookup
$ ./awklookup infile
ID  Designation  ParentID  ParentDesignation
A1  M.D-Sales    0         UmbrellaCorp
a1  Sr.Sales     A1        M.D-Sales
a2  Jr.Sales     A1        M.D-Sales
B1  M.D-R&D      0         UmbrellaCorp
b1  Sr.R&D       B1        M.D-R&D
b2  Jr.SR&D      B1        M.D-R&D
Improve this answer
1
  • 1
    Neat trick for re-using the input file.
    – BWhite
    Jul 31, 2021 at 4:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .