Copy Column Value Of Next Line Into Current Line

Shell Programming and Scripting
1

Hello All,

I am looking for help to achieve the following:

Here is the data set

1757890237|42|55570025|1468796400|0  
1757890237|32|55570025|1471474800|0
1757890237|54|55570025|1474153200|1476745200
1757890237|34|55570026|1468796400|0
1757890237|44|55570026|1471474800|0
1757890237|56|55570026|1474153200|1476745200
1757890237|36|55570027|1468796400|0
1757890237|46|55570027|1471474800|0
1757890237|58|55570027|1474153200|1476745200
1762391149|30|55570025|1468710000|0
1762391149|38|55570025|1468796400|0
1762391149|32|55570026|1468710000|0
1762391149|40|55570026|1468796400|0
1762391149|34|55570027|1468710000|0
1762391149|42|55570027|1468796400|0

In above data we have two distinct values in column 1 which are C1 and C2. We have three distinct values in column 3 which are K1, K2 and K3. I will grep the file three times (K1, K2 and K3) for each C (C1 and C2). After grep I will replace C5 of line n by C4 of line n+1 until C5 of line n+1 is not zero.

using while loop this can be done but it will be slow. Is there any awk command which will be quicker?

Please help.

Thanks
Angsuman

2

You lost me. WHAT are you doing "After grep"? Describe and post output.

And, please post your "while loop" so we can improve it or infer from it what needs to be done.

3

Following should be the output:

1757890237|42|55570025|1468796400|1471474800
1757890237|32|55570025|1471474800|1474153200
1757890237|54|55570025|1474153200|1476745200
1757890237|34|55570026|1468796400|1471474800
1757890237|44|55570026|1471474800|1474153200
1757890237|56|55570026|1474153200|1476745200
1757890237|36|55570027|1468796400|1471474800
1757890237|46|55570027|1471474800|1474153200
1757890237|58|55570027|1474153200|1476745200
1762391149|30|55570025|1468710000|1468796400
1762391149|38|55570025|1468796400|1468796400+30 days converted into seconds
1762391149|32|55570026|1468710000|1468796400
1762391149|40|55570026|1468796400|1468796400+30 days converted into seconds
1762391149|34|55570027|1468710000|1468796400
1762391149|42|55570027|1468796400|1468796400+30 days converted into seconds
4

Yes.

And the rest?

5

Sorry. What do you mean by rest?

6

The other questions in post#2.

7

Hello RudiC,

Sorry if my question is not clear. Let me rephrase as below:

Replace value of column 5 of a line with value of column 4 of previous line provided value of column 5 is zero and value of column 1 and column 3 of both lines match.

Replace value of column 5 of a line with value of column 4 of same line + constant if value of column 5 is zero and value of column 1 and column 3 of a line does not match with its previous line.

Thanks
Angshuman

---------- Post updated at 11:58 PM ---------- Previous update was at 10:54 PM ----------

I am also trying to explain with the example:

1757890237|54|55570025|1474153200|1476745200
1757890237|42|55570025|1468796400|0  
1757890237|32|55570025|1471474800|0
1757890237|56|55570026|1474153200|1476745200
1757890237|34|55570026|1468796400|0
1757890237|44|55570026|1471474800|0
1757890237|58|55570027|1474153200|1476745200
1757890237|36|55570027|1468796400|0
1757890237|46|55570027|1471474800|0
1762391149|30|55570025|1468710000|0
1762391149|38|55570025|1468796400|0
1762391149|32|55570026|1468710000|0
1762391149|40|55570026|1468796400|0
1762391149|34|55570027|1468710000|0
1762391149|42|55570027|1468796400|0

In above example,

  1. column 5 of line 1 is not zero. Hence no action
  2. column 5 of line 2 is zero, value of column 1 and column 3 of line 2 matches with column 1 and column 3 of line 1, replace column 5 with value of column 4 of line 1
  3. In line 10, value of column 1 and column 3 does not match with value of column 1 and column 3 of column 9, also value of column 5 in line 10 is 0, replace value of column 5 of line 10 with a constant value.

Thanks
Angsuman

8

Using your NEW example input file - not the one from post#1 - and the explanation you gave in post #7, the awk script

awk -F\| '!$5 {$5 = (LAST13 == $1 FS $3)?LAST4:"CONST"} 1; {LAST13 = $1 FS $3; LAST4 = $4}' OFS=\| file
1757890237|54|55570025|1474153200|1476745200
1757890237|42|55570025|1468796400|1474153200
1757890237|32|55570025|1471474800|1468796400
1757890237|56|55570026|1474153200|1476745200
1757890237|34|55570026|1468796400|1474153200
1757890237|44|55570026|1471474800|1468796400
1757890237|58|55570027|1474153200|1476745200
1757890237|36|55570027|1468796400|1474153200
1757890237|46|55570027|1471474800|1468796400
1762391149|30|55570025|1468710000|CONST
1762391149|38|55570025|1468796400|1468710000
1762391149|32|55570026|1468710000|CONST
1762391149|40|55570026|1468796400|1468710000
1762391149|34|55570027|1468710000|CONST
1762391149|42|55570027|1468796400|1468710000

gives an output that seems correct but differs severely from the one shown in post #3.

2 Likes