Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Filling in missing columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Filling in missing columns
# 1  
Old 10-17-2009
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

Code:
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  6  2  6  9  8

So I want to fill in each row so that they all have values up to 800 columns

file after

Code:
bob  2  4  5  6  8  9  4  5
tar   2  4  5  4  3  2  9  1
bro  3  5  3  4 NA NA NA NA
yar  2  6  2  6  9  8 NA NA

hope I was clear in what I wanted to do

thank you
# 2  
Old 10-17-2009
Try awk Smilie
Code:
# awk 'NR==FNR{max=(NF>max)?NF:max;next}{for(i=0;++i<=max;)$i=($i)?$i:"NA"}1' file file
bob 2 4 5 6 8 9 4 5
tar 2 4 5 4 3 2 9 1
bro 3 5 3 4 NA NA NA NA
yar 2 6 2 6 9 8 NA NA

# 3  
Old 10-17-2009
Add a -v max=(some value) after the awk keyword to tell Dan's command to set the number of columns if the initial line is short and set the max number of columns if the input has more than expected..
# 4  
Old 10-17-2009
Hmm I tried but a blank document comes out. Maybe I forgot to mention that the file is tab delimited. Also does it need to be sorted in a particular order?
# 5  
Old 10-17-2009
Code:
# awk '{FS=OFS="\t"}NR==FNR{max=(NF>max)?NF:max;next}{for(i=0;++i<=max;)$i=$i?$i:"NA"}1'  file file
bob     2       4       5       6       8       9       4       5
tar     2       4       5       4       3       2       9       1
bro     3       5       3       4       NA      NA      NA      NA
yar     2       6       2       6       9       8       NA      NA

# 6  
Old 10-17-2009
hi

I am still getting a blank document. hmm
# 7  
Old 10-17-2009
NB:not tab separated
Code:
awk 'NF<8{ a=""; for(i=1;i<=8-NF;i++){a=a"NA " }$0=$0" "a}1' file
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk Matching Columns - Am I missing something?

I am using awk to match columns and output based on those matches. For some reason it is not printing matching columns, am I missing something? Operating system - windows with cygwin. Command that I am using: sed 's/]*,]*/,/g' $tempdir/file1 > $tempdir/file1.$$ && awk -F, 'FNR==NR{f2=$2... (7 Replies)
Discussion started by: dis0wned
7 Replies

2. Shell Programming and Scripting

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... (5 Replies)
Discussion started by: numareica
5 Replies

3. UNIX for Dummies Questions & Answers

Filling in the empty columns with rows above them

Hi, I have a text file where some of the values in columns are missing: Input: QhFudW3dKSYL2NTQUA 1 11133999-11134048 uhIp9KxB6USIy16CEY 1 11126760-11126774 11126775-11126805 11128065-11128068 6epeZ6CTNW4Au8uXys 1 24480823-24480872 Tp3nvutucBZ13CjuXc 1 155204870-155204891... (3 Replies)
Discussion started by: evelibertine
3 Replies

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

5. UNIX for Dummies Questions & Answers

Filling in empty columns with "X"

I have a text file which includes empty columns with missing values. I want to fill the empty columns with the character "X". How do I go about doing that? Thanks! (1 Reply)
Discussion started by: evelibertine
1 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. UNIX for Dummies Questions & Answers

Filling in the empty columns with the rows above them

I have a text file that looks like the following: 5.644 39.2% 0.00 0.50 rs1695626 4 -0.003 0.21% 0.03 0.73 rs1763326 3 -0.001 0.03% 0.00 0.89 5.645 39.2% 0.00 0.50 rs770721 2 -0.002 0.07% 0.01 0.84... (7 Replies)
Discussion started by: evelibertine
7 Replies

8. UNIX for Dummies Questions & Answers

Filling the empty columns in a fixed column file

Hi, I have a file with fixed number of columns (total 58 columns) delimeted by pipe (|). Due to a bug in the application the export file does not come with fixed number of columns. The missing data columns are being replaced by blank in the output file. In one line I can have 25 columns (33... (1 Reply)
Discussion started by: yale_work
1 Replies

9. UNIX for Dummies Questions & Answers

Filling in the empty columns using the rows above them

I have a text file that looks like the following: rs529715 CFD rs1550758 CIDEB Magmas rs12542019 CPNE1 RBM12 rs10515181 CPNE1 RBM12 rs4411112 CPT1A Some rows have 1 column, whereas some have 2. The ones that have 2 columns have one column (1st column) that start with rs. I need to... (6 Replies)
Discussion started by: evelibertine
6 Replies

10. 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
Login or Register to Ask a Question