I have file 1 which only contains one column which is list of genes and file 2 which has a longer list of genes and the genes' statistical data. I am trying to find the statistical data which matches the genes in file 1 and put that in a new file 3. In specific file 2 has 33 columns and the only statistical data I want is in column 29, however I've currently been writing code to find file 1 gene's information for every column as I am still stuck on that alone.

For example my data looks like:

File 1:


File 2:

gene_id   Gene    pval    beta    maf    ...     qval   ...
12345     ACE     0.01    300     100            0.0008
67890     UMOD.1  0.002   400     45             0.047
00008     HER.2   0.02    123     98             0.56
34587     CICP3   0.05    55      12             0.77
67859     HER     0.003   600     33             0.234

The 29th column I want is qval and I've used ... to imply more columns before and after column 29, but I am inexperienced with stackexchange and if there's a way for me to provide larger data in full for a better example let me know and I will do it immediately.

Using these example datasets, the output file 3 would look like:

Gene   qval
ACE    0.0008
HER.2  0.56

I have tried code that I thought would give me all columns for these 2 matching genes and it is currently giving me an empty output file:

awk 'BEGIN{FS=OFS="\t"} {sub(/\.[0-9]+$/,"",$2)} FNR==NR{$30="";sub(/[[:space:]]+$/,"");a[$2]=$0;next} $1 in a{print a[$2],$NF}' file2.txt   file1.txt > file3.txt

I have also tried another method, to more specifically get column 29, which runs but the output matches only give some of my matched genes data, whilst others which should have matching data are left with 'NA':

awk '{printf("%s\t%s\n", $2, $29)}' originalfile2.txt > file2.txt

awk '{
k[1] in a {                 
    print $0 "\t" a[k[1]]   
}'  file1.txt file2.txt  > file3.txt

awk '{$3=""; print $0}' file3.txt | column -t > file3.txt 
sed -e '1i\Gene \ qval' file3.txt | column -t > file3.txt
  • 2
    Are you looking for awk -v OFS='\t' 'FNR == NR {a[$0]++; next}; $2 in a {print $2, $29}' file1 file2?
    – muru
    Jan 28 '20 at 10:08
  • Hi thank you for this, this unfortunately also produces an empty file3
    – DN1
    Jan 28 '20 at 10:13
  • Sure you don't have DOS line terminators (<CR> = \r = ^M = 0x0D) in file1? remove and try again...
    – RudiC
    Jan 28 '20 at 10:23

Your list of gene names is a DOS text file, is it not? This means that there's an extra carriage-return character at the end of each gene name. This means that no name matches between the files. This means that your output is empty.

Convert the gene list to a Unix text file using dos2unix. Then use the code given by muru in comments (which assumes that the second file is tab-delimited):

awk -v OFS='\t' 'FNR == NR {a[$0]++; next}; $2 in a {print $2, $29}' file1 file2 >file3

Alternatively, make awk strip off carriage returns from the end of each line:

awk -v OFS='\t' '{ sub("\r$", "") } FNR == NR {a[$0]++; next}; $2 in a {print $2, $29}' file1 file2 >file3
  • Thank you for this, running dos2unix completes but with running the given code it still produces an empty file, but I will keep trying
    – DN1
    Jan 28 '20 at 10:23
  • @DN1 Is your file actually tab-delimited?
    – Kusalananda
    Jan 28 '20 at 10:24
  • @DN1 do you have spaces at the end of lines in file1? If so, try a[$1]++ instead of a[$0]++
    – muru
    Jan 28 '20 at 10:25
  • Thank you both, the files should be definitely given in a tab-delimited format, but I will double check them now. Unfortunately adding a[$0]++ also gives an empty file
    – DN1
    Jan 28 '20 at 10:31
  • 1
    I had a typo, this fix seems to now have it all working - thank you!
    – DN1
    Jan 28 '20 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.