Account Snapshot Python Script

import pandas as pd

# Load the Excel file
file_path = r"C:\Users\marcp\PycharmProjects\shopsight-kw-checker\AMZ ACCOUNT OVERVIEW.xlsx"
xl = pd.ExcelFile(file_path)

# Read the "BusinessReport" and "Sponsored Products Campaigns" tabs into DataFrames
business_report_df = xl.parse('BusinessReport')
sponsored_products_df = xl.parse('Sponsored Products Campaigns')

# Extract unique ASINs from the "BusinessReport" tab
unique_asins = business_report_df['(Child) ASIN'].unique()

# Initialize the "Summary" DataFrame with unique ASINs
summary_df = pd.DataFrame(unique_asins, columns=['(Child) ASIN'])

# Map the sales, ad spend, and ad sales data for each ASIN
summary_df['Sales'] = summary_df['(Child) ASIN'].map(
    business_report_df.groupby('(Child) ASIN')['Ordered Product Sales'].sum())
summary_df['Ad Spend'] = summary_df['(Child) ASIN'].map(
    sponsored_products_df.groupby('ASIN (Informational only)')['Spend'].sum())
summary_df['Ad Sales'] = summary_df['(Child) ASIN'].map(
    sponsored_products_df.groupby('ASIN (Informational only)')['Sales'].sum())

# Perform the required calculations
summary_df['% sales'] = (summary_df['Sales'] / summary_df['Sales'].sum())
summary_df['% Ad Spend'] = (summary_df['Ad Spend'] / summary_df['Ad Spend'].sum())
summary_df['ACOS'] = (summary_df['Ad Spend'] / summary_df['Ad Sales'])
summary_df['TACOS'] = (summary_df['Ad Spend'] / summary_df['Sales'])
summary_df['% of ad sales'] = (summary_df['Ad Sales'] / summary_df['Sales'])

# Sort by "% sales" descending
summary_df = summary_df.sort_values(by='% sales', ascending=False)

# Rearrange the columns to the specified order
column_order = ['(Child) ASIN', '% sales', 'Sales', '% Ad Spend', 'Ad Spend', 'Ad Sales', 'ACOS', 'TACOS',
                '% of ad sales']
summary_df = summary_df[column_order]

# Create an Excel writer object
output_file_path = r"C:\Users\marcp\PycharmProjects\shopsight-kw-checker\AMZ ACCOUNT OVERVIEW - DONE.xlsx"
with pd.ExcelWriter(output_file_path, engine='xlsxwriter') as writer:
    # Write the Summary DataFrame to Excel file
    summary_df.to_excel(writer, sheet_name='Summary', index=False)

    # Access the XlsxWriter workbook and worksheet objects.
    workbook = writer.book
    worksheet = writer.sheets['Summary']

    # Define formats
    percent_format = workbook.add_format({'num_format': '0.0%'})
    currency_format = workbook.add_format({'num_format': '$#,##0'})

    # Apply formats to the columns
    worksheet.set_column('B:B', None, percent_format)
    worksheet.set_column('C:C', None, currency_format)
    worksheet.set_column('D:D', None, percent_format)
    worksheet.set_column('E:F', None, currency_format)
    worksheet.set_column('G:I', None, percent_format)

    # Define the conditional format
    conditional_format = {
        'type': '3_color_scale',
        'max_color': "#CD0D0D",  # red
        'mid_color': "#F5EF03",  # yellow
        'min_color': "#4AA238",  # green
    }

    # Get the number of rows in the summary DataFrame
    num_rows = len(summary_df.index)

    # Apply conditional formatting to "ACOS" and "TACOS" columns
    worksheet.conditional_format(f'G2:G{num_rows + 1}', conditional_format)
    worksheet.conditional_format(f'H2:H{num_rows + 1}', conditional_format)

    # Write other tabs
    for sheet in xl.sheet_names:
        if sheet != 'Summary':
            xl.parse(sheet).to_excel(writer, sheet_name=sheet, index=False)