Read column values from previous and next line using awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read column values from previous and next line using awk
# 1  
Old 10-02-2012
Read column values from previous and next line using awk

Hi,

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

Code:
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. Smilie

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!! Smilie

Moderator's Comments:
Mod Comment Please view this code tag video for how to use code tags when posting code and data.

Last edited by Corona688; 10-02-2012 at 02:13 PM..
# 2  
Old 10-02-2012
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  
Old 10-02-2012
What I gave above is the output i want.

The input is like this:
Code:
       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:
Code:
         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.

Last edited by Corona688; 10-03-2012 at 01:23 PM.. Reason: Missed out some information and the content was not clearly formatted.
# 4  
Old 10-03-2012
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 Smilie):
Code:
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


Last edited by elixir_sinari; 10-03-2012 at 05:33 AM..
# 5  
Old 10-03-2012
Thanks, but I guess I'm missing something..

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?
Code:
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:
Quote:
key1, key2, key3
101, 12, 0
101, 13, 1
101, 14, 2
101, 15, 3
102, 21, 0
102, 22, 1
And I want my output to look like this:
Quote:
key1, key2, key3, new_key1, new_key2, new_key3
101, 12, 0, -, -, -,
101, 13, 1, 12, 13, 14
101, 14, 2, 13, 14, 15
101, 15, 3, 14, 15, 12
102, 21, 0, -, -, -,
102, 22, 1, 21, 22, 21
What I've done so far is:
Code:
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 Smilie. Also I haven't found the way to fill in the value for new_key3 as yet Smilie

Also i'm yet to do it on the basis of every unique key1. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Insert data in first column(if blank) from previous line first column

Dear Team I need to insert field(which is need to taken from previous line's first field) in first column if its blank. I had tried using sed but not find the way. Detail input and output file as below. Kindly help for same. INPUT: SCGR SC DEV DEV1 NUMDEV DCP ... (7 Replies)
Discussion started by: jaydeep_sadaria
7 Replies

2. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

3. Shell Programming and Scripting

Sed Comparing Parenthesized Values In Previous Line To Current Line

I am trying to delete lines in archived Apache httpd logs Each line has the pattern: <ip-address> - - <date-time> <document-request-URL> <http-response> <size-of-req'd-doc> <referring-document-URL> This pattern is shown in the example of 6 lines from the log in the code box below. These 6... (1 Reply)
Discussion started by: Proteomist
1 Replies

4. Shell Programming and Scripting

AWK: read values from file1; search for values in file2

I have read another post about this issue and am wondering how to adapt it to my own, much simpler, issue. I have a file of user IDs like so: 333333 321321 546465 ...etc I need to take each number and use it to print records wherein the 5th field matches the user ID pulled from the... (2 Replies)
Discussion started by: Bubnoff
2 Replies

5. Shell Programming and Scripting

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (3 Replies)
Discussion started by: yerruhari
3 Replies

6. Shell Programming and Scripting

eAdd two fields in a column if their previous field values are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output file as follows If field3 in file1 is same as field3 in the next line then the field4 should add... (1 Reply)
Discussion started by: yerruhari
1 Replies

7. UNIX for Dummies Questions & Answers

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

8. UNIX for Advanced & Expert Users

Compare two files using awk or sed, add values in a column if their previous fields are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output files as follows In my above example, each row in file1 has 6 fields and each row in file2 has 3... (1 Reply)
Discussion started by: yerruhari
1 Replies

9. Shell Programming and Scripting

read column values one after another

hi, Can some one help me how to retrieve column values row by row My requirement is like below : I have a text file having comma seperated values of these records . OName OType SrcDB Sschema targetdb TSchema Load Dataype processY/N aa Table a e i m Y db2 y aa index b c d e N sql N ... (4 Replies)
Discussion started by: sailaja_80
4 Replies

10. Shell Programming and Scripting

how to read the column and print the values under that column

hi all:b:, how to read the column and print the values under that column ...?? file1 have something like this cat file1 ======= column1, column2,date,column3,column4..... 1, 23 , 12/02/2008,...... 2, 45, 14/05/2008,..... 3, 56, 16/03/2008,..... cat file2 =======... (6 Replies)
Discussion started by: gemini106
6 Replies
Login or Register to Ask a Question

Featured Tech Videos