How to replace all values (all NAs) in a column with numeric part of the file name?

Question

I have multiple (22) files that are named like this:

chr1.out, chr2.out...,chr22.out

each of those files has 46 columns and multiple lines

The first 6 columns and 6 rows in one of those files looks like this:

  alternate_ids      rsid chromosome position alleleA alleleB index
     rs4814683 rs4814683         NA     9795       G       T     1
     rs6076506 rs6076506         NA    11231       T       G     2
     rs6139074 rs6139074         NA    11244       A       C     3
     rs1418258 rs1418258         NA    11799       C       T     4
     rs7274499 rs7274499         NA    12150       C       A     5
     rs6116610 rs6116610         NA    12934       G       A     6

Let's say this is in file chr1.out

what I would like to do is to replace all NAs in column chromosome with 1.

so it would look like this:

  alternate_ids      rsid chromosome position alleleA alleleB index
     rs4814683 rs4814683         1     9795       G       T     1
     rs6076506 rs6076506         1    11231       T       G     2
     rs6139074 rs6139074         1    11244       A       C     3
     rs1418258 rs1418258         1    11799       C       T     4
     rs7274499 rs7274499         1    12150       C       A     5
     rs6116610 rs6116610         1    12934       G       A     6

I would like to do the same for each of those 22 files. So chr2.out get 2 in 3rd column, chr3.out get's 3 in 3rd column etc

Answer 1

Using a bash script:

#!/bin/bash

tmp_d=$(mktemp -q -d -t 'replace.XXXXX' || mktemp -q -d)

for f in chr*.out; do
    tmp_f="${tmp_d}/$f"
    n="${f#chr}"
    n="${n%.out}"
    awk -v n="$n" '$3 == "NA" { $3=n }1' "$f" > "$tmp_f"
    mv "$tmp_f" "$f"
done

rm -r "$tmp_d"

First we make a tmp directory as we will be creating tmp files

Then we loop through each chr*.out file.

• Create a variable for this file in the tmp directory
• remove the chr prefix
• remove the .out suffix
• awk will then replace any NA in the third column with the number extracted from the filename and save that to the tmp file
• replace the original file with the tmp file

After the loop finishes we remove the tmp directory.

all the tmp stuff can be avoided if you have GAWK which can use the -i in place option

Answer 2

I suggest one sed script to handle all files.

sed -i 's/         NA  /          1  /' chr{1..22}.out