2

I've got two genetic datasets which have matching chromosome position IDs. I want to count how many times file 1's chromosome position IDs appear in file 2.

For example my data looks like:

File 1 (chromosome position is actually my 125th column, implied by the ...):

Gene  pval    ... Chromosome position ID
ACE   0.002   ... 01:3290834_CT_C_1
NOS   0.01    ... 03:3304593_GA_G_1
BRCA  0.004 . ... 06:6265733_GA_G_1
CYP3  0.34    ... 09:9433933_GA_G_1

File 2 (chromosome position is my first column):

Chromosome position ID  Gene  pval
01:1243933_GA_G_1       ACE   0.002
03:3304593_GA_G_1       NOS   0.01
06:6265733_GA_G_1       BRCA  0.004
09:9433933_GA_G_1       CYP3  0.34

I've found a lot of questions giving extraction of matching lines, and applied code based off those questions, but I just want to get the count of matching chromosome positions between 2 files.

Currently I'm using: 

awk -F'|' 'NR==FNR{c[$125]++;next};c[$125]' file2.csv file1.txt > file3.txt

wc -l file1.txt
wc -l file3.txt

The line count for files 1 and 3 doesn't exactly match as I'm expecting (I expect all of file 1 to be in file 2) and to be sure what's going on I need to find a way to perform a count of matching rows in the chromosome position column. If I can find a way to code 'does all of file 1 chromosome positions match/appear in file 3?' that would be ideal, but even just a count works for now.

So the output would be a number counting how many times chromosome position column $125 in file 1 has matches also with file 2 chromosome position column $1.

I am using Linux.

Improve this question
6
  • 1
    Why are you using -F'|'? Your files don't even contain any |, at least none that you show. What defined a field in these files?
    – terdon ♦
    Jan 31, 2020 at 11:15
  • Hi thank you for this, I will remove it. I'm new to linux, and just trying to piece this command together based on what I'm finding online.
    – DN1
    Jan 31, 2020 at 11:17
  • Ah, I see. The -F is setting the field delimiter, so when you use -F'|', it is expecting data separated into fields by a |, like foo|bar|baz. What separates the fields in your files? Is it tabs? Spaces? Something else?
    – terdon ♦
    Jan 31, 2020 at 11:18
  • 1
    Some good example input there, but some example output would be useful too, in order to aid those trying to answer.
    – steve
    Jan 31, 2020 at 11:21
  • Also, could you please edit your question and make the title match the content? Your title is asking for something completely different (replacing a character), but your question doesn't seem to be involving any replacement at all.
    – terdon ♦
    Jan 31, 2020 at 11:23

2 Answers 2

Reset to default
2

A solution with awk, tail, sort, join and wc.

join <(awk -F '\t' '{print $125}' file1 | tail -n +2 | sort) <(awk -F '\t' '{print $1}' file2 | tail -n +2 | sort ) | wc -l
3

Explanation.

This solution assumes that columns are tab separated. With awk are collected the 125th column of file1 and the first column of file2. tail -n +2 removes the first line of the collected results. sort is mandatory since join expects ordered files. The resulting intersecting set is supplied to wc which returns its number of lines.

Improve this answer
5
  • Thank you, is the number 3 at the end meant to be a new file?
    – DN1
    Jan 31, 2020 at 11:59
  • In problem statement you said: the output would be a number counting how many times chromosome position column $125 in file 1 has matches also with file 2 chromosome position column $1.
    – Paulo Tomé
    Jan 31, 2020 at 12:07
  • To see the resulting intersecting set remove | wc -l from the command.
    – Paulo Tomé
    Jan 31, 2020 at 12:20
  • Thank you, this seems to work for me. Oddly it gives back 0 matches, which shouldn't be possible, but I'm wondering if this isn't a code problem anymore. One file is a text file and one is a csv, would that be an issue?
    – DN1
    Jan 31, 2020 at 13:00
  • If files have different separators that will be an issue. In the CVS file replace the awk separator by -F ','.
    – Paulo Tomé
    Jan 31, 2020 at 13:03
1

You're close. Try

awk 'FNR == 1 {next}; FNR==NR {P[$125]; next} $1 in P {P[$1]++} END {for (p in P) print p, P[p]+0}' file[12]
03:3304593_GA_G_1 1
01:3290834_CT_C_1 0
09:9433933_GA_G_1 1
06:6265733_GA_G_1 1

Obviously, not all positions in file1 are found in file2.

Improve this answer
2
  • Thank you, is the file[12] representative of both files 1 and 2? I currently have different file names in actuality but I can rename them if it works like this
    – DN1
    Jan 31, 2020 at 11:59
  • file[12] is expanded by the shell to file1 file2 and will be operated upon in that sequence by awk.
    – RudiC
    Jan 31, 2020 at 13:52

You must log in to answer this question.

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