1

I have the following text file as input (infile.txt)

A foo 3.0
A bar 3.1
B foo 3.0
B bar 3.1

And with the following code 

import pandas as pd

infile="infile.txt"
df = pd.io.parsers.read_table(infile,header=None,sep=" ")
df.columns = ['sample','celltype','score']
dfp = df.pivot(index='sample',columns='celltype',values='score')
dfp_prc =  dfp.applymap(lambda x: x * 100)
dfp_prc.to_csv("out.txt",sep=" ", index=True)

it produces this CSV:

sample bar foo
A 310.0 300.0
B 310.0 300.0

What I want to do then, is given a list:

mlist = ['sample','XXX','YYY']

I would like to obtain final CSV file that looks like this:

sample XXX YYY
sample bar foo
A 310.0 300.0
B 310.0 300.0

What's the convenient way to do it in Pandas?

pdubois
  • 7,640
  • 21
  • 70
  • 99

1 Answers1

3

to_csv accepts a filename or a buffer (opened file) as well, so instead of

dfp_prc.to_csv("out.txt",sep=" ", index=True)

use

import csv

...

mlist = ['sample','XXX','YYY']
sep = ' '
with open('out.txt', 'w') as f:
    csv.writer(f, delimiter=sep, lineterminator='\n').writerow(mlist)
    dfp_prc.to_csv(f, sep=sep, index=True)

So you open a file, use csv.writer to insert a line and then into the same opened files you export the DataFrame.

eumiro
  • 207,213
  • 34
  • 299
  • 261
  • @euromiro: Thanks. After writing at the end of first line, there is ^M. Is there a way to remove it? – pdubois Jan 30 '15 at 07:55
  • 1
    @pdubois - yes, add `lineterminator` parameter to the `csv.writer` - see my edited answer and documentation here: https://docs.python.org/2/library/csv.html#dialects-and-formatting-parameters – eumiro Jan 30 '15 at 07:57