Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Filling in the missing data point by awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Filling in the missing data point by awk
# 1  
Old 01-14-2016
Filling in the missing data point by awk
I am learning AWK by trying out examples whenever I need a specific conversion. I would like to edit the 'before.txt' so that all the missing data points between 140-150 are added and shown as 0.

before.txt

145 2
148 13
149 17

to below,

140 0
141 0
142 0
143 0
144 0
145 2
146 0
147 0
148 13
149 17
150 0

And here is what I tried so far.

Code:
awk 'BEGIN { for (i = 140; i <= 150; ++i){if(i=$1) print $i,$1;else print $i,0}}' before.txt

Any help would be great
# 2  
Old 01-14-2016
Try
Code:
awk 'NR == FNR {T[$1] = $2; next} END {for (i=140; i<=150; i++) print i, (i in T)?T[i]:0}' file
140 0
141 0
142 0
143 0
144 0
145 2
146 0
147 0
148 13
149 17
150 0

This User Gave Thanks to RudiC For This Post:
numareica
# 3  
Old 01-14-2016
You do something in the BEGIN section, before the before.txt is processed.
And then you do nothing in the main section (where it's too late anyway).
The solution is to process the file in the main section and store it into variables (An array that is addressed by the $1 values), and then process/print in the END section. Like RudiC did.
The following is multi-line for readability (at least for me), and does not have the NR==FNR {...; next} overhead (that takes the first given file, where the following code will take the next file(s) - but here is only one file).
Code:
awk '
# main section; process all lines of all given files
{ T[$1]=$2 }
END {
  for (i=140; i<=150; ++i) {
    if (i in T) print i,T[i]; else print i,0
  }
}' before.txt

It prints with an explicit if - else, like you did; compare with RudiC's implicit if!
These 2 Users Gave Thanks to MadeInGermany For This Post:
numareica RudiC
# 4  
Old 01-14-2016
Quote:
Originally Posted by numareica
I am learning AWK by trying out examples whenever I need a specific conversion. I would like to edit the 'before.txt' so that all the missing data points between 140-150 are added and shown as 0.
before.txt
145 2
148 13
149 17
to below,
140 0
141 0
142 0
143 0
144 0
145 2
146 0
147 0
148 13
149 17
150 0
And here is what I tried so far.
Code:
awk 'BEGIN { for (i = 140; i <= 150; ++i){if(i=$1) print $i,$1;else print $i,0}}' before.txt

Any help would be great
Hello numareica,

Welcomes to forums, special thanks for using the code tags. Could you please try following and let me know if this helps.
1st: If you need to have maximum number till 150 only from 140 then following may help you.
Let's say following is the Input_file:
Code:
140 2
148 13
149 17
152 28
153 20

Code:
awk -vSTART=140 -vend=150 'NR==1{if(START < $1){while(START <= $1){if(START==$1){print;Y=$1;next} else {print START++ OFS 0}}};if(START==$1){print;Y=$1};next}{if($1 - Y > 1){while(Y<end && ++Y <= $1){if(Y==$1){print;Y=$1;;next} else {print Y OFS 0}}} else {print $0;Y=$1}}'   Input_file

Output will be as follows.
Code:
140 2
141 0
142 0
143 0
144 0
145 0
146 0
147 0
148 13
149 17
150 0

EDIT: Adding 1st solution's non-one liner form now.
Code:
awk -vSTART=140 -vend=150 'NR==1{
                                        if(START < $1){
                                                        while(START <= $1){
                                                                                if(START==$1){
                                                                                                print;
                                                                                                Y=$1;
                                                                                                next
                                                                                             }
                                                                                else         {
                                                                                                print START++ OFS 0
                                                                                             }
                                                                          }
                                                      };
                                        if(START==$1) {
                                                        print;
                                                        Y=$1
                                                      };
                                        next
                                }
                                {
                                        if($1 - Y > 1){
                                                        while(Y<end && ++Y <= $1)            {if(Y==$1){
                                                                                                        print;
                                                                                                        Y=$1;
                                                                                                        next
                                                                                                       }
                                                                                              else     {
                                                                                                        print Y OFS 0
                                                                                                       }
                                                                                             }
                                                      }
                                        else          {
                                                        print $0;
                                                        Y=$1
                                                      }
                                }
                         '  Input_file

2nd: If you need to run the commands till the last maximum number starting from 140 then following may help you in same. But in case maximum number is lesser than value of 150 then it will take care of printing the values till 150.
Let's say following is the Input_file:
Code:
145 2
148 13
149 17
152 28
153 20
176 2
190 0

Then following is the code for above:
Code:
awk -vSTART=140 -vend=150 'NR==1{if(START < $1){while(START <= $1){if(START==$1){print;Y=$1;next} else {print START++ OFS 0}}};if(START==$1){print;Y=$1};next}{if($1 - Y > 1){while(++Y <= $1){if(Y==$1){print;Y=$1;;next} else {print Y OFS 0}}} else {print $0;Y=$1}} END{;if(end>Y){while(Y < end){print ++Y OFS 0;}}}'   Input_file

Output will be as follows then.
Code:
140 0
141 0
142 0
143 0
144 0
145 2
146 0
147 0
148 13
149 17
150 0
151 0
152 28
153 20
154 0
155 0
156 0
157 0
158 0
159 0
160 0
161 0
162 0
163 0
164 0
165 0
166 0
167 0
168 0
169 0
170 0
171 0
172 0
173 0
174 0
175 0
176 2
177 0
178 0
179 0
180 0
181 0
182 0
183 0
184 0
185 0
186 0
187 0
188 0
189 0
190 0

EDIT: Adding a non-one liner form of 2nd solution now.
Code:
awk -vSTART=140 -vend=150 'NR==1{
                                        if(START < $1){
                                                        while(START <= $1){
                                                                                if(START==$1){
                                                                                                print;
                                                                                                Y=$1;
                                                                                                next
                                                                                             }
                                                                                else         {
                                                                                                print START++ OFS 0
                                                                                             }
                                                                          }
                                                      };
                                        if(START==$1) {
                                                        print;
                                                        Y=$1
                                                      };
                                        next
                                }
                                {
                                        if($1 - Y > 1){
                                                        while(++Y <= $1)   {
                                                                                if(Y==$1)    {
                                                                                                print;
                                                                                                Y=$1;;
                                                                                                next
                                                                                             }
                                                                                else         {
                                                                                                print Y OFS 0
                                                                                             }
                                                                           }
                                                      }
                                        else          {
                                                        print $0;
                                                        Y=$1
                                                      }
                                                      }
                          END    {;
                                        if(end>Y)     {
                                                        while(Y < end)     {
                                                                                print ++Y OFS 0;
                                                                           }
                                                      }
                                 }
        ' Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 01-14-2016 at 09:59 AM.. Reason: Added non one-liner forms of both the solutions now.
This User Gave Thanks to RavinderSingh13 For This Post:
numareica
# 5  
Old 01-14-2016
Quote:
Originally Posted by MadeInGermany
.
.
.
and does not have the NR==FNR {...; next} overhead (that takes the first given file, where the following code will take the next file(s) - but here is only one file).
.
.
.
Absolutely right - I started "the usual way" and overlooked the "one file only" ... rats!
# 6  
Old 01-15-2016
Worked like a charm! thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Missing data

Gents, Using the following code. awk -F: 'BEGIN { print "Time,FFID,Swath,Line,Point"; } /(SCI TB Timestamp Local : |File # :|Swath Name :|Tape # :|Line Name :|Point Number :|Type_Of_Dump|Type_Of_Test|Tape_Nb|Tape_Label|Date|Hist)/{ sub("^*","",$2);sub("*$","",$2); if($1 ~ /Hist/) { printf... (2 Replies)
Discussion started by: jiam912
2 Replies

2. Post Here to Contact Site Administrators and Moderators

How to sum up data in fixed width file with decimal point?

HI Everyone, I have below source file AAA|NAME1|ADDRESS1|300.20 BBB|NAME2|ADDRESS2|400.31 CCC|NAME3|ADDRESS3|300.34 I have requirement where I need to sum up fourth field in above fixed width pipe delimited flat file. When I use below code, it gives me value 1001.00 But I am expecting... (1 Reply)
Discussion started by: patricjemmy6
1 Replies

3. Programming

Find gaps in time data and replace missing time value and column 2 value by interpolation in awk

Dear all, I am kindly seeking assistance on the following issue. I am working with data that is sampled every 0.05 hours (that is 3 minutes intervals) here is a sample data from the file 5.00000 15.5030 5.05000 15.6680 5.10000 16.0100 5.15000 16.3450 5.20000 16.7120 5.25000... (4 Replies)
Discussion started by: malandisa
4 Replies

4. Solaris

Mount point at 100%, but cannot see what is filling up

Hi Please I need some help, I have system running solaris 10, with a file system at 100%: df -h /nikira Filesystem size used avail capacity Mounted on /dev/dsk/c5t500A09818DE3E799d0s0 226G 223G 0K 100% /nikira but when I look inside to... (17 Replies)
Discussion started by: fretagi
17 Replies

5. UNIX for Dummies Questions & Answers

Merging two text files by a column and filling in the missing values

Hi, I have to text files that I want to merge by the first column. The values in the first column pretty much match for the first part. However there are some values that are present in column 1 and not present in column 2 or vice versa. For such values I would like to substitute X for the... (9 Replies)
Discussion started by: evelibertine
9 Replies

6. UNIX for Dummies Questions & Answers

Filling a tab-separated file with known missing entries in columns

Hello all, I have a file which is tab separated like that: PHE_205_A TIP_127_W ARG_150_B MET_1150_A TIP_12_W VAL_11_B GLU_60_A TIP_130_W ARG_143_B LEU_1033_A TIP_203_W ARG_14_B SER_1092_A TIP_203_W THR_1090_A TIP_203_W SER_1092_A TIP_25_W ... (6 Replies)
Discussion started by: TheTransporter
6 Replies

7. Shell Programming and Scripting

filling in strings in a template file using awk

Hi all, I have a template form to fill in for quite a number of files and I want to automate the filling-in process. the concept seemed to be simple but i cant get it work. the template form is a text file containing the information below: File Name: Date Created: Contents: I need to... (4 Replies)
Discussion started by: ida1215
4 Replies

8. Solaris

import lun data to mount point - Solaris 10

Hi Guys, I have EMC Storage and from this storage I have maped lun5 to Sun Solaris server and I have created on this lun mount point with name /application I have anothere Sun Solaris server and I'll colne lun5 to lun10 from storage level so the data of lun5 will be in lun10 how to... (6 Replies)
Discussion started by: Mr.AIX
6 Replies

9. UNIX for Advanced & Expert Users

Extract data from a particular point in a file

Hi, I wanted to extract the data from a particular comma from each line. My file contains some 10000 lines and each lines are separated by commas. There are 60 fields which are separated by 59 commas. I wanted to show the data which are there after the 49th comma from each line. For... (2 Replies)
Discussion started by: satyaatcgi
2 Replies

10. Shell Programming and Scripting

Filling in missing columns

Hi all, I have a file that contains about 1000 rows and 800 columns. Nearly every row has 800 columns but some DONT. I want to extend the rows that dont have values with NA's. Here is an example: my file bob 2 4 5 6 8 9 4 5 tar 2 4 5 4 3 2 9 1 bro 3 5 3 4 yar 2 ... (7 Replies)
Discussion started by: gisele_l
7 Replies
Login or Register to Ask a Question