Filtering using awk returns empty files

user_9606753 0
Viewed 143

I have a similar problem to this question: How to do filtering of multiple files in a directory using awk?

The solution in the answers of the question above does not work for me.

I have tab-delimited txt files (all in folder Observation_by_pracid). For each file, I want to create a new file that only contains rows with a specific value in column $9 (medcodeid). The specific values are to be found in medicalcode_list.txt. There is no error, however it returns only empty files.

Codelist

medcodeid   
2576        
3199

Format of input files

patid           consid      ... medcodeid   
500470520002    3062539302  ... 2576    
951924020002    3062538414  ... 310803013       
503478020002    3061587464  ... 257619018       
951924020002    3062537807  ... 55627011        
503576720002    3062537720  ... 3199

Desired output

patid           consid      ... medcodeid       
500470520002    3062539302  ... 2576    
503576720002    3062537720  ... 3199

My code

mkdir HBA1C_observation_bypracid
awk '
    NR==FNR {mlist[$1]; next }
    FNR==1 {close(out); out="HBA1C_observation_bypracid/HBA1C_" FILENAME }
    ($9 in mlist) { print > out } 
' PATH/medicalcode_list.txt *.txt

Solution

mkdir HBA1C_observation_bypracid
awk '
    BEGIN{ FS=OFS="\t" }
    NR==FNR {mlist[$1]; next }
    FNR==1 {close(out); out="HBA1C_observation_bypracid/HBA1C_" FILENAME }
    ($9 in mlist) { print > out } 
' PATH/medicalcode_list.txt *.txt

Adding "BEGIN..." solved my problem.

awk
2 Answers

You can join two files on a column using join.

  • Files must be sorted on the joined column. To perform a numerical sort on a column, use sort this way, where N is the column number:

      sort -kN -n FILE

  • You also need to get ride of the first line (column names) of each files. You can use tail command the way below, where N is the number of line from which you want to output the content (so 2nd line):

    tail -n +N

  • ... But still need to display the column values:

    head -n 1 FILE

  • To join two files f1 and f2, on the fields c1 of f1 and c2 of f2, and output fields y of files x:

    join -1 c1 -2 c2 f1 f2 -o "x.y, x.y"

Working sample:

head -n 1 input_file
for input_file in *.txt ; do
    join -1 1 -2 9 -o "2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9" \
         <(tail -n +2 PATH/medicalcode_list.txt | sort -k1 -n) \
         <(tail -n +2 "$input_file" | sort -k3 -n)
done

Result (for the input file you gave):

patid           consid      ... medcodeid   
500470520002 3062539302 ... 2576
503576720002 3062537720 ... 3199

Note: the column names arent aligned with the values. Don't know if it's a prerequisite. You can format the display with printf command.

user_43753270

Personally I think it would be simpler to loop over in the shell (understanding that this will reread the code list more than once), with a simpler awk function that you should be able to test and debug. Something like:

for file in *.txt; do
    awk 'FNR == NR { mlist[$1] } FNR != NR && ($9 in mlist) { print }' \
       PATH/medicalcode_list.txt "$file" > HBA1C_observation_bypracid/HBA1C_"$file"
done

You should be able to start without the redirection to make sure that for a single file, you get the results printed to the terminal that you were expected. If you don't there might be some incorrect assumption about the files.

Another option would be to write a separate awk script that writes the code to hard-code the list in another awk script. Also gives the advantage to check the contents of the variable mlist.

printf 'BEGIN {\n%s\n}\n $9 in mlist { print }' \
    "$(awk '{ print "mlist[" $1 "]" }' PATH/medicalcode_list.txt)" > filter.awk

for file in *.txt; do
   awk -f filter.awk "$file" > HBA1C_observation_bypracid/HBA1C_"$file"
done
user_59321840
Related