Add a character C in a column if that column is blank


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add a character C in a column if that column is blank
# 1  
Old 08-26-2013
Add a character C in a column if that column is blank

I have some files that look as follows. I need to add a character 'C' in the fifth column if that column is blank. I prefer in-place editing.
Code:
    1    1 B M              0   0  203      0, 0.0     0, 0.0     0, 0.0     0, 0.0   0.000 360.0 360.0 360.0 141.9   15.4   28.8   66.1
    2    2 B A        +     0   0   96      2,-0.0     2,-0.1     0, 0.0     0, 0.0   0.211 360.0 128.7-112.4   7.4   18.7   30.7   66.2
    3    3 B K        -     0   0  170      1,-0.0     2,-0.3     0, 0.0     0, 0.0  -0.311  37.4-173.9 -67.6 143.5   20.5   27.4   65.6
    4   44 B M  G <  S+     0   0  159     -3,-2.3    -1,-0.2    -4,-0.3    -2,-0.2   0.445  80.4 133.6 -94.8   0.2   11.7   15.9   -0.7
    5   45 B L    <   -     0   0   46     -3,-1.4    -3,-0.0    -4,-0.2     5,-0.0  -0.218  64.1-113.7 -57.2 131.3   15.2   17.0    0.3
    7  541 A E  S    S-     0   0  135     -3,-0.1    -1,-0.1     0, 0.0    -2,-0.0   0.150  74.0  -8.3-107.3-139.1   42.1   66.5   41.6
    8  542 A E  S    S-     0   0  170      1,-0.1     4,-0.1    -2,-0.1    -2,-0.1   0.348  84.9-132.3 -44.0  -5.3   41.2   70.2   42.2
    9  543 A E        +     0   0   38      1,-0.2     3,-0.2     2,-0.1    -1,-0.1   0.934  39.1 177.3  45.2  63.8   39.8   69.0   45.6

Desired Output

Code:
    1    1 B M  C           0   0  203      0, 0.0     0, 0.0     0, 0.0     0, 0.0   0.000 360.0 360.0 360.0 141.9   15.4   28.8   66.1
    2    2 B A  C      +    0   0   96      2,-0.0     2,-0.1     0, 0.0     0, 0.0   0.211 360.0 128.7-112.4   7.4   18.7   30.7   66.2
    3    3 B K  C      -    0   0  170      1,-0.0     2,-0.3     0, 0.0     0, 0.0  -0.311  37.4-173.9 -67.6 143.5   20.5   27.4   65.6
    4   44 B M  G <  S+     0   0  159     -3,-2.3    -1,-0.2    -4,-0.3    -2,-0.2   0.445  80.4 133.6 -94.8   0.2   11.7   15.9   -0.7
    5   45 B L  C <   -     0   0   46     -3,-1.4    -3,-0.0    -4,-0.2     5,-0.0  -0.218  64.1-113.7 -57.2 131.3   15.2   17.0    0.3
    7  541 A E  S    S-     0   0  135     -3,-0.1    -1,-0.1     0, 0.0    -2,-0.0   0.150  74.0  -8.3-107.3-139.1   42.1   66.5   41.6
    8  542 A E  S    S-     0   0  170      1,-0.1     4,-0.1    -2,-0.1    -2,-0.1   0.348  84.9-132.3 -44.0  -5.3   41.2   70.2   42.2
    9  543 A E  C      +    0   0   38      1,-0.2     3,-0.2     2,-0.1    -1,-0.1   0.934  39.1 177.3  45.2  63.8   39.8   69.0   45.6

# 2  
Old 08-26-2013
Code:
 awk '{a=substr($0, 17, 1);a=(a~/[a-z]/)?a:"C";print substr($0,1, 16) a substr($0,18)}' file
    1    1 B M  C           0   0  203      0, 0.0     0, 0.0     0, 0.0     0, 0.0   0.000 360.0 360.0 360.0 141.9   15.4   28.8   66.1
    2    2 B A  C     +     0   0   96      2,-0.0     2,-0.1     0, 0.0     0, 0.0   0.211 360.0 128.7-112.4   7.4   18.7   30.7   66.2
    3    3 B K  C     -     0   0  170      1,-0.0     2,-0.3     0, 0.0     0, 0.0  -0.311  37.4-173.9 -67.6 143.5   20.5   27.4   65.6
    4   44 B M  G <  S+     0   0  159     -3,-2.3    -1,-0.2    -4,-0.3    -2,-0.2   0.445  80.4 133.6 -94.8   0.2   11.7   15.9   -0.7
    5   45 B L  C <   -     0   0   46     -3,-1.4    -3,-0.0    -4,-0.2     5,-0.0  -0.218  64.1-113.7 -57.2 131.3   15.2   17.0    0.3
    7  541 A E  S    S-     0   0  135     -3,-0.1    -1,-0.1     0, 0.0    -2,-0.0   0.150  74.0  -8.3-107.3-139.1   42.1   66.5   41.6
    8  542 A E  S    S-     0   0  170      1,-0.1     4,-0.1    -2,-0.1    -2,-0.1   0.348  84.9-132.3 -44.0  -5.3   41.2   70.2   42.2
    9  543 A E  C     +     0   0   38      1,-0.2     3,-0.2     2,-0.1    -1,-0.1   0.934  39.1 177.3  45.2  63.8   39.8   69.0   45.6

# 3  
Old 08-26-2013
Hi Jotne,

I tried your code . I don't get the output like you. I got the following output. All the characters in the 5th column are changing in to C.

Code:
    1    1 B M  C           0   0  203      0, 0.0     0, 0.0     0, 0.0     0, 0.0   0.000 360.0 360.0 360.0 141.9   15.4   28.8   66.1
    2    2 B A  C     +     0   0   96      2,-0.0     2,-0.1     0, 0.0     0, 0.0   0.211 360.0 128.7-112.4   7.4   18.7   30.7   66.2
    3    3 B K  C     -     0   0  170      1,-0.0     2,-0.3     0, 0.0     0, 0.0  -0.311  37.4-173.9 -67.6 143.5   20.5   27.4   65.6
    4   44 B M  C <  S+     0   0  159     -3,-2.3    -1,-0.2    -4,-0.3    -2,-0.2   0.445  80.4 133.6 -94.8   0.2   11.7   15.9   -0.7
    5   45 B L  C <   -     0   0   46     -3,-1.4    -3,-0.0    -4,-0.2     5,-0.0  -0.218  64.1-113.7 -57.2 131.3   15.2   17.0    0.3
    7  541 A E  C    S-     0   0  135     -3,-0.1    -1,-0.1     0, 0.0    -2,-0.0   0.150  74.0  -8.3-107.3-139.1   42.1   66.5   41.6
    8  542 A E  C    S-     0   0  170      1,-0.1     4,-0.1    -2,-0.1    -2,-0.1   0.348  84.9-132.3 -44.0  -5.3   41.2   70.2   42.2
    9  543 A E  C     +     0   0   38      1,-0.2     3,-0.2     2,-0.1    -1,-0.1   0.934  39.1 177.3  45.2  63.8   39.8   69.0   45.6

# 4  
Old 08-26-2013
Then your data on your PC is not the same as in example above.
Try copy data in post #1 to a file and then run the code.
Its important that all data are at same position.

Your data is at position #17 in your example.

This should give same as I have here, if not try to change 17 to some other.
Code:
awk '{print substr($0, 17, 1)}' file



G

S
S

# 5  
Old 08-26-2013
Thank you for your reply. I did with the example which I gave. But I get the same result.
# 6  
Old 08-26-2013
And the output of line in post 4 is what?
What os are you running?

And do you get the same output as I do here:
Code:
awk '{print substr($0, 14, 4)}' file
M
A
K
M  G
L
E  S
E  S
E

# 7  
Old 08-26-2013
My OS is ubuntu 12.04.

Code:
thejitha@ubuntu:~/Desktop$ awk '{print substr($0, 14, 4)}' thejitha
M   
A   
K   
M  G
L   
E  S
E  S
E   
thejitha@ubuntu:~/Desktop$ awk '{print substr($0, 17, 1)}' thejitha


G
 
S
S


Last edited by thejitha; 08-26-2013 at 04:59 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Add column and multiply its result to all elements of another column

Input file is as follows: 1 | 6 2 | 7 3 | 8 4 | 9 5 | 10 Output reuired (sum of the first column $1*$2) 1 | 6 | 90 2 | 7 | 105 3 | 8 | 120 4 |9 | 135 5 |10 | 150 Please enclose sample input, sample output, and code... (5 Replies)
Discussion started by: Sagar Singh
5 Replies

2. Shell Programming and Scripting

Split column when value in column is blank in any row

Hi Experts, In short : Need to split file when field in column 5 is blank and need to generate two file in which column 5 is blank and other in which column 5 has values along with other rows and column data My issue is i am not able to get header for column from raw file into new file which... (1 Reply)
Discussion started by: as7951
1 Replies

3. Shell Programming and Scripting

Add Column base on other Column Data

HI Guys, I want add one extra Column base on 3rd Column . Input :- M204 MS204_154 :vsDataUeMe M204 MS204_154 es:sMeasure 0 M204 MS204_154 es:90ilterCoe 9 M204 MS204_154 es:searchE9090ortTime 40 M204 MS204_154 es:servOrPrioI90HoTimer 4000 M204 MS204_154 es:ueMeajllls154545 TRUE... (5 Replies)
Discussion started by: pareshkp
5 Replies

4. Shell Programming and Scripting

Insert data in first column(if blank) from previous line first column

Dear Team I need to insert field(which is need to taken from previous line's first field) in first column if its blank. I had tried using sed but not find the way. Detail input and output file as below. Kindly help for same. INPUT: SCGR SC DEV DEV1 NUMDEV DCP ... (7 Replies)
Discussion started by: jaydeep_sadaria
7 Replies

5. Shell Programming and Scripting

Add all 4 column entries for similar column ids

Hi, I want to write a script which will add the entries in all columns for the same column id. I can do it in excel, but I need to do this for 384 columns which will come down to 96 (384/4). How can I do this iteratively A A A A B B B B C C C C 1 0 1 0 2 1 4 5 3 4 5 6 2 0 0 2 3 5 70 100 1... (7 Replies)
Discussion started by: Diya123
7 Replies

6. Shell Programming and Scripting

Remove the first character from the fourth column only if the column has four characters

I have a file as follows ATOM 5181 N AMET K 406 12.440 6.552 25.691 0.50 7.37 N ATOM 5182 CA AMET K 406 13.685 5.798 25.578 0.50 5.87 C ATOM 5183 C AMET K 406 14.045 5.179 26.909 0.50 5.07 C ATOM 5184 O MET K... (14 Replies)
Discussion started by: hasanabdulla
14 Replies

7. Shell Programming and Scripting

to add special tag to a column based on column condition

Hi All, I have following html code <TR><TD>9</TD><TD>AR_TVR_TBS </TD><TD>85000</TD><TD>39938</TD><TD>54212</TD><TD>46</TD></TR> <TR><TD>10</TD><TD>ASCV_SMY_TBS </TD><TD>69880</TD><TD>33316</TD><TD>45698</TD><TD>47</TD></TR> <TR><TD>11</TD><TD>ARC_TBS ... (9 Replies)
Discussion started by: ckwan
9 Replies

8. Shell Programming and Scripting

Need to add letters to a column and add in a new column subtracting from another column

So I have this input 1 10327 rs112750067 T C . PASS DP=65;AF=0.208;CB=BC,NCBI 1 10469 rs117577454 C G . PASS DP=2055;AF=0.020;CB=UM,BC,NCBI 1 10492 rs55998931 C T . PASS DP=231;AF=0.167;CB=BC,NCBI 1 10583 rs58108140 G A ... (3 Replies)
Discussion started by: kellywilliams
3 Replies

9. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

10. Shell Programming and Scripting

Parse 1 column and add 2nd column

I'm racking my brain on this one! :( I have a list like this: Paul 20 Paul 25 Paul 30 Frank 10 Julie 15 Julie 13 etc, etc... I've been trying to figure out a way to have the output display the name in the first column ONCE and add the numbers in the second column and display that... (2 Replies)
Discussion started by: sdlennon
2 Replies
Login or Register to Ask a Question