excelformatter.py

import os

import pandas as pd
import xlsxwriter
from xlsxwriter.utility import xl_col_to_name


def set_column(df: 'dataframe', worksheet: 'a pd.Excelwriter sheet', cols: list, format: 'excel format to use', col_width: int = None) -> None:
    """ sets column by index, the column's position in the dataframe """
    idx = [df.columns.get_loc(c) for c in cols if c in df]

    for i in idx:
        # set the column width and format
        col = xl_col_to_name(i)
        worksheet.set_column(
            f'{col}:{col}',
            col_width,
            format)


def format(df: 'affiliate revenue stats dataframe', writer: 'pandas.ExcelWriter object', sheetname: str, ) -> 'pd.ExcelWriter':
    '''
    formats affiliate revenue stats excel worksheet with currency and percentage formatting
    '''

    # conv df to xslxwriter excel object
    df.to_excel(
        excel_writer=writer,
        sheet_name=sheetname,
        index=False,
    )

    # get the xlsxwriter workbook and worksheet objects
    workbook = writer.book
    worksheet = writer.sheets[sheetname]

    # add format cells written
    pct_fmt = workbook.add_format({
        'num_format': '0.0%',
    })

    money_fmt = workbook.add_format({
        'num_format': '$#,##0.00',
    })

    pct_cols = df.filter(regex='Rate|Margin')
    money_cols = df.filter(regex='Revenue|RPC|Cost')

    set_column(df, worksheet, pct_cols, pct_fmt)
    set_column(df, worksheet, money_cols, money_fmt)

    return writer

Hey Yanan! Here's a module you can put in the same directory as the Jupyter notebook you are writing the spreadsheets with. Here is how you would use it:

# import the module with your other imports
from excelformatter import format

Then where you iterate through the affiliate list ...

for affiliate in list(week....):
    drop_by_opener = ...
    dataProvider = ...

   ''' Here is where you put the function '''
   writer = format(dataframe, 'path/on/yanans/computer/', 'filename.xlsx', 'sheetname')
   ''' Note you need to save the output of the function to a variable so you can finish 
       working on the excel workbook, then save it to disk 
    '''

   # segment report sheets
   writer_weekly_drops = ...
   writer_weekly_drops.save()