Python – Pandas dataframe to excel gives “file is not UTF-8 encoded”

dataframepandaspythonutf-8

I'm working on lists that I want to export into an Excel file.

I found a lot of people advising to use pandas.dataframe so that's what I did. I could create the dataframe but when I try to export it to Excel, the file is empty, there is just the following message:

" Error! [file_pathway] is not UTF-8 encoded. Saving disabled. See console for more details".

I didn't see any more info on the console, and every example I found on the Internet leads to the same error message.

The different lists I'm using contain different types of data. So I try to convert every element I could into a UTF-8 encoded element. I couldn't do it for the "float" list nor for the "NoType" list.

After that, here is what I wrote :

d = {'Dataset_name': dataset_names, 'Parameter_name': para_names, 'Parameter_amount': para_amounts, 'Parameter_unit': para_units, 'Parameter_variable': para_variables, 'Parameter_formula': para_formulas}
df = pd.DataFrame(data=d)

from pandas import ExcelWriter
writer = ExcelWriter('Ocelot_Export.xlsx')
df.to_excel(writer, encoding='utf8', index=False)
writer.save()

The dataframe is correct, as I can print it in Jupyter Notebook. The only problem is the exportation. Please let me know if you have any idea about what's wrong.

Best Answer

The cause of this problem is likely just that jupyter is unable to display .xlsx files. Try downloading the file from jupyter (checkbox next to the file name -> "Download" button near the page header) onto your local machine and open it using excel.

2020 Update: If you're using Jupyter Lab, jupyterlab-spreadsheet is a great way to view excel files without leaving your browser.

Related Solutions

Python – Create a Pandas Dataframe by appending one row at a time

You can use df.loc[i], where the row with index i will be what you specify it to be in the dataframe.

>>> import pandas as pd
>>> from numpy.random import randint

>>> df = pd.DataFrame(columns=['lib', 'qty1', 'qty2'])
>>> for i in range(5):
>>>     df.loc[i] = ['name' + str(i)] + list(randint(10, size=2))

>>> df
     lib qty1 qty2
0  name0    3    3
1  name1    2    4
2  name2    2    8
3  name3    2    1
4  name4    9    6

Python – Selecting multiple columns in a Pandas dataframe

The column names (which are strings) cannot be sliced in the manner you tried.

Here you have a couple of options. If you know from context which variables you want to slice out, you can just return a view of only those columns by passing a list into the __getitem__ syntax (the []'s).

df1 = df[['a', 'b']]

Alternatively, if it matters to index them numerically and not by their name (say your code should automatically do this without knowing the names of the first two columns) then you can do this instead:

df1 = df.iloc[:, 0:2] # Remember that Python does not slice inclusive of the ending index.

Additionally, you should familiarize yourself with the idea of a view into a Pandas object vs. a copy of that object. The first of the above methods will return a new copy in memory of the desired sub-object (the desired slices).

Sometimes, however, there are indexing conventions in Pandas that don't do this and instead give you a new variable that just refers to the same chunk of memory as the sub-object or slice in the original object. This will happen with the second way of indexing, so you can modify it with the .copy() method to get a regular copy. When this happens, changing what you think is the sliced object can sometimes alter the original object. Always good to be on the look out for this.

df1 = df.iloc[0, 0:2].copy() # To avoid the case where changing df1 also changes df

To use iloc, you need to know the column positions (or indices). As the column positions may change, instead of hard-coding indices, you can use iloc along with get_loc function of columns method of dataframe object to obtain column indices.

{df.columns.get_loc(c): c for idx, c in enumerate(df.columns)}

Now you can use this dictionary to access columns through names and using iloc.

Best Answer

Related Solutions

Python – Create a Pandas Dataframe by appending one row at a time

Python – Selecting multiple columns in a Pandas dataframe

Related Topic