Save large pandas dataframe to excel
This took some source code digging, but...
print('Reading temporaty files for variable {}:'.format(Var))print(' Reading stations')s=pd.read_csv(StatFile,sep=':',dtype={'ID': 'str'},encoding='utf-8')print(' Reading data')d=pd.read_csv(DataFile,sep=':',dtype='str',encoding='utf-8').transpose()d.columns = d.iloc[0]d=d[1:].astype('float')d.reindex_axis(sorted(d.columns), axis=1)print('Writing out Excel file for variable {}'.format(Var))writer = pd.ExcelWriter(Path + Var + '.xlsx', engine='xlsxwriter')#THISwriter.book.use_zip64()d.to_excel(writer, sheet_name='Data')OutStatCol=['ID','Name','Longitude','Latitude','GRS','OriginalVariable','VariableUnits','URL','JsonNode']s.to_excel(writer, columns=OutStatCol, index=False, sheet_name='Stations')writer.save()should work
figuring out that the writer didn't inherit from workbook took me longer than it should have. writer.book is directly a workbook instance... d'oh
I just added engine='xlsxwriter' to the function .to_excel() and it's fixed the problem.