python pandas csv exporting

Question

I've problem exporting a dataframe to CSV file.

Data types are String and Float64 values like this:

In [19]: segmenti_t0
Out[19]:
SEGM1  SEGM2
AD     P.S.         8.3
       SCREMATO     0.6
CRE    STD          1.2
FUN    INTERO       0.0
       P.S.         2.0
       SCREMATO     0.0
NORM   INTERO      13.1
       P.S.        69.5
       SCREMATO     5.2
Name: Quota volume_t0

I try to export this dataframe with this command:

IN [20]: segmenti_t0.to_csv('C:Users\MarioRossi\prova.csv', sep=";")

When I try to open it with Excel or I try to import it in excel from the csv file with formatting parameters I obtain really strange formatting for float values like 69.5000 or 5.2.0000000 or date times formatting too like this:

NORM    INTERO  13.01
NORM    P.S.    69.05.00
NORM    SCREMATO    5.02

Consider that I m using European format ("," as decimal as I use to import the original raw data from csv files).

Please help me: I developed a software (with GUI and so on) and I cant deliver it for that reason!

Thanks

What does the csv look like when opened with a text editor (e.g. in notepad) — Andy Hayden, Jan 08 '13 at 15:36
like this: AD;P.S.;8.3 AD;SCREMATO;0.6 CRE;STD;1.2 FUN;INTERO;0.0 FUN;P.S.;2.0 FUN;SCREMATO;0.0 NORM;INTERO;13.1 NORM;P.S.;69.5 NORM;SCREMATO;5.2 — Manuel Zompetta, Jan 08 '13 at 15:58
if I save it from notepad and I try to open it with excel I obtain the same problem: AD P.S. 8.03 AD SCREMATO 0.06 CRE STD 1.02 FUN INTERO 0.00 FUN P.S. 2.00 FUN SCREMATO 0.00 NORM INTERO 13.01 NORM P.S. 69.05.00 NORM SCREMATO 5.02 — Manuel Zompetta, Jan 08 '13 at 16:02

Andy Hayden · Accepted Answer · 2013-01-08T16:00:45.430

6

You should use the to_excel DataFrame method:

# first convert Series to DataFrame
df_segmenti_t0 = DataFrame(segmenti_t0)

# save as excel spreadsheet
df_segmenti_t0.to_excel('prova.xls')

edited Jan 08 '13 at 16:00

answered Jan 08 '13 at 15:40

Andy Hayden

359,921
101
625
535

I obtain this: In [3]: segmenti_t0.to_excel('prova.csv') --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) C:\Windows\system32\ in () ----> 1 segmenti_t0.to_excel('prova.csv') AttributeError: 'Series' object has no attribute 'to_excel' – Manuel Zompetta Jan 08 '13 at 15:52
@ManuelZompetta I missed it was a series not a DataFrame, please see updated answer. `to_excel` is a DataFrame method, so you must convert the Series to a DataFrame (`segmenti_t0 = pd.DataFrame(segmenti_t0)`). – Andy Hayden Jan 08 '13 at 16:02
Now I obtain this: C:\Python27\lib\site-packages\pandas\io\parsers.pyc in __init__(self, path) 2108 self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD') 2109 else: -> 2110 from openpyxl.workbook import Workbook 2111 self.book = Workbook()#optimized_write=True) 2112 – Manuel Zompetta Jan 08 '13 at 16:08
and... ImportError: No module named openpyxl.workbook maybe the library is not properly installed? I work on Enthought distribution 7.3 Thanks again! – Manuel Zompetta Jan 08 '13 at 16:14
@ManuelZompetta it looks like `openpyxl` is not in [Enthought Free](http://www.enthought.com/products/epdlibraries.php), you'll need to install it separately... – Andy Hayden Jan 08 '13 at 16:26
Ok. I'll try asap. Thanks again. Do you know how to install to a specific Python distribution? I've many installed on my pc and I do not use a lot of imported libraries... M – Manuel Zompetta Jan 08 '13 at 16:30
Great thanks, I have installed with: python install setup.py and, thanks God, library has been installed in the correct folder of the Enthought distribution: '/Library/Frameworks/Python.framework/Versions/7.3/lib/python2.7/site-packages' I have anyway many Python installation on my system (Mac OSX 10.8) and I would like to know how is possible to install to a specific Python installation (ex. Python 3.3): I ve read documentation about install command but I had not understood well or python install setup.py +path of packages site do not function. Thanks Hayden – Manuel Zompetta Jan 08 '13 at 19:44
Now if I import openpyxl from the pylab shell the situation is ok but if I write your instructions for the conversion to data frame and then: segmenti_t0.to_excel('/Users/manuelzompetta/Desktop/prova.xls') I obtain this error: ImportError: No module named xlwt Sob.... M – Manuel Zompetta Jan 08 '13 at 19:56
@ManuelZompetta you need to install that too (it shouldn't go much deeper, and once you've installed all these you won't need to again!) :) – Andy Hayden Jan 08 '13 at 20:06
Great, now the file is created but with number in text format with "." instead of "," for decimals. I think I have to find in the openpyxl documentation the correct parameters to set the decimals... M – Manuel Zompetta Jan 08 '13 at 20:19
Finally the situation is ok. I have just to understand how to export many dataframes on the same excel workbook... Not easy but I feel close to the arrival... HAve you any idea about that? Is better I write another post? Dear Hayden, I m not a professional programmer but just a marketers: programming its nice but hard...))) – Manuel Zompetta Jan 08 '13 at 22:01
Upvote and accept if this answers been helpful, and ask this as another question :) These comments are getting quite long! – Andy Hayden Jan 08 '13 at 22:03
I asked [this question](http://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet) myself, as I am not sure. Hopefully someone will know :) – Andy Hayden Jan 08 '13 at 23:14
Finally I arrive to the solution in the night. Tks. More more difficult is to write many small dataframes on the same sheet... I have to write an answer for that...))) – Manuel Zompetta Jan 09 '13 at 10:05

python pandas csv exporting

1 Answers1