Fill in missing values


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fill in missing values
# 1  
Old 07-27-2018
Fill in missing values

Hi,

I have a data sample as shown below. I want to fill in the left column so that the line will be continuous. For example, between 1 and 5 should be 2,3,4. And corresponding values in the right column will be 0. Thus the expected data should look like that:
Code:
1 1
1 10
1 2
1 3
1 5
1 6
2 0
3 0
4 0
5 44 
6 0
7 60
7 61

Any help or suggestion would be much appreciated.

Code:
1 1
1 10
1 2
1 3
1 5
1 6
5 44
7 60
7 61
7 62
8 73
10 89
11 98
12 106
12 107
12 109
13 117
15 140
15 142
17 159
17 162
18 171
19 179
19 183
19 184
19 186
20 198
21 211
21 213
21 215
21 216
22 230
22 232
24 264
25 268
26 294
27 305
27 306
28 318
29 335
34 398
35 404
38 428
39 435
40 444
40 448
44 474
44 476
45 485
46 499
47 508
47 509
48 517
48 519
49 530
50 539
51 546

# 2  
Old 07-27-2018
Anything attempted already?
# 3  
Old 07-27-2018
I come up with this one:
Code:
file='XX1.txt'
i=1
while [ $i -lt 366 ]
do
  head -$i $file | tail -1 | cut -c1-5 > a.txt
  grep "$i" a.txt
  check2=$?
#  echo $check2
  if [ $check2 -eq 1 ]
  then
#    printf "%3s %3s\n" $(sed -i ''$i' i \ '$i' 0\' $file)
     sed -i ''$i' i \'$i', 0\' $file
  fi
  rm -f a.txt
  i=`expr $i + 1`
done

Since it turns out more duplicate numbers, I am getting trouble
# 4  
Old 07-27-2018
how about - a bit rough..:
Code:
awk 'FNR!=1 && p+1!=$1{for(i=p+1;i<$1;i++) print i,0}p=$1' myFile

This User Gave Thanks to vgersh99 For This Post:
# 5  
Old 07-27-2018
I got it!

Thank you so much!
# 6  
Old 07-27-2018
I think the FNR!=1 condition is a bug here.
Actually the whole condition is not needed, knowing that the for can run zero times.
Code:
awk '{for (i=p1+1; i<$1; i++) print i,0; print; p1=$1}'

# 7  
Old 07-27-2018
In standard shell:
Code:
file='XX1.txt'
format="%3s %3s\n"
prev=0 nr=0
while read col1 col2
do
  : $((nr+=1))
  i=$prev
  while [ $((i+=1)) -lt $col1 ]
  do
    printf "$format" "$i" "0"
  done
  printf "$format" "$col1" "$col2"
  prev=$col1
done < "$file"

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Fill in missing hours and interpolate values using awk.

I have a time series data like this 40754,35.6931,51.3092,201610160700,21.0 40754,35.6931,51.3092,201610160800,23.0 40754,35.6931,51.3092,201610160900,24.0 40754,35.6931,51.3092,201610161000,24.0 40754,35.6931,51.3092,201610161300,25.0 40754,35.6931,51.3092,201610161400,23.0... (6 Replies)
Discussion started by: emirzaei
6 Replies

2. Shell Programming and Scripting

Fill in missing rows with zero to have uniform table

Hello, I have two files of same structure except some rows are missing randomly in each file. How do I fill the missing rows to have the exact ID column (S01 ~ S96) and rest columns filled with "0" with awk? The purpose of this step is to join the two files side by side. The closest thread is... (17 Replies)
Discussion started by: yifangt
17 Replies

3. Shell Programming and Scripting

Insert missing values

Hi, please help with this, I need to insert missing values into a matrix for a regression analysis. I have made up an example. The first three columns are variables with levels and the next 3 are values, the 4th column missing values should be replaced by 0s, and 5th and 6th column missing... (3 Replies)
Discussion started by: ritakadm
3 Replies

4. Shell Programming and Scripting

Fill in missing Data

hello everyone, I have a task to input missing data into a file. example of my data below: Wed Feb 01 09:00:02 EST 2012,,,0.4,0.3,,0.3,,0.3,,0.5,,0.3,,,0.4,0.3, Wed Feb 01 09:00:11 EST 2012,,,,,,,0.2,,,,,,,,,, Wed Feb 01 09:00:22 EST... (23 Replies)
Discussion started by: Nolph
23 Replies

5. Shell Programming and Scripting

Fill missing values with 2

Hi All, I have 100 .txt files which look like this: 3 4 5 6 7 Now, some files have some numbers missing in them and they look like this: 4 5 6 (6 Replies)
Discussion started by: shoaibjameel123
6 Replies

6. Shell Programming and Scripting

Fill missing numbers in second column with zeros

Hi All, I have 100 files with names like this: 1.dat, 2.dat, 3.dat until 100.dat. My dat files look like this: 42323 0 438939 1 434 0 0.9383 3434 120.23 3 234 As you can see in the second column, some numbers are missing. I want to fill those missing places with 0's in all... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

7. Shell Programming and Scripting

Fill the values between -500 to 500 -awk

input -200 2.4 0 2.6 30 2.8 output -500 0 -499 0 -488 0 .......... .......... .... -200 2.4 .... ... 0 2.6 (6 Replies)
Discussion started by: quincyjones
6 Replies

8. Shell Programming and Scripting

fill in missing columns

It can't be that hard, but I just can't figure it out: I have file like: File Sub-brick M_1 S_1 M_2 S_2 M_4 S_4 ... xxx 1 214 731 228 621 132 578 ... and would like to get 0 0 where M_3 S_3 is missing xxx 1 214 731 228 621 0 0 132 578 ... I wrote following script, but can't figure out... (3 Replies)
Discussion started by: avvk
3 Replies

9. Shell Programming and Scripting

Fill in missing numbers in range

I need to edit a list of numbers on the following form: 1 1.0 2 1.4 5 2.1 7 1.9 I want: 1 1.0 2 1.4 3 0.0 4 0.0 5 2.1 6 0.0 7 1.9 (i want to add the missing number in column 1 together with 0.0 in column 2). I guess it is rather trivial but i didn't even manage to read column... (5 Replies)
Discussion started by: bistru
5 Replies
Login or Register to Ask a Question