Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Fill in missing rows with zero to have uniform table
Top Forums Shell Programming and Scripting Fill in missing rows with zero to have uniform table Post 302939174 by yifangt on Monday 23rd of March 2015 12:13:20 PM
Old 03-23-2015
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 here.
Code:
file1:
S01    36407    53706    88540
S02    69343    87098    87316
S03    50133    59721    107923
S05    1290    41074    135810
S06    11285    30164    40361
S07    11285    30164    40361
S10    11298    30165    40361
S11    18311    37266    135798
S12    14567    35958    62691

Code:
file2:
S02    14644    37964    70990
S04    52922    84177    87225
S05    43427    68368    109344
S06    4212    15654    15664
S08    16257    41558    45595
S10    1290    41074    135810
S12    30731    60117    77166

Code:
file1_padded
S01    36407    53706    88540
S02    69343    87098    87316
S03    50133    59721    107923
S04    0    0    0
S05    0    41074    135810       #There was a typo 
S05   1290    41074    135810     #This is correct   
S06    11285    30164    40361
S07    11285    30164    40361
S08    0    0    0
S09    0    0    0
S10    11298    30165    40361
S11    18311    37266    135798
S12    14567    35958    62691

Code:
 file2_padded:
S01    0    0    0
S02    14644    37964    70990
S03    0    0    0
S04    52922    84177    87225
S05    43427    68368    109344
S06    4212    15654    15664
S07    0    0    0
S08    16257    41558    45595
S09    0    0    0
S10    1290    41074    135810
S11    0    0    0
S12    30731    60117    77166

Code:
file_joined
S01    36407    53706    88540        S01    0    0    0
S02    69343    87098    87316        S02    14644    37964    70990
S03    50133    59721    107923       S03    0    0    0
S04    0    0    0                    S04    52922    84177    87225
S05    1290    41074    135810           S05    43427    68368    109344    # Corrected
S06    11285    30164    40361        S06    4212    15654    15664
S07    11285    30164    40361        S07    0    0    0
S08    0    0    0                    S08    16257    41558    45595
S09    0    0    0                    S09    0    0    0
S10    11298    30165    40361        S10    1290    41074    135810
S11    18311    37266    135798       S11    0    0    0
S12    14567    35958    62691        S12    30731    60117    77166

I am not sure if there is a trick in awk to do this job,at least to fill the first column (the ID column). Thanks a lot!
Last edited by yifangt; 03-24-2015 at 04:43 PM.. Reason: add link
 

9 More Discussions You Might Find Interesting

1. 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

2. 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

3. Shell Programming and Scripting

Compare columns and rows with template, and fill empty slots.

Hi, I'm working on a script that will take the contents of a file, that is in a row and column format, and compare it to a arrangment file. Such that if there is any or all blanks in my content file, the blank will be filled with a flag and will retain the row and column configuration. Ex. ... (2 Replies)
Discussion started by: hizzle
2 Replies

4. 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

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 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

7. Shell Programming and Scripting

How to get the missing date and day in a table?

Hi Am using unix Aix Ksh Have Created table called vv and i have inserted two date Select * from vv; Output :- New_date 21/02/2013 24/02/2013 I have tried Using One query but Unsuccessful so far.. SELECT l.new_date + '1 day' as miss from vv as l (7 Replies)
Discussion started by: Venkatesh1
7 Replies

8. 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

9. Shell Programming and Scripting

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: 1 1 1 10 1 2 1 3 1 5 1 6 2 0... (6 Replies)
Discussion started by: theanh0508
6 Replies
All times are GMT -4. The time now is 01:50 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy