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?

Answer 1

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)