<p>Python dictionaries are indeed a good tool for that.</p>

<p>Here is a script that takes 2 arguments (the file containing the counts and the file containing the genome length info), and prints the input data, with an added column containing coverage:</p>

<pre><code>#!/usr/bin/env python3
import sys

counts_fname = sys.argv[1]
genome_lengths_fname = sys.argv[2]

genome_length_dict = {}
with open(genome_lengths_fname) as genome_lengths_f:
    # Checking the header line, for extra safety
    header = genome_lengths_f.readline().strip().split("\t")
    assert header[0] == "ID"
    assert header[1] == "y"
    # Reading the data lines
    for line in genome_lengths_f:
        [genome_id, genome_length] = line.strip().split("\t")
        genome_length_dict[genome_id] = int(genome_length)

with open(counts_fname) as counts_f:
    print("ID\tInfo\tInfo2\tx\tx/y")
    # Checking the header line, for extra safety
    header = counts_f.readline().strip().split("\t")
    assert header[0] == "ID"
    assert header[1] == "Info"
    assert header[2] == "Info2"
    assert header[3] == "x"
    # Reading the data lines
    for line in counts_f:
        [genome_id, info1, info2, counts] = line.strip().split("\t")
        # /!\ It would be different in python 2:
        # different print and division behaviours
        print(
            genome_id, info1, info2, int(counts),
            int(counts) / genome_length_dict[genome_id],
            sep="\t")

sys.exit(0)
</code></pre>

<p>Testing it:</p>

<pre><code>$ ./coverage.py x.tsv y.tsv
ID  Info    Info2   x   x/y
M80428  info    info    1000    0.0366152832192157
T83241  info    info    8400    0.22450288646568314
E81292  info    info    10200   0.7785665216395695
</code></pre>

CC BY-SA 4.0
2020-02-26T11:55:02.920Z
This worked perfectly thanks so much!
Stanley Ho
CC BY-SA 4.0
2020-02-26T10:32:27.757Z
2020-02-26T10:37:34.387Z
2020-02-26T10:37:34.387Z
bli
<p>I would do it in R:</p>

<pre><code>tab1 &lt;- read.table('file1.tsv', header = T, stringsAsFactors = F)
tab2 &lt;- read.table('file2.tsv', header = T, stringsAsFactors = F)
merged_tav &lt;- merge(reads_tab, genome_len_tab)
merged_tav<span class="math-container">$z &lt;- merged_tav$</span>x / merged_tav$y
merged_tav
</code></pre>

<p>returns</p>

<pre><code>      ID Info Info2     x     y          z
1 E81292 info  info 10200 13101 0.77856652
2 M80428 info  info  1000 27311 0.03661528
3 T83241 info  info  8400 37416 0.22450289
</code></pre>

<p>note that only IDs that are in both tables will be preserved.</p>

CC BY-SA 4.0
2020-02-25T17:20:03.553Z
2020-02-26T12:40:23.703Z
2020-02-26T12:40:23.703Z
Kamil S Jaron
<p>I was wondering if anyone could help with my little issue in manipulating tsv files in order to calculate estimated coverage across many tsv files. </p>

<p>I have tsv files that look something like this:</p>

<pre><code>ID        Info    Info2  x
M80428    info    info   1000 
T83241    info    info   8400
E81292    info    info   10200
</code></pre>

<p>I also have another tsv file with all possible IDs and a corresponding number like so:</p>

<pre><code>ID        y
M80428    27311
E11123    46531
P38163    14213
A34197    12892
T83241    37416
Q21345    27321
E81292    13101
</code></pre>

<p>x in the first tsv are estimated read counts and the y in the second is the genome length of each ID.</p>

<p>I would like to divide the "x" by the "y" in the second tsv that has the corresponding ID in order to get the estimated coverage of each hit. e.g. the first row would be 1000/27311, second row 8400/37416. I would then want this to be written to the fifth column of the tsv. </p>

<p>I am working in bash shell mostly here, and think that the way I would probably do this is with awk and by making the second tsv an associative array but I can't work out how exactly to do so. The python package dict seems like another option but my Python skills are practically zero. </p>

<p>Thanks for the help in advance!</p>

CC BY-SA 4.0
2020-02-25T16:06:03.143Z
I'm voting to close this question as off-topic because there is nothing related to bioinformatics in this post
Ram RS
CC BY-SA 4.0
2020-02-25T16:38:25.247Z
Hi @stanleyHo, could you provide more detail, what have you tried and what biological context is your problem?
M__
CC BY-SA 4.0
2020-02-25T17:10:07.267Z
x in the first tsv are estimated read counts and the y in the second is the genome length of each ID. Trying to divide them to get estimated coverage.

I have tried to set the second tsv as variables and then tried to awk out the columns with divisions but i'm stuck at how to link the ID and y together.
Something like this:

cat genome-size.txt | while read eachline;
    do echo $eachline;
 vname=`echo $eachline| awk '{print $1}'`;
 div=`echo $eachline| awk '{print $2}'`
 cat .tsv | awk -v div="$div" '{FS ="\t"} {OFS ="\t"} {print $1,$2,$3,$4,$4/div}' > new.tsv;
    done
Stanley Ho
CC BY-SA 4.0
2020-02-25T17:21:23.710Z
Hi @StanleyHo, note you can [edit] your questions to make them clearer.
Kamil S Jaron
CC BY-SA 4.0
2020-02-25T19:48:00.047Z
Use Python's pandas ...
M__
<python><bash><awk>
Trying to divide number in fourth TSV column by a certain number according to its corresponding identifier in the first column
# Trying to divide number in fourth TSV column by a certain number according to its corresponding identifier in the first column

(Asked by: username_0 on 2020-02-25)

I was wondering if anyone could help with my little issue in manipulating tsv files in order to calculate estimated coverage across many tsv files. 
I have tsv files that look something like this:
ID        Info    Info2  x
M80428    info    info   1000 
T83241    info    info   8400
E81292    info    info   10200

I also have another tsv file with all possible IDs and a corresponding number like so:
ID        y
M80428    27311
E11123    46531
P38163    14213
A34197    12892
T83241    37416
Q21345    27321
E81292    13101

x in the first tsv are estimated read counts and the y in the second is the genome length of each ID.
I would like to divide the "x" by the "y" in the second tsv that has the corresponding ID in order to get the estimated coverage of each hit. e.g. the first row would be 1000/27311, second row 8400/37416. I would then want this to be written to the fifth column of the tsv. 
I am working in bash shell mostly here, and think that the way I would probably do this is with awk and by making the second tsv an associative array but I can't work out how exactly to do so. The python package dict seems like another option but my Python skills are practically zero. 
Thanks for the help in advance!

**Tags:** python, bash, awk
> **Comments:**
> **username_1**: I'm voting to close this question as off-topic because there is nothing related to bioinformatics in this post  
> **username_2**: Hi @stanleyHo, could you provide more detail, what have you tried and what biological context is your problem?  
> **username_0**: x in the first tsv are estimated read counts and the y in the second is the genome length of each ID. Trying to divide them to get estimated coverage.  I have tried to set the second tsv as variables and then tried to awk out the columns with divisions but i'm stuck at how to link the ID and y together. Something like this:  cat genome-size.txt | while read eachline;     do echo $eachline;  vname=`echo $eachline| awk '{print $1}'`;  div=`echo $eachline| awk '{print $2}'`  cat .tsv | awk -v div="$div" '{FS ="\t"} {OFS ="\t"} {print $1,$2,$3,$4,$4/div}' > new.tsv;     done  
> **username_3**: Hi @StanleyHo, note you can [edit] your questions to make them clearer.  
> **username_2**: Use Python's pandas ...  

---

## 2 answers

### Answer by username_4 on 2020-02-26. Score: 3

Python dictionaries are indeed a good tool for that.
Here is a script that takes 2 arguments (the file containing the counts and the file containing the genome length info), and prints the input data, with an added column containing coverage:
#!/usr/bin/env python3
import sys

counts_fname = sys.argv[1]
genome_lengths_fname = sys.argv[2]

genome_length_dict = {}
with open(genome_lengths_fname) as genome_lengths_f:
    # Checking the header line, for extra safety
    header = genome_lengths_f.readline().strip().split("\t")
    assert header[0] == "ID"
    assert header[1] == "y"
    # Reading the data lines
    for line in genome_lengths_f:
        [genome_id, genome_length] = line.strip().split("\t")
        genome_length_dict[genome_id] = int(genome_length)

with open(counts_fname) as counts_f:
    print("ID\tInfo\tInfo2\tx\tx/y")
    # Checking the header line, for extra safety
    header = counts_f.readline().strip().split("\t")
    assert header[0] == "ID"
    assert header[1] == "Info"
    assert header[2] == "Info2"
    assert header[3] == "x"
    # Reading the data lines
    for line in counts_f:
        [genome_id, info1, info2, counts] = line.strip().split("\t")
        # /!\ It would be different in python 2:
        # different print and division behaviours
        print(
            genome_id, info1, info2, int(counts),
            int(counts) / genome_length_dict[genome_id],
            sep="\t")

sys.exit(0)

Testing it:
$ ./coverage.py x.tsv y.tsv
ID  Info    Info2   x   x/y
M80428  info    info    1000    0.0366152832192157
T83241  info    info    8400    0.22450288646568314
E81292  info    info    10200   0.7785665216395695

> **Comments:**
> **username_0**: This worked perfectly thanks so much!  

---

### Answer by username_3 on 2020-02-25. Score: 1

I would do it in R:
tab1 <- read.table('file1.tsv', header = T, stringsAsFactors = F)
tab2 <- read.table('file2.tsv', header = T, stringsAsFactors = F)
merged_tav <- merge(reads_tab, genome_len_tab)
merged_tav$z <- merged_tav$x / merged_tav$y
merged_tav

returns
      ID Info Info2     x     y          z
1 E81292 info  info 10200 13101 0.77856652
2 M80428 info  info  1000 27311 0.03661528
3 T83241 info  info  8400 37416 0.22450289

note that only IDs that are in both tables will be preserved.


---

