problem with using pandas to manipulate a big text file in python

Question

i have a text file like the following small example:

small example:

0,1,2,3,4,5,6
chr1,144566,144597,30,chr1,120000,210000
chr1,154214,154245,34,chr1,120000,210000
chr1,228904,228935,11,chr1,210000,240000
chr1,233265,233297,13,chr1,210000,240000
chr1,233266,233297,58,chr1,210000,240000
chr1,235438,235469,36,chr1,210000,240000
chr1,262362,262393,16,chr1,240000,610000
chr1,347253,347284,12,chr1,240000,610000
chr1,387022,387053,38,chr1,240000,610000

I want to remove the first line and instead of comma separated, make a tab separated file. like the expected output:

expected output:

chr1    144566  144597  30  chr1    120000  210000
chr1    154214  154245  34  chr1    120000  210000
chr1    228904  228935  11  chr1    210000  240000
chr1    233265  233297  13  chr1    210000  240000
chr1    233266  233297  58  chr1    210000  240000
chr1    235438  235469  36  chr1    210000  240000
chr1    262362  262393  16  chr1    240000  610000
chr1    347253  347284  12  chr1    240000  610000
chr1    387022  387053  38  chr1    240000  610000

I am trying to do that in python using pandas. I wrote this code but does not return what I want. do you how to fix it?

import pandas
file = open('myfile.txt', 'rb')
new =[]
for line in file:
    new.append(line.split(','))
    df = pd.DataFrame(new)
    df.to_csv('outfile.txt', index=False)

Kurtis Streutker · Accepted Answer · 2018-11-03 14:10:02Z

3

import pandas as pd    
df = pd.read_csv('myfile.txt', header=0)
df.to_csv('outfile.txt', sep='\t', index=None, header=False)

edited Nov 3, 2018 at 14:10

answered Nov 3, 2018 at 14:01

Kurtis Streutker

1,34413 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fuglede Over a year ago

Looks like you'll want to throw a header=False in there as well.

Kurtis Streutker Over a year ago

^ Thanks. Didn't test it. Updated and tested and now works as expected.

AybikeA · Accepted Answer · 2018-11-03 15:19:10Z

Depending on how big your file is, avoiding Pandas and using base Python I/O could be a much more efficient idea. That way you don't have to read the whole file into memory, instead read line-by-line and dump into a new file with tab separations:

with open("myfile.txt", "r") as r:
    with open("myfile2.txt", "w") as w:
        for line in r:
            w.write("\t".join(line.split(',')))

myfile2.txt is now the tab-separated version of myfile.txt.

Collectives™ on Stack Overflow

problem with using pandas to manipulate a big text file in python

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related