I have a huge dataframe and I need to display it into an excel sheet such that every other 2 columns are colored except the 1st column.
For example:
If there are columns 1 to 100,
column 2,3 must be red
then 4,5 non colored
then 6,7 again red
then 8,9 non colored
and it goes on and on till last column of the dataframe.
I came with following solution:
import pandas as pd
import numpy as np
columns = 13
data = np.array([np.arange(10)]*columns).T
df = pd.DataFrame(data=data)
df = df.fillna(0) # with 0s rather than NaNs
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'bg_color': '#FFC7CE'})
for col in range(2, columns+1, 4):
worksheet.set_column(col, col + 1, cell_format=format1)
writer.save()
Iterate from index 2 (second col), until columns+1 (indexing comes from 1 in excel), color 2 cols at once and then move 4 indices further. The only problem here right now, it colors whole column (even not filled), I'll look for solution for that later.
Output:
You need to translate integer indices to excel-like labels with a function and use conditional_format in case you want to color only fields with text:
import pandas as pd
import numpy as np
columns = 13
data = np.array([np.arange(10)]*columns).T
df = pd.DataFrame(data=data)
df = df.fillna(0) # with 0s rather than NaNs
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'bg_color': '#FFC7CE'})
def colnum_string(n):
string = ""
n+=1 #just because we have index saved in first col
while n > 0:
n, remainder = divmod(n - 1, 26)
string = chr(65 + remainder) + string
return string
for col in range(2, columns+1, 4):
str1 = colnum_string(col)+"2" #ommiting header, 1 if header
str2 = colnum_string(col+1)+str(11) #number of rows+1 (header)
ids = str1+":"+str2
print(ids)
worksheet.conditional_format(ids, {'type': 'no_blanks',
'format': format1})
writer.save()
Output of the second code:
In Excel, Selected the columns containing you data or the entire spreadsheet. Click Conditional formatting on the Home Ribbon. Click New Rule. Click Use a formula to determine which cells to format. In the formula box enter =OR(MOD(COLUMN(A1),4)=2,MOD(COLUMN(A1),4)=3). Click the Format button. Select the fill tab. Set the fill color to what you want. Hit OK a few times and you should be done.
This will fill in the cells that or equal to 2 or 3 mod 4.
if you have poblem only with iteration than:
for i in range(2, columns, 4):
setColumnColor(i)
setColumnColor(i+1)
You start coloring from the 2nd column and coloring 2 columns at a time. Than iterating on the columns by steps of 4.
But if you have problems on finding a method to set dataframe colors than this is a thread for you: Colouring one column of pandas dataframe
You do it in Excel:
You start with converting a range to a table (Ctrl+T).
Then switch to the Design tab, remove a tick from Banded rows and select Banded columns instead.
right-click on the table styles
click on duplicates
