Find FIRST and LAST non-zero values in each row of Pandas, color them, and save it to an Excel file-CodePudding

I have a Pandas DataFrame:

import pandas as pd

df = pd.DataFrame([[0.0, 2.0, 0.0, 0.0, 5.0, 6.0, 7.0],
               [1.0, 0.0, 1.0, 3.0, 0.0, 0.0, 7.0],
               [0.0, 0.0, 13.0, 14.0, 0.0, 16.0, 0.0]
              ]
              , columns=['A', 'B', 'C', 'D', 'E', 'F', 'G'])

    A    B     C     D     E     F     G
0  0.0  2.0   0.0   0.0   5.0   6.0   7.0
1  1.0  0.0   1.0   3.0   0.0   0.0   7.0
2  0.0  0.0  13.0  14.0   0.0  16.0  17.0

And I would like to save it as an .xlsx file, with the first and last non-zero values in each row marked in color. something like:

CodePudding user response：

I removed the index column though. The first column.

# import dependencies
import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

# data
df = pd.DataFrame([[0.0, 2.0, 0.0, 0.0, 5.0, 6.0, 7.0],
               [1.0, 0.0, 1.0, 3.0, 0.0, 0.0, 7.0],
               [0.0, 0.0, 13.0, 14.0, 0.0, 16.0, 0.0]
              ], columns=['A', 'B', 'C', 'D', 'E', 'F', 'G'])

first_and_last_non_zeroes_index = []

for index, row in df.iterrows():
    # all non zeroes index in a row
    non_zeroes_index= [i for i, x in enumerate(row) if x>0]

    # append the first and last non zero in a row to list
    first_and_last_non_zeroes_index.append([non_zeroes_index[0],non_zeroes_index[-1]])

# output to excel
df.to_excel('output.xlsx', index=False)

# open excel
wb = openpyxl.load_workbook("output.xlsx")
ws = wb['Sheet1']

# set the color
fill_cell = PatternFill(patternType='solid', 
                            fgColor='ffff00')

# color the appropriate cells
for index, row in enumerate(first_and_last_non_zeroes_index):
    for col in row:
        ws[f'{get_column_letter(col 1)}{index 2}'].fill = fill_cell

# save output
wb.save("output.xlsx")