Add new column based in condition


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add new column based in condition
# 1  
Old 08-17-2018
Add new column based in condition

At begining of column 2 the same block (2000) have 3 lines, in the next block (2336) it have 9 lines and for block (2524) 3 lines.

In block (2336) which contend more than 3 lines. The value increase for column 1 is every 2, but when it is > 36. Then, from 3601 to 33607 (lower values) value in column 3 = S3 and for 34033 to 34041 (higher values) value in column 3 = S2

In the orignal file i have thousands of lines.

At beginning some blocks in column 2 will contends 3 lines, then value in column 3 = S2.
In the middle blocks will contend more than 3 line. Here we need to apply the condition. (lower values in column1) value in column 3 = S3, (higher values in column1) value in column 3 = S2
At the end blocks in column 2 will contends 3 lines, then value in column 3 = S3.

Code:
Input file

Code:
30001 2000
30003 2000
30005 2000
30007 2005
30009 2005
30011 2005
33601 2336
33603 2336
33605 2336
33607 2336
34033 2336
34035 2336
34037 2336
34039 2336
34041 2336
33609 2339
33611 2339
33613 2339
34043 2339
34045 2339
34047 2339
34049 2339
35857 2524
35859 2524
35861 2524
35855 2527
35857 2527
35869 2527

Output desired

Code:
30001 2000 S2
30003 2000 S2
30005 2000 S2
30007 2005 S2
30009 2005 S2
30011 2005 S2
33601 2336 S3
33603 2336 S3
33605 2336 S3
33607 2336 S3
34033 2336 S2
34035 2336 S2
34037 2336 S2
34039 2336 S2
34041 2336 S2
33609 2339 S3
33611 2339 S3
33613 2339 S3
34043 2339 S2
34045 2339 S2
34047 2339 S2
34049 2339 S2
35857 2524 S3
35859 2524 S3
35861 2524 S3
35855 2527 S3
35857 2527 S3
35869 2527 S3

I tried

Code:
awk '$3<0{c=3} {$3=(c-->0)?"S3":"S2"}1' tmp1 | tac | awk 'NR<=36 {$NF="S3"}1' | tac | awk '{print $2$1,$3}'

Thanks in advance Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk to add +1 to value based on condition in input

In the awk below I am trying to add a | that will adjust $2 in the ouput by adding +1 if the original value from file that was used in $3 had a - in it. Line 3 of file is an example of this. In my current awk I just subtract one but I am not sure how to only apply this to those values without a -.... (5 Replies)
Discussion started by: cmccabe
5 Replies

2. UNIX for Beginners Questions & Answers

Add New Column Based on Files Name

Dear Sir, I need to add new column (last column) based on files name. my files name 20170809_target_tdc_pmx4.xls 20170809_target_tdc_pmx5.xls 20170809_target_tdc_pmx6.xls for example file : 20170809_target_tdc_pmx4.xls Item code Quantity Unit price Serial number... (1 Reply)
Discussion started by: radius
1 Replies

3. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

4. Shell Programming and Scripting

Check first column - average second column based on a condition

Hi, My input file Gene1 1 Gene1 2 Gene1 3 Gene1 0 Gene2 0 Gene2 0 Gene2 4 Gene2 8 Gene3 9 Gene3 9 Gene4 0 Condition: If the first column matches, then look in the second column. If there is a value of zero in the second column, then don't consider that record while averaging. ... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. UNIX for Dummies Questions & Answers

Add 1 column based on name of files

Hi all, I already transferred list of files from ftp server with files name end with $Y$m$d Example my files data_20140101.xls data_20140102.xls data-20140103.xls and content of each files, for example data_20140101.xls USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445... (6 Replies)
Discussion started by: radius
6 Replies

6. UNIX for Dummies Questions & Answers

Condition based on Timestamp (Date/Time based) from logfile (Epoch seconds)

Below is the sample logfile: Userids Date Time acb Checkout time: 2013-11-20 17:00 axy Checkout time: 2013-11-22 12:00 der Checkout time: 2013-11-17 17:00 xyz Checkout time: 2013-11-19 16:00 ddd Checkout time: 2013-11-21 16:00 aaa Checkout... (9 Replies)
Discussion started by: asjaiswal
9 Replies

7. Shell Programming and Scripting

Sum Of Column Based On Column Condition

I have a following inputfile MT,AP,CDM,TTML,MUM,GS,SUCC,3 MT,AP,CDM,TTSL,AP,GS,FAIL,9 MT,AP,CDM,RCom,MAH,GS,SUCC,3 MT,AP,CDM,RTL,HP,GS,SUCC,1 MT,AP,CDM,Uni,UPE,GS,SUCC,2 MT,AP,CDM,Uni,MUM,GS,SUCC,2 TTSL,AP,GS,MT,MAH,CDM,SUCC,20 TTML,AP,GS,MT,MAH,CDM,FAIL,10... (2 Replies)
Discussion started by: siramitsharma
2 Replies

8. Shell Programming and Scripting

to add special tag to a column based on column condition

Hi All, I have following html code <TR><TD>9</TD><TD>AR_TVR_TBS </TD><TD>85000</TD><TD>39938</TD><TD>54212</TD><TD>46</TD></TR> <TR><TD>10</TD><TD>ASCV_SMY_TBS </TD><TD>69880</TD><TD>33316</TD><TD>45698</TD><TD>47</TD></TR> <TR><TD>11</TD><TD>ARC_TBS ... (9 Replies)
Discussion started by: ckwan
9 Replies

9. Shell Programming and Scripting

awk to select rows based on condition on column

I have got a file like this 003ABC00281020091005000100042.810001 ... (8 Replies)
Discussion started by: Maruti
8 Replies

10. Shell Programming and Scripting

Filter the column and print the result based on condition

Hi all This is my output of the some SQL Query TABLESPACE_NAME FILE_NAME TOTALSPACE FREESPACE USEDSPACE Free ------------------------- ------------------------------------------------------- ---------- --------- ---------... (2 Replies)
Discussion started by: jhon
2 Replies
Login or Register to Ask a Question