CODEWITHSUNDEEP
pythonpandas
fast tooth
fast tooth

Reputation: 2459

Pandas, to_csv () to a specific format

I want to use DecisionTree 2.2.2 to build a decision tree. https://engineering.purdue.edu/kak/distDT/DecisionTree-2.2.2.html

However, it uses this weird csv format. 

"","pgtime","pgstat","age","eet","g2","grade","gleason","ploidy"
"1",6.1,0,64,2,10.26,2,4,"diploid"
"2",9.4,0,62,1,NA,3,8,"aneuploid"
"3",5.2,1,59,2,9.99,3,7,"diploid"
"4",3.2,1,62,2,3.57,2,4,"diploid"
"5",1.9,1,64,2,22.56,4,8,"tetraploid"
"6",4.8,0,69,1,6.14,3,7,"diploid"
"7",5.8,0,75,2,13.69,2,NA,"tetraploid"
"8",7.3,0,71,2,NA,3,7,"aneuploid"
"9",3.7,1,73,2,11.77,3,6,"diploid"

My question is how can I use pandas to_csv function to save a DataFrame into this format? If not possible, can you suggest what would be the best solution?

Thanks

This is what I've tried. I convert my columns to string type: 

df.col1 = df.col1.apply(str)

and use index_label when saving: 

df.to_csv( 'filename.csv', header=True, index=True, index_label='"')

but this gives me the following: 

"""",url,class,length,volume,name,degree,pagerank
......

the first element is four quotes.

Upvotes: 1

Views: 3255

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375675

First off just to demonstrate that reading this in is fine:

In [11]: df = pd.read_clipboard(sep=',', index_col=0)

In [12]: df
Out[12]:
   pgtime  pgstat  age  eet     g2  grade  gleason      ploidy
1     6.1       0   64    2  10.26      2        4     diploid
2     9.4       0   62    1    NaN      3        8   aneuploid
3     5.2       1   59    2   9.99      3        7     diploid
4     3.2       1   62    2   3.57      2        4     diploid
5     1.9       1   64    2  22.56      4        8  tetraploid
6     4.8       0   69    1   6.14      3        7     diploid
7     5.8       0   75    2  13.69      2      NaN  tetraploid
8     7.3       0   71    2    NaN      3        7   aneuploid
9     3.7       1   73    2  11.77      3        6     diploid

You have to use quoting=csv.QUOTING_NONNUMERIC* when outputing the csv:

In [21]: s = StringIO()

In [22]: df.to_csv(s, quoting=2)  # or output to file instead

In [23]: s.getvalue()
Out[23]: '"","pgtime","pgstat","age","eet","g2","grade","gleason","ploidy"\n1,6.1,0,64,2,10.26,2,4.0,"diploid"\n2,9.4,0,62,1,"",3,8.0,"aneuploid"\n3,5.2,1,59,2,9.99,3,7.0,"diploid"\n4,3.2,1,62,2,3.57,2,4.0,"diploid"\n5,1.9,1,64,2,22.56,4,8.0,"tetraploid"\n6,4.8,0,69,1,6.14,3,7.0,"diploid"\n7,5.8,0,75,2,13.69,2,"","tetraploid"\n8,7.3,0,71,2,"",3,7.0,"aneuploid"\n9,3.7,1,73,2,11.77,3,6.0,"diploid"\n'

* QUOTING_NONNUMERIC is 2.

Now, this isn't quite what you want, since the index column is not quoted, I would just modify the index:

In [24]: df.index = df.index.astype(str)  # unicode in python 3?

In [25]: s = StringIO()

In [26]: df.to_csv(s, quoting=2)

In [27]: s.getvalue()
Out[27]: '"","pgtime","pgstat","age","eet","g2","grade","gleason","ploidy"\n"1",6.1,0,64,2,10.26,2,4.0,"diploid"\n"2",9.4,0,62,1,"",3,8.0,"aneuploid"\n"3",5.2,1,59,2,9.99,3,7.0,"diploid"\n"4",3.2,1,62,2,3.57,2,4.0,"diploid"\n"5",1.9,1,64,2,22.56,4,8.0,"tetraploid"\n"6",4.8,0,69,1,6.14,3,7.0,"diploid"\n"7",5.8,0,75,2,13.69,2,"","tetraploid"\n"8",7.3,0,71,2,"",3,7.0,"aneuploid"\n"9",3.7,1,73,2,11.77,3,6.0,"diploid"\n'

As required.

Upvotes: 3

Related Questions