I am trying to write pandas DataFrame and Series in xlwt Worksheet. Everything is going well if I try to write numpy.int64 data, in which case xlwt is suffocating. Changing int64 to float64 in my data and sibling indexes are simple, but what would be the right way to do this for MultiIndexes?
t = pd.DataFrame(np.array(np.mat('0 1 0 1; 1 0 2 3; 1 1 2 4'))) arrays = [[1,2,3,4],[5,6,7,8]] tuples = zip(*arrays) index = pd.MultiIndex.from_tuples(tuples, names=['First','Second']) t.columns = index wb = xlwt.Workbook() ws_1 = wb.add_sheet('simple index', cell_overwrite_ok=True) In [137]: t Out[137]: First 1 2 3 4 Second 5 6 7 8 0 0 1 0 1 1 1 0 2 3 2 1 1 2 4 In [157]: t.ix[0][1][5] Out[157]: 0 In [158]: ws_1.row(0).write(0, t.ix[0][1][5]) ------------------------------------------------------------ Traceback (most recent call last): File "<ipython console>", line 1, in <module> File "C:\Python27\lib\site-packages\xlwt\Row.py", line 259, in write raise Exception("Unexpected data type %r" % type(label)) Exception: Unexpected data type <type 'numpy.int64'> In [159]: t.dtypes Out[159]: First Second 1 5 int64 2 6 int64 3 7 int64 4 8 int64 In [160]: idx = t.dtypes[t.dtypes == np.int64].index In [161]: idx Out[161]: MultiIndex [(1, 5), (2, 6), (3, 7), (4, 8)] In [163]: for i in idx: .....: t[i] = t[i].astype(np.float64) .....: In [164]: t.dtypes Out[164]: First Second 1 5 float64 2 6 float64 3 7 float64 4 8 float64 In [165]: ws_1.row(0).write(0, t.ix[0][1][5]) In [167]: t.columns.levels Out[167]: [Int64Index([1, 2, 3, 4], dtype=int64), Int64Index([5, 6, 7, 8], dtype=int64)] In [168]: t.columns Out[168]: MultiIndex [(1, 5), (2, 6), (3, 7), (4, 8)] In [169]: t.columns[0][0] Out[169]: 1 In [170]: ws_1.row(0).write(0, t.columns[0][0]) ------------------------------------------------------------ Traceback (most recent call last): File "<ipython console>", line 1, in <module> File "C:\Python27\lib\site-packages\xlwt\Row.py", line 259, in write raise Exception("Unexpected data type %r" % type(label)) Exception: Unexpected data type <type 'numpy.int64'>