I have a panda dataframe that I write to a xslx file, and would like to add a table over that data. I would also like to keep the headers that I have already written, instead of adding them again. Is that possible?
Example:
import pandas as pd
import xlsxwriter as xw
# random dataframe
d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)
# write data to file
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
df.to_excel(writer,"sheet without table")
df.to_excel(writer,"sheet with table")
df.to_excel(writer,"sheet with table and header")
# get sheets to add the tables
workbook = writer.book
worksheet_table = writer.sheets['sheet with table']
worksheet_table_header = writer.sheets['sheet with table and header']
# the range in which the table is
end_row = len(df.index)
end_column = len(df.columns)
cell_range = xw.utility.xl_range(0, 0, end_row, end_column)
# add the table that will delete the headers
worksheet_table.add_table(cell_range,{'header_row': True,'first_column': True})
######################################
# The hack
# Using the index in the Table
df.reset_index(inplace=True)
header = [{'header': di} for di in df.columns.tolist()]
worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})
writer.save()
The hack / work around is the only option (as seen from @jmcnamara). In short it is:
import pandas as pd
import xlsxwriter as xw
# random dataframe
d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)
# write data to file
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
df.to_excel(writer,"sheet with table and header")
# get sheets to add the tables
workbook = writer.book
worksheet_table_header = writer.sheets['sheet with table and header']
# the range in which the table is
end_row = len(df.index)
end_column = len(df.columns)
cell_range = xw.utility.xl_range(0, 0, end_row, end_column)
######################################
# The hack
# Using the index in the Table
df.reset_index(inplace=True)
header = [{'header': di} for di in df.columns.tolist()]
worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})
writer.save()
How about this (note that the 'options' is only required if the data frame contains NAs):
import pandas as pd
import xlsxwriter
# random dataframe
d = {'one':pd.Series([1., 2., 3.]), 'two':pd.Series([5., 6., 7., 8.])}
df = pd.DataFrame(d)
workbook = xlsxwriter.Workbook('test.xlsx', options={'nan_inf_to_errors': True})
worksheet = workbook.add_worksheet('sheet1')
worksheet.add_table(0, 0, df.shape[0], df.shape[1]-1,
{'data': df.values.tolist(),
'columns': [{'header': c} for c in df.columns.tolist()],
'style': 'Table Style Medium 9'})
workbook.close()
I would also like to keep the headers that I have already written, instead of adding them again. Is that possible?
No.
Your third solution in worksheet_table_header
is probably the best way to implement it.
I had to modify the the hack by @jmcnamara when using xlsxwriter version 0.9.6. I had to subtract one from the number of columns or I ended up with an extra column that was not in the pandas.DataFrame (see end_column assignment). Modified version below (pandas version 0.19.2).
import pandas as pd
import xlsxwriter
# random dataframe
d = {'one':pd.Series([1., 2., 3.]), 'two':pd.Series([5., 6., 7., 8.])}
df = pd.DataFrame(d)
print df
# write data to file
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
df.to_excel(writer, 'sheet1', index=False)
# get sheets to add the tables
ws = writer.sheets['sheet1']
# the range in which the table is
end_row = len(df.index)
end_column = len(df.columns) - 1
cell_range = xlsxwriter.utility.xl_range(0, 0, end_row, end_column)
######################################
# The hack
header = [{'header': c} for c in df.columns.tolist()]
ws.add_table(cell_range,{'header_row': True, 'columns':header, 'style':'Table Style Medium 11'})
ws.freeze_panes(1, 1)
writer.save()
writer.close()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With