TypeError: Unsupported type <type 'list'> in write()

Question

I am trying to dump a bunch of dicts to an .xlsx file by means of the following lines:

H=0.5 #Used to name the xlsx file
fail_thre=0.2 #Used to name the xlsx file
dict_list=[dict1,dict2,dict3] #The list of dictionaries to be dumped
myindex=['event 1','event 2','event 3'] #Used to name rows
from itertools import izip_longest
stats_matrix=[ tuple('dict{}'.format(i+1) for i in range(len(dict_list))) ] + list( izip_longest(*([ v for k,v in sorted(d.items())] for d in dict_list)) )
import pandas as pd
column_names=['Dict1','Dict2','Dict3']
mydf=pd.DataFrame(stats_matrix,index=myindex,columns=column_names) #Creating a data frame for MS Excel visualization
mydf.columns = ['Dict1','Dict2','Dict3']
writer = pd.ExcelWriter('Lattice_stats_H_'+str(Hurst)+'FAIL_'+str(fail_thre)+'_FLOODING.xlsx', engine='xlsxwriter')
mydf.to_excel(writer, sheet_name='Lattice')   
writer.save()

But I get this error (please note that the names in column_names and mydf.columns and etc. are the real names of my dicts, which are too long to be posted):

TypeError                                 Traceback (most recent call last)
C:\Users\Francesco\Desktop\Scripts\Network_Scripts\Lattice_ForLoop_FLOODING.py in <module>()
    399 mydf.columns = ['failed_nodes_1Stage','percent_failed_haz','act_nodes_1Stage','percent_active_haz','failed_nodes_2Stage','percent_failed_conn','failed_nodes_1plus2','percent_failed_1plus2','act_nodes_2Stage','percent_active_conn','total_failed_nodes','percent_total_failed_nodes','total_active_nodes','percent_total_active_nodes','giant_component','center_giant_comp','network_diam','connectedness','average_degree', 'graph_len']
    400 writer = pd.ExcelWriter('Lattice_stats_H_'+str(Hurst)+'FAIL_'+str(fail_thre)+'_FLOODING.xlsx', engine='xlsxwriter')
--> 401 mydf.to_excel(writer, sheet_name='Lattice')
    402 writer.save()
    403 

C:\Users\Francesco\AppData\Local\Enthought\Canopy32\User\lib\site-packages\pandas\core\frame.pyc in to_excel(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep)
   1272         formatted_cells = formatter.get_formatted_cells()
   1273         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1274                                  startrow=startrow, startcol=startcol)
   1275         if need_save:
   1276             excel_writer.save()

C:\Users\Francesco\AppData\Local\Enthought\Canopy32\User\lib\site-packages\pandas\io\excel.pyc in write_cells(self, cells, sheet_name, startrow, startcol)
   1337                 wks.write(startrow + cell.row,
   1338                           startcol + cell.col,
-> 1339                           cell.val, style)
   1340 
   1341     def _convert_to_style(self, style_dict, num_format_str=None):

C:\Users\Francesco\AppData\Local\Enthought\Canopy32\User\lib\site-packages\xlsxwriter\worksheet.pyc in cell_wrapper(self, *args, **kwargs)
     62             args = new_args
     63 
---> 64         return method(self, *args, **kwargs)
     65 
     66     return cell_wrapper

C:\Users\Francesco\AppData\Local\Enthought\Canopy32\User\lib\site-packages\xlsxwriter\worksheet.pyc in write(self, row, col, *args)
    429             pass
    430         except TypeError:
--> 431             raise TypeError("Unsupported type %s in write()" % type(token))
    432 
    433         # Finally try string.

TypeError: Unsupported type <type 'list'> in write()

What am I doing wrong?

davidshinn · Accepted Answer · 2015-11-20 19:26:21Z

3

Your mydf dataframe has elements of type list in the dataframe cells. It is likely due to how you built stats_matrix. See the the pandas docs for the appropriate ways to call DataFrame. To find your problems, you could call mydf.applymap(type) and see where the lists reside in mydf.

If you wanted to translate those list items into strings without the braces/brackets, you could use this function to convert the culprit column into a column that would work:

def list_to_number_string(value):
    if isinstance(value, (list, tuple)):
        return str(value)[1:-1]
    else:
        return value

mydf[badcol] = mydf[badcol].apply(list_to_number_string)

edited Nov 20, 2015 at 19:26

answered Nov 20, 2015 at 19:08

davidshinn

1,9561 gold badge16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

FaCoffee Over a year ago

Found it. There is a column which bears list type. And, unfortunately, I really need that column to be like that. Is there any workaround? The values of that column are meant to be something like: 17,56,89,212,567,789. That is, a list of integers.

davidshinn Over a year ago

Added a function for you to clean it the problem cells.

FaCoffee Over a year ago

The line mydf[center_giant_comp] = mydf[center_giant_comp].apply(list_to_number_string) throws out an error since the dicts to be written are OrderedDicts in reality. What should I change in your function to account for this?

davidshinn Over a year ago

It depends on what the OrderDict contents look like and what you want it to look like in your excel cells. If you share that information, would be an easy add to the function.

FaCoffee Over a year ago

This is what the ordered dict looks like before entering the suggested function:

center_giant_comp={'event 1':[12,45,78,123],'event 2':[67,89,345,678],'event 3':[346,678],...,'event n':[num1,num2,num3...]}

. The number of values in each list is either 0 or an even number, and they are always integer.

|

Collectives™ on Stack Overflow

TypeError: Unsupported type <type 'list'> in write()

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related