0

I have 2 files as follows:

# File 1 has 3 columns:
SampleName   Type     Position
5R01924      ABC      92184093
5R01028      ABC      15589809
5R01092      ABC      48759348
# File 2 has many columns. 
# Column 2 is the Position 
# Columns 5 to end are the different SampleNames
A   Position   B    C    5R01924       5R01028       5R01092 
1   15589809   2    3    D:E:F:G       H:I:J:K       L:M:N:O
1   92184093   2    3    P:Q:R:S       T:U:V:W       X:Y:Z:AA
1   48759348   2    3    AB:AC:AD:AE   AF:AG:AH:AI   AJ:AK:AL:AM

I would like to either add a 4th column to the first file, or duplicate file 1 and add the 4th column to this new third file.

The 4th column should contain the correct piece of information extracted from file 2, using the index from file 1. So row 1 of file 1 says to go to row 2 of file 2 and extract the information from column 5 (5R01924). However, I only want the part R section of this information. So the output should look like:

# file 3 or append column 4 to file 1
SampleName   Type     Position     Output
5R01924      ABC      92184093     R
5R01028      ABC      15589809     J
5R01092      ABC      48759348     AL

So far I have something like this:

while IFS=" " read -r value1 value2 value3
do
        awk '$2 == "${value3}" {
        # find column # from name of value 1 (save as col variable) 
        print split(${col},a,":"), a[3]
        }' file2 >> file3.txt
done
# where does file 1 go? 

Please help :)

2
  • What should happen for cases where the key/common value from one file doesn't exist in the other one?
    – Ed Morton
    Commented Jan 22, 2021 at 21:08
  • It can just be blank in the output column, or be marked with N/A
    – Sarah
    Commented Jan 22, 2021 at 21:33

3 Answers 3

1

awk can handle this on its own: this is the file "combine.awk"

# first file, first line
FILENAME == ARGV[1] && FNR == 1  {
    for (i=5; i<=NF; i++) head[i] = $i
}

# first file,  subsequent lines
FILENAME == ARGV[1] && FNR > 1 {
    for (i=5; i<=NF; i++) {
        split($i, f, /:/)
        value[$2, head[i]] = f[3]
    }
}

# second file
FILENAME == ARGV[2] {
    print $0, (FNR == 1 ? "Output" : value[$3, $1])
}

And then you invoke it like this

awk -f combine.awk file2 file1 > file3

And the output file contains

SampleName   Type     Position Output
5R01924      ABC      92184093 R
5R01028      ABC      15589809 J
5R01092      ABC      48759348 AL
4
  • It's taking a long time to run so I'm not sure if something is wrong. I will check once complete and let you know if it works. Thanks for your suggestions.
    – Sarah
    Commented Jan 22, 2021 at 21:28
  • How many rows (lines) by how many columns (fields) are your input files?
    – Ed Morton
    Commented Jan 22, 2021 at 21:38
  • 1
    Thanks Ed, good tip. And I did test the space after the comma, it does not insert a space into the array key. Commented Jan 22, 2021 at 22:05
  • Thanks, this works! I had to reformat my files, that's why it was getting stuck. @EdMorton the dimensions of file 2 is ~50 columns by 500,000 rows. File 1 is 3 rows. Once I got it working, it only took a few mins to run.
    – Sarah
    Commented Jan 25, 2021 at 20:50
0

There's no need to invoke bash for file reading purposes.

We can do it all within awk as shown here:

### sanitize inputs
for f in ./File1 ./File2; do
  dos2unix "$f"
done

awk '
  !NF || $1 ~ /^#/ {next}
  NR==FNR {
    for (i=5; i<=NF; i++)
      if (NR > 1) {
        split($i, t, /:/)
        a[sample[i-4],$2] = t[3]
        split("", t, ":")
      }
      else sample[i-4] = $i
    next
  }
  {
    k = $1 SUBSEP $3
    f = FNR == 1  ? "Output" \
      : !(k in a) ? "N/A"    \
      : a[k]
    print $0, f
  }
' File2 OFS="\t" File1

Output:

SampleName   Type     Position  Output
5R01924      ABC      92184093  R
5R01028      ABC      15589809  J
5R01092      ABC      48759348  AL
0

first, your question is complicated, then could not expect a simple answer.

awk 'BEGIN{IFS="[[:space:]]+";OFS=" ";} NR>1 && NR==FNR {Trr[$3]=$0;Arr[$1]=$3;next} NR>FNR && FNR==1 {for(key in Arr) {for(i=5;i<=NF;i++) {if(key==$i) {nArr[key]=i;break}}}} NR>FNR && FNR>1 {for(tKey in Trr) {if($2==tKey) {for(aKey in Arr) {if(Arr[aKey]==$2) {i=nArr[aKey];Trr[tKey]=Trr[tKey]" "$i;break}}}}}END{for(key in Trr) print Trr[key]}' A B | tr ':' ' ' | awk -F'[[:space:]]+' '{print $1,$2,$3,$6}'

explanation of above:

  • IFS and OFS: input and output file(s) separator.
  • [[:space:]]+: as your file separator has more than one space, you should use it.
  • NR is row number start from one to ...
  • FNR: is is file row number. if you give multiple filenames to awk, NR go ahead from 1 but, FNR reset to one by file changing
  • next,break are clear
  • {}: you should use it in each block to prevent any confusing of the interpreter.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .