CODEWITHSUNDEEP
pythonexcelpandasxlsxwriter
Siesta
Siesta

Reputation: 471

Xlsxwriter different formats to different cells in same column

I have a problem when setting the format for different cells. I have two dataframes that contains numbers that should be shown in different formats. I have created a simple example to show my problem.

import pandas as pd
import numpy as np
import xlsxwriter

frame1 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)),  columns=list('ABCD'))

#want to show this frame as percentage in Excel later
frame2 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)),    columns=list('EFGH'))

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

frame1.to_excel(writer, sheet_name='Sheet1', startcol= 0, startrow= 0)

frame2.to_excel(writer, sheet_name='Sheet1', startcol= 0, startrow = (2 + len(frame1)))

writer.save()

This code produces this Excel Sheet:

Shows output

Now the problem is that I for instance might want to set the cells marked in yellow as % or as some other format. I know I can set the format on a whole column or a row, but I have not been able to solve how I can set the format for just some specific cells. I guess there is some really easy solutions to this.

Upvotes: 3

Views: 2497

Answers (2)

MaThMaX
MaThMaX

Reputation: 2015

The work-around would be using conditional_format, as mentioned by @jmcnamara, you just apply {'type': 'top','value': str(f2rows),'format': format1}, where f2rows is the number of rows of your dataframe and the format1 is the format you would like to apply.

import pandas as pd
import numpy as np
import xlsxwriter

frame1 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)),  columns=list('ABCD'))

#want to show this frame as percentage in Excel later
frame2 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)),    columns=list('EFGH'))

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

frame1.to_excel(writer, sheet_name='Sheet1', startcol= 0, startrow= 0)

frame2.to_excel(writer, sheet_name='Sheet1', startcol= 0, startrow = (2 + len(frame1)))

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

f2rows = frame2.shape[0]
startR = 4 + len(frame1)
endR = startR + f2rows -1

# Apply a conditional format to the cell range.
format1 = workbook.add_format({'bg_color': '#FFFF00',
                               'font_color': '#9C0006'})

worksheet.conditional_format('C{}:C{}'.format(startR, endR), {'type': 'top',
                                           'value': str(f2rows),
                                           'format': format1})

worksheet.conditional_format('E{}:E{}'.format(startR, endR), {'type': 'top',
                                           'value': str(f2rows),
                                           'format': format1})

writer.save()

Upvotes: 3

jmcnamara
jmcnamara

Reputation: 41644

The column format in Excel applies to the entire column and can only be overridden by a row format or a cell format.

So you would need to apply a cell format to the cells that you wanted to format differently and that isn't possible with the Pandas interface.

An alternative could be to use a conditional format for the range of cells you are interested in. See for example this Pandas program from the XlsxWriter docs.

Upvotes: 3

Related Questions