Read column values from previous and next line using awk

Shell Programming and Scripting
1

Hi,

I have a csv file which contains data that looks something like this:

Key1 Key2 Key3 New_Key1 New_Key2 New_Key3
102   30      0        -                 -                  -
102   40      1        30               40               50
102   50      2        40               50               30
103   12      0        -                 -                  -
103   15      1        12              15               12

I need to fill in New_Key1, New_Key2 and New_Key3 based on the existing data taken from column Key2 keeping Key1 as the reference. A set of values of Key2 can be mapped to Key1. Like we have 30, 40 and 50 are mapped to Key1=102. Using this I need to fill in for New_Key1, New_Key2 and New_Key3. Here for my first row the New_Key1, New_Key2 and New_Key3 need to be dashed out. The value of New_Key1 on the second row is same as the 1st value of Key2(old) and the value of New_Key3 will be same as the 3rd value of Key2(new future value). While the value of New_Key2 will be same as the current value of Key2. Similarly for the next set of rows mapping to the same Key1 the process needs to repeat. But there's one more thing to it...The last value of New_Key3 will be the same as the first value of Key2. I hope my explanation is not confusing. :confused:

I want to this the awk way..but i'm kind of stuck. It might need me to use many more commands than is required. Please help me out!! :mad:

2

It would be less confusing if you showed the output you wanted rather than described it. You could even do both, explaining what's different and why.

3

What I gave above is the output i want.

The input is like this:

       Key1              Key2              Key3       102                 30                   0       102   40   1       102   50   2       103   12   0       103   15   1

I need to add three new columns to the existing columns above and insert data into them from the column Key2.

The output is expected to look like this:

         Key1   Key2   Key3   New_Key1   New_Key2   New_Key3       102   30   0   -   -   -       102   40   1   30   40   50       102   50   2   40   50   30       103   12   0   -   -   -       103   15   1   12   15   12

The first row to any unique Key1, has New_Key1, New_Key2, New_Key3 dashed out. Now for the next row, the first value of Key2(in this case 30) becomes New Key1, the second value of Key2(40) fills in for New_Key2 and the third value(50) fills in for New_Key3. The process then repeats for the next row where the 2nd, 3rd and 4th values of Key2 are taken to fill New_Key1, New_Key2 and New_Key3 repectively. All this happens provided its mapped to the same Key1. I hope I'm clearer now.

4

Try (I've assumed no header. If you want to handle the header, a slight modification is needed. That is left as an exercise for you ;)):

awk 'FNR==NR{a[$1]=(a[$1])?(a[$1] "|" $0):$0;next}
{c[$1]++}
c[$1]==1{$4=$5=$6="-";print}
c[$1]>1{
n=split(a[$1],t,"|")
if(n==c[$1])
 n3=t[1]
else
 n3=t[c[$1]+1]
split(t[c[$1]-1],new1," ")
split(n3,new3," ")
print $0,new1[2],$2,new3[2]
}' file file
5

Hey Elixir!

Thanks for that. But I'm pretty confused with the pipe you've used on the first line of the code and then later for the split. Help me understand if you use this pipe to append at the end of each line?

awk 'FNR==NR{a[$1]=(a[$1])?(a[$1] "|" $0):$0;next}

I'm not sure if you know how my input looks like. I really apologize since i'm new to the forum i'm pretty much bad with the code tags and formatting. Just to let you know, my input looks something like this:

And I want my output to look like this:

What I've done so far is:

awk -F"," 'NR==FNR {a[$1]=$0; {split(a[$1],b,","); {if ($NR ~ /[A-Z]/) print a[$1], "new_key1, new_key2, new_key3"; else if (NR==2) {print a[$1], "-,-,-";} else if (NR>1) {if ((getline tmp) > 0) {{ a[$1]=tmp; {split(a[$1],b,","); field=b[2]; print a[$1], ",", $2, ",", field, ",", "YTF"}}}}' file

Though this doesn't fetch me the entire output as getline does it for every alternate line it encounters leaving me with only a partial output :mad:. Also I haven't found the way to fill in the value for new_key3 as yet :frowning:

Also i'm yet to do it on the basis of every unique key1. :wall: