This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Convert two-column txt to multiple column csv

Hi,

I need some advice. I have output file (count file) from VCF format. It looks like this:

Chr 10
protein_coding 447164
pseudogene 87457
Chr 11
protein_coding 368825
pseudogene 78131
Chr 12
protein_coding 357596
pseudogene 68176

and there are more chromosomes. I have two others files with another column names (but could differ with 1 or more fields between chromosomes). How can I convert that file to CSV or another file format. I mean, I want to create file like this:

Chr,protein_coding,pseudogene
10,447164,87457
11,368825,78131
12,357596,68176

Assuming that if some chromosome does not has for example pseudogene, than script will put empty field, e.g. for 15 chromosome:

15,132598,

Thank you in advance

bash python r conversion
• 2,508 views • link
updated by cpad0112 cpad0112 • written by misterie misterie

Thank you for explaining the problem at hand so well.

What have you tried by yourself to solve this problem? How far did you get and what specific challenges are you facing?

• 1 views • link
written by Ram Ram

I have no idea, how can I do that...

• 1 views • link
written by misterie misterie

You can use awk with RS=Chr. That will use Chr to create records, so each record would contain all data between consecutive Chrs. You can then replace each new line by a space and use $1, $2 etc to get to your result.

• 1 views • link
written by Ram Ram

Also, please see the following oddities:

  • protein_coding becomes protein_codin
  • There's a blank space preceding pseudogene. Why?
  • Your example for "15 chromosome" has entry for Chr = 16
  • Your current dataset has nothing separating Chr (the key) from the chromosome number (the value). Is this true?
  • The comma-separated format you've shown is actually semi-colon separated. You do mention "or other", so that's fine I guess, but pick a separator.

These will make a difference in the final script you develop.

• 1 views • link
written by Ram Ram

sorry, my bad. I have corrected everything.

• 1 views • link
written by misterie misterie

3 answers

Using R dplyr package:

library(dplyr)
library(tidyr) # spread: reshape long-to-wide

# example data (using @RamRS's data)
df1 <- read.table(text = "Chr 10
protein_coding 447164
pseudogene 87457
Chr 11
protein_coding 368825
pseudogene 78131
Chr 12
protein_coding 357596
pseudogene 68176
Chr 13
protein_coding 447164
Chr 14
pseudogene 87457
Chr 15", header = FALSE, stringsAsFactors = FALSE)

write.csv(
  df1 %>% 
    mutate(grp = cumsum(V1 == "Chr")) %>% 
    spread(key = "V1", value = "V2") %>% 
    select(-grp),
  file = "myOutput.csv", na = "", row.names = FALSE, quote = FALSE)

# myOutput.csv
#
# Chr,protein_coding,pseudogene
# 10,447164,87457
# 11,368825,78131
# 12,357596,68176
# 13,447164,
# 14,,87457
# 15,,
• 1 views • link
written by zx8754 zx8754

Neat trick, using a conditional cumsum as a group identifier!

• 1 views • link
written by Ram Ram

Here's a simple bash-awk script I wrote:

$ cat in.txt

Chr 10
protein_coding 447164
pseudogene 87457
Chr 11
protein_coding 368825
pseudogene 78131
Chr 12
protein_coding 357596
pseudogene 68176
Chr 13
protein_coding 447164
Chr 14
pseudogene 87457
Chr 15

$ echo "Chr,protein_coding,pseudogene" && awk 'BEGIN{RS="Chr "} {gsub(/\n/," ",$0); print $0}' in.txt | awk -F" " 'NF==5 {print $1 "," $3 "," $5} NF==3 && $2=="protein_coding" {print $1 "," $3 ","} NF==3 && $2=="pseudogene" {print $1 ",," $3} NF==1 {print $1 ",,"}'

Chr,protein_coding,pseudogene
10,447164,87457
11,368825,78131
12,357596,68176
13,447164,
14,,87457
15,,

The code makes a lot of assumptions and is as unscalable and data-dependent as code gets, but it works. It assumes missing entries would not have corresponding key (first column) entries. It assumes Chr is mandatory. It assumes no blank spaces exist in values, and that the three listed keys are the only allowed keys. I hate this code, but it works.

• 1 views • link
written by Ram Ram

I think this python script should do the trick, but I haven't tested it

import sys

data = dict(Chr= 'Chr', protein_coding= 'protein_coding', pseudogene = 'pseudogene')
for line in open(sys.argv[1]):
    if line.startswith('Chr'):
        print("{},{},{}".format(*data.values()))
        data = dict(Chr= '', protein_coding= '', pseudogene = '')
    data[line.split('\t')[0]] = line.strip().split('\t')[1]
• 1 views • link
written by WouterDeCoster WouterDeCoster

Log in to answer this question.

More posts like this