Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-26T11:55:02.920Z
Answers.Text: This worked perfectly thanks so much!
Answers.UserDisplayName: Stanley Ho
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-26T10:32:27.757Z
Answers.LastActivityDate: 2020-02-26T10:37:34.387Z
Answers.LastEditDate: 2020-02-26T10:37:34.387Z
Answers.UserDisplayName: bli
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-25T17:20:03.553Z
Answers.LastActivityDate: 2020-02-26T12:40:23.703Z
Answers.LastEditDate: 2020-02-26T12:40:23.703Z
Answers.UserDisplayName: Kamil S Jaron
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-25T16:06:03.143Z
Comments.Text: I'm voting to close this question as off-topic because there is nothing related to bioinformatics in this post
Comments.UserDisplayName: Ram RS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-25T16:38:25.247Z
Comments.Text: Hi @stanleyHo, could you provide more detail, what have you tried and what biological context is your problem?
Comments.UserDisplayName: M__
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-25T17:10:07.267Z
Comments.Text: 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
Comments.UserDisplayName: Stanley Ho
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-25T17:21:23.710Z
Comments.Text: Hi @StanleyHo, note you can [edit] your questions to make them clearer.
Comments.UserDisplayName: Kamil S Jaron
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-25T19:48:00.047Z
Comments.Text: Use Python's pandas ...
Comments.UserDisplayName: M__
Tags: <python><bash><awk>
Title: Trying to divide number in fourth TSV column by a certain number according to its corresponding identifier in the first column
ThreadText: # 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 @username_0, 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.
  
  
  ---
  
  