Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Check increment values in column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Check increment values in column
# 1  
Old 06-23-2015
Check increment values in column
Gents,

I have a file where i would like to check the constant increment by 2 in column 2.
Code:
5450 1000
5450 1002
5450 1004
5450 1006
5465 1000
5465 1002
5465 1006
5465 1008
5550 1002
5550 1004
5550 1006
5550 1008
6830 1000
6830 1002
6830 1008
6830 1010

I would like to get something like this:

Code:
5465 missing 1004          
6830 missing 1004,1006

Thanks for your help
# 2  
Old 06-23-2015
Hello Jiam912,

Following may help you in same.
Code:
 awk '{if(NR>1 && Y==$1 && $2-A!=2){DIFF=$2-A;for(i=1;i<DIFF/2;i++){A=A+2;print $1 " is missing " A}};Y=$1;A=$2}'  Input_file

Output is as follows.
Code:
5465 is missing 1004
6830 is missing 1004
6830 is missing 1006

NOTE: It will only look for difference of 2.

EDIT: Adding solution which will provide exact output as per user's request.
Code:
awk '{if(NR>1 && Y==$1 && $2-A!=2){DIFF=$2-A;for(i=1;i<DIFF/2;i++){A=A+2;Q=Q?Q "," A:$1 " is missing " A};print Q;Q=""};Y=$1;A=$2}'  Input_file

Output will be as follows.
Code:
5465 is missing 1004
6830 is missing 1004,1006

Thanks,
R. Singh
Last edited by RavinderSingh13; 06-23-2015 at 06:52 AM.. Reason: Added a bit edited solution to get as per user's solution output
This User Gave Thanks to RavinderSingh13 For This Post:
jiam912
# 3  
Old 06-23-2015
Great Solution,
Thanks Mr. Ravinder.
It works fine.
# 4  
Old 06-23-2015
The only thing with that solution is that it will print multiple instances of the first column if there are missing sequences in different sections. For example, with the input of:
Code:
9999 10
9999 14
9999 18
9999 20
9999 26

It will output the below:
Code:
9999 is missing 12
9999 is missing 16
9999 is missing 22,24

Another solution which should avoid that scenario is below:

Code:
awk '(f in a) && !($1 in a) {print f " missing " a[f]}
$1==f && $2!=(s+2){
for (x=s+2;x<$2;x+=2) {
    a[$1]=a[$1]?a[$1] "," x:x 
    }
}
{f=$1;s=$2}
END{if (a[f]) {print f " missing " a[f]}}
' file

This User Gave Thanks to pilnet101 For This Post:
jiam912
# 5  
Old 06-23-2015
Hi Pilnet
Thanks for your help, let me try your code.
regards
# 6  
Old 06-24-2015
Hello Jiam912/Pilnet,

I have edited my solution now and it will give the all missing lines in a single row.
Input_file:
Code:
 cat test45
5450 1000
5450 1002
5450 1004
5450 1006
5465 1000
5465 1002
5465 1006
5465 1008
5465 1010
5465 1022
5465 1024
5465 1028
5550 1002
5550 1004
5550 1006
5550 1008
6830 1000
6830 1002
6830 1008
6830 1010
6830 1012
6830 1020

Code as follows with output.
Code:
awk '{if(NR>1 && Y==$1 && $2-A!=2){DIFF=$2-A;for(i=1;i<DIFF/2;i++){A=A+2;Q[$1]=Q[$1]?Q[$1] "," A:$1 " has missing " A};};Y=$1;A=$2} END{for(i in Q){print Q[i]}}' test45
6830 has missing 1004,1006,1014,1016,1018
5465 has missing 1004,1012,1014,1016,1018,1020,1026

Thanks,
R. Singh
# 7  
Old 06-24-2015
Quote:
Originally Posted by RavinderSingh13
Hello Jiam912/Pilnet,

I have edited my solution now and it will give the all missing lines in a single row.
Code:
awk '{if(NR>1 && Y==$1 && $2-A!=2){DIFF=$2-A;for(i=1;i<DIFF/2;i++){A=A+2;Q[$1]=Q[$1]?Q[$1] "," A:$1 " has missing " A};};Y=$1;A=$2} END{for(i in Q){print Q[i]}}' test45
6830 has missing 1004,1006,1014,1016,1018
5465 has missing 1004,1012,1014,1016,1018,1020,1026

Thanks,
R. Singh
Ravinder don't mind for me it looks like you are confusing the user, solution given by Pilnet is simple and cool.

Just look at your code, there are no much difference in code compare to Pilnet has given except variable names and curly braces used in your case.

You have added NR>1 && which does nothing since Y==$1 fails since variable Y is not set when NR=1, also in loop you are doing for(i=1;i<DIFF/2;i++) instead of simple for (i=A;i<$2-A;i+=2).

My concern is just that if someone is learning, then let them learn simple way, because this type of things create confusion in beginning stage of learning.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to check for null values in a column

Hi Guys, I am new to shell script.I need your help to write a shell script. I have a csv file which has 13 columns separated by , I have written below script to fetch required 5 columns. awk -F, '(NR==1){h3=$3;h4=$4;h8=$8;h9=$9;h13=$13;next}(NF>1) \ {print... (5 Replies)
Discussion started by: Vivekit82
5 Replies

2. Shell Programming and Scripting

Check null values column

hi, I had a small question.I had a file from which i need to extract data. I have written the below script to check if the file exists and if it exists extract requierd columns from the file. IFILE=/home/home01/Report_1.csv OFILE=/home/home01/name.csv.out1 if #Checks if file exists... (1 Reply)
Discussion started by: Vivekit82
1 Replies

3. Shell Programming and Scripting

Increment existing column in file

Hi All, I have a file with 3 millions records in which 3rd column is same throughout say its value is 0 throughout.for example: Col1 Col2 Col3 Col4 A 1 0 5 B 2 0 6 C 3 0 7 D 4 0 9 I want my output as : Col1 Col2 Col3 Col4 A 1 ... (4 Replies)
Discussion started by: Pinky456
4 Replies

4. Shell Programming and Scripting

Check to identify duplicate values at first column in csv file

Hello experts, I have a requirement where I have to implement two checks on a csv file: 1. Check to see if the value in first column is duplicate, if any value is duplicate script should exit. 2. Check to verify if the value at second column is between "yes" or "no", if it is anything else... (4 Replies)
Discussion started by: avikaljain
4 Replies

5. Shell Programming and Scripting

To increment the values from the file

Hi I have the file called "file.txt" which contains the following output $cat file.txt sandy <version>1</version> karen <version>2</version> Rob <version>3</version> peter <version>4</version> i want to write a command which will add the value 1 to the digits and show the output... (2 Replies)
Discussion started by: sidh_arth85
2 Replies

6. Shell Programming and Scripting

Check file and increment

My scripts excepts 4 files ABCD_01 ABCD_02 ABCD_03 ABCD_04 I want to check for these files , and increment counter one by one . at the end i would like to echo as 4 of 4 expected instances of file found . I tried something like thsi $counter =1 if counter=counter+1 i need... (5 Replies)
Discussion started by: ultimatix
5 Replies

7. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

8. Shell Programming and Scripting

Increment a column using awk

Hi, I have a sample file like below: 213~!0~!Feb 16 2009 4:57:29:833PM~!0 212~!0~!Feb 7 2009 5:29:57:760PM~!0 211~!0~!Feb 4 2009 5:51:40:863PM~!0 209~!0~!Dec 17 2008 3:19:05:043PM~!0 206~!0~!Dec 4 2007 4:01:02:850PM~!0 "~!" is the field seperator. I need to replace the... (5 Replies)
Discussion started by: h_banka
5 Replies

9. UNIX for Dummies Questions & Answers

Check for null values in a column

Hi All, I have a file with 10 columns and get the required data for nine columns properly except 8th. In 8th column i have both NULL and NON NULL values...i.e certain records have values for all the columns including 8th column and certain records have 8th column as NULL.My requisite is,without... (20 Replies)
Discussion started by: ganesh_248
20 Replies

10. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies
Login or Register to Ask a Question