2

I´ve got the following issue for which I haven´t found a solution yet here. I´m using Python 2.7 and I like to export Pandas Series to an excel file with umlauts ("ä", "ü", "ü")

e.g. for Python 2.7:

#Python 2.7###############

# -*- coding: iso-8859-15 -*-

import pandas as pd
import csv
city = ['München', 'Nürnberg', 'Würzburg']
result = pd.Series(city)
result.to_csv(result.to_csv('Umlauts.csv', sep= ',' , encoding='iso-8859-15')

It only works for csv if I open it with Excel even though the result are in the same line and column

0,München
1,Nürnberg
2,Würzburg

Question: What is the solution for exporting this Series frame into a Excel sheet like

A1 | A2
1  |  München
2  |  Nürnberg
3  |  Würzburg

And can I transfer this solution for creating files called 'München.xls'?

Thanks for helping!

Best

monsoon
  • 35
  • 2
  • 7
  • CSV is not "an Excel file". If you want to produce actual .xls files, use one of the many libraries that do that (e.g. openpyxl). – deceze Jan 31 '17 at 14:18

3 Answers3

2

As written here, here and here you can use another engine when exporting to xls.
This works for me when the other solutions didn't:

  1. Install xlsxwriter pip install XlsxWriter
  2. Import import xlsxwriter
  3. Write using this engine instead of the default
from pandas import ExcelWriter
writer = pd.ExcelWriter('file.xlsx', engine='xlsxwriter')
df.to_excel(writer,'Sheet1')
writer.save()

where df is the DataFrame you want to export.

daco
  • 600
  • 1
  • 4
  • 14
1

Panads DataFrame.to_csv doesn't support a dialect parameter. You will have to use the csv module which supports excel dialect.

Disclaimer

Excel has issues with csv. Write the csv with UTF-8 encoding and prepend the utf8 bom b"\0xEF\0xBB\0xBF" in front of the file contents. Excel will read this file just fine.

HOWEVER

Excel cannot save csv files with utf8 encoding. Even when you open an utf8 csv, and press save without modifications, excel will re-encode it in iso-8859-15, with ?-s inserted where a unicode character couldn't be encoded.

In other words: Editing csv files with excel results in data-loss most of the time!

Tamas Hegedus
  • 28,755
  • 12
  • 63
  • 97
  • \* Excel on OS X will probably still fail… – deceze Jan 31 '17 at 14:17
  • Your last statement seems overly harsh - if you can enter a character into Excel with the keyboard, it should be able to save it! – Mark Ransom Jan 31 '17 at 14:18
  • @Mark "Should"… :) I don't know whether or not Excel really does have issues with exporting "foreign" characters to CSV, but it certainly always had a very difficult relationship with processing UTF-8 in CSVs… – deceze Jan 31 '17 at 14:20
  • @MarkRansom as long as you don't have a russian keyboard :) I think I can find a letter on my hungarian keyboard as well, like capital `Ű` – Tamas Hegedus Jan 31 '17 at 14:20
  • @ALL: Actually, I like to avoid writing csv files. I would like to export the results in mac excel files directly using the xlrw package. As far as I know it should work using xlrw package, e.g. ´u"München`. But I can´t solve it. Any ideas? Thanks for your help! – monsoon Jan 31 '17 at 14:31
1

You should overwrite your sys settings and use pandas.DataFrame.to_excel():

# Import modules
import sys
reload(sys)
sys.setdefaultencoding('UTF8')
import pandas as pd

# Your code
city = ['München', 'Nürnberg', 'Würzburg']
result = pd.Series(city)

# Transform Series to DataFrame in order to use to_excel()
d = result.to_frame()

# Specify writer
writer = pd.ExcelWriter('output.xlsx', options={'encoding':'utf-8'})

# Write the frame to excel
d.to_excel(writer,'Sheet1')

# Save the file
writer.save()

This should work.

Fabio Lamanna
  • 20,504
  • 24
  • 90
  • 122