import pandas as pd import numpy as np import pptx from pptx import Presentation from pptx.chart.data import CategoryChartData, ChartData from pptx.enum.chart import XL_CHART_TYPE, XL_LEGEND_POSITION, XL_LABEL_POSITION from pptx.enum.chart import XL_TICK_LABEL_POSITION from pptx.util import Inches, Pt import os import pickle from pathlib import Path from sklearn.metrics import ( mean_absolute_error, r2_score, mean_absolute_percentage_error, ) import streamlit as st from collections import OrderedDict from utilities import get_metrics_names, initialize_data, retrieve_pkl_object_without_warning from io import BytesIO from pptx.dml.color import RGBColor from post_gres_cred import db_cred schema=db_cred['schema'] from constants import ( TITLE_FONT_SIZE, AXIS_LABEL_FONT_SIZE, CHART_TITLE_FONT_SIZE, AXIS_TITLE_FONT_SIZE, DATA_LABEL_FONT_SIZE, LEGEND_FONT_SIZE, PIE_LEGEND_FONT_SIZE ) def format_response_metric(target): if target.startswith('response_metric_'): target = target.replace('response_metric_', '') target = target.replace("_", " ").title() return target def smape(actual, forecast): # Symmetric Mape (SMAPE) eliminates shortcomings of MAPE : ## 1. MAPE becomes insanely high when actual is close to 0 ## 2. MAPE is more favourable to underforecast than overforecast return (1 / len(actual)) * np.sum(1 * np.abs(forecast - actual) / (np.abs(actual) + np.abs(forecast))) def safe_num_to_per(num): try: return "{:.0%}".format(num) except: return num # Function to convert numbers to abbreviated format def convert_number_to_abbreviation(number): try: number = float(number) if number >= 1000000: return f'{number / 1000000:.1f} M' elif number >= 1000: return f'{number / 1000:.1f} K' else: return str(number) except: return number def round_off(x, round_off_decimal=0): # round off try: x = float(x) if x < 1 and x > 0: round_off_decimal = int(np.floor(np.abs(np.log10(x)))) + max(round_off_decimal, 1) x = np.round(x, round_off_decimal) elif x < 0 and x > -1: round_off_decimal = int(np.floor(np.abs(np.log10(np.abs(x))))) + max(round_off_decimal, 1) x = -np.round(x, round_off_decimal) else: x = np.round(x, round_off_decimal) return x except: return x def fill_table_placeholder(table_placeholder, slide, df, column_width=None, table_height=None): cols = len(df.columns) rows = len(df) if table_height is None: table_height = table_placeholder.height x, y, cx, cy = table_placeholder.left, table_placeholder.top, table_placeholder.width, table_height table = slide.shapes.add_table(rows + 1, cols, x, y, cx, cy).table # Populate the table with data from the DataFrame for row_idx, row in enumerate(df.values): for col_idx, value in enumerate(row): cell = table.cell(row_idx + 1, col_idx) cell.text = str(value) for col_idx, value in enumerate(df.columns): cell = table.cell(0, col_idx) cell.text = str(value) if column_width is not None: for col_idx, column_width in column_width.items(): table.columns[col_idx].width = Inches(column_width) table_placeholder._element.getparent().remove(table_placeholder._element) def bar_chart(chart_placeholder, slide, chart_data, titles={}, min_y=None, max_y=None, type='V', legend=True, label_type=None, xaxis_pos=None): x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height if type == 'V': graphic_frame = slide.shapes.add_chart( XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data ) if type == 'H': graphic_frame = slide.shapes.add_chart( XL_CHART_TYPE.BAR_CLUSTERED, x, y, cx, cy, chart_data ) chart = graphic_frame.chart category_axis = chart.category_axis value_axis = chart.value_axis # Add chart title if 'chart_title' in titles.keys(): chart.has_title = True chart.chart_title.text_frame.text = titles['chart_title'] chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0] chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE) # Add axis titles if 'x_axis' in titles.keys(): category_axis.has_title = True category_axis.axis_title.text_frame.text = titles['x_axis'] category_title = category_axis.axis_title.text_frame.paragraphs[0].runs[0] category_title.font.size = Pt(AXIS_TITLE_FONT_SIZE) if 'y_axis' in titles.keys(): value_axis.has_title = True value_axis.axis_title.text_frame.text = titles['y_axis'] value_title = value_axis.axis_title.text_frame.paragraphs[0].runs[0] value_title.font.size = Pt(AXIS_TITLE_FONT_SIZE) if xaxis_pos == 'low': category_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW # Customize the chart if legend: chart.has_legend = True chart.legend.position = XL_LEGEND_POSITION.BOTTOM chart.legend.font.size = Pt(LEGEND_FONT_SIZE) chart.legend.include_in_layout = False # Adjust font size for axis labels category_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE) value_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE) if min_y is not None: value_axis.minimum_scale = min_y # Adjust this value as needed if max_y is not None: value_axis.maximum_scale = max_y # Adjust this value as needed plot = chart.plots[0] plot.has_data_labels = True data_labels = plot.data_labels if label_type == 'per': data_labels.number_format = '0"%"' elif label_type == '$': data_labels.number_format = '$[>=1000000]#,##0.0,,"M";$[>=1000]#,##0.0,"K";$#,##0' elif label_type == '$1': data_labels.number_format = '$[>=1000000]#,##0,,"M";$[>=1000]#,##0,"K";$#,##0' elif label_type == 'M': data_labels.number_format = '#0.0,,"M"' elif label_type == 'M1': data_labels.number_format = '#0.00,,"M"' elif label_type == 'K': data_labels.number_format = '#0.0,"K"' data_labels.font.size = Pt(DATA_LABEL_FONT_SIZE) chart_placeholder._element.getparent().remove(chart_placeholder._element) def line_chart(chart_placeholder, slide, chart_data, titles={}, min_y=None, max_y=None): # Add the chart to the slide x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height chart = slide.shapes.add_chart( XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data ).chart chart.has_legend = True chart.legend.position = XL_LEGEND_POSITION.BOTTOM chart.legend.font.size = Pt(LEGEND_FONT_SIZE) category_axis = chart.category_axis value_axis = chart.value_axis if min_y is not None: value_axis.minimum_scale = min_y if max_y is not None: value_axis.maximum_scale = max_y if min_y is not None and max_y is not None: value_axis.major_unit = int((max_y - min_y) / 2) if 'chart_title' in titles.keys(): chart.has_title = True chart.chart_title.text_frame.text = titles['chart_title'] chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0] chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE) if 'x_axis' in titles.keys(): category_axis.has_title = True category_axis.axis_title.text_frame.text = titles['x_axis'] category_title = category_axis.axis_title.text_frame.paragraphs[0].runs[0] category_title.font.size = Pt(AXIS_TITLE_FONT_SIZE) if 'y_axis' in titles.keys(): value_axis.has_title = True value_axis.axis_title.text_frame.text = titles['y_axis'] value_title = value_axis.axis_title.text_frame.paragraphs[0].runs[0] value_title.font.size = Pt(AXIS_TITLE_FONT_SIZE) # Adjust font size for axis labels category_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE) value_axis.tick_labels.font.size = Pt(AXIS_LABEL_FONT_SIZE) plot = chart.plots[0] series = plot.series[1] line = series.format.line line.color.rgb = RGBColor(141, 47, 0) chart_placeholder._element.getparent().remove(chart_placeholder._element) def pie_chart(chart_placeholder, slide, chart_data, title): # Add the chart to the slide x, y, cx, cy = chart_placeholder.left, chart_placeholder.top, chart_placeholder.width, chart_placeholder.height chart = slide.shapes.add_chart( XL_CHART_TYPE.PIE, x, y, cx, cy, chart_data ).chart chart.has_legend = True chart.legend.position = XL_LEGEND_POSITION.RIGHT chart.legend.include_in_layout = False chart.legend.font.size = Pt(PIE_LEGEND_FONT_SIZE) chart.plots[0].has_data_labels = True data_labels = chart.plots[0].data_labels data_labels.number_format = '0%' data_labels.position = XL_LABEL_POSITION.OUTSIDE_END data_labels.font.size = Pt(DATA_LABEL_FONT_SIZE) chart.has_title = True chart.chart_title.text_frame.text = title chart_title = chart.chart_title.text_frame.paragraphs[0].runs[0] chart_title.font.size = Pt(CHART_TITLE_FONT_SIZE) chart_placeholder._element.getparent().remove(chart_placeholder._element) def title_and_table(slide, title, df, column_width=None, custom_table_height=False): placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = title title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) table_placeholder = slide.placeholders[ph_idx[1]] table_height = None if custom_table_height: if len(df) < 4: table_height = int(np.ceil(table_placeholder.height / 2)) fill_table_placeholder(table_placeholder, slide, df, column_width, table_height) # try: # font_size = 18 # default for 3*3 # if cols < 3: # row_diff = 3 - rows # font_size = font_size + ((row_diff)*2) # 1 row less -> 2 pt font size increase & vice versa # else: # row_diff = 2 - rows # font_size = font_size + ((row_diff)*2) # for row in table.rows: # for cell in row.cells: # cell.text_frame.paragraphs[0].runs[0].font.size = Pt(font_size) # except Exception as e : # print("**"*30) # print(e) # else: # except Exception as e: # print('table', e) return slide def data_import(data, bin_dict): import_df = pd.DataFrame(columns=['Category', 'Value']) import_df.at[0, 'Category'] = 'Date Range' date_start = data['date'].min().date() date_end = data['date'].max().date() import_df.at[0, 'Value'] = str(date_start) + ' - ' + str(date_end) import_df.at[1, 'Category'] = 'Response Metrics' import_df.at[1, 'Value'] = ', '.join(bin_dict['Response Metrics']) import_df.at[2, 'Category'] = 'Media Variables' import_df.at[2, 'Value'] = ', '.join(bin_dict['Media']) import_df.at[3, 'Category'] = 'Spend Variables' import_df.at[3, 'Value'] = ', '.join(bin_dict['Spends']) if bin_dict['Exogenous'] != []: import_df.at[4, 'Category'] = 'Exogenous Variables' import_df.at[4, 'Value'] = ', '.join(bin_dict['Exogenous']) return import_df def channel_groups_df(channel_groups_dct={}, bin_dict={}): df = pd.DataFrame(columns=['Channel', 'Media Variables', 'Spend Variables']) i = 0 for channel, vars in channel_groups_dct.items(): media_vars = ", ".join(list(set(vars).intersection(set(bin_dict["Media"])))) spend_vars = ", ".join(list(set(vars).intersection(set(bin_dict["Spends"])))) df.at[i, "Channel"] = channel df.at[i, 'Media Variables'] = media_vars df.at[i, 'Spend Variables'] = spend_vars i += 1 return df def transformations(transform_dict): transform_df = pd.DataFrame(columns=['Category', 'Transformation', 'Value']) i = 0 for category in ['Media', 'Exogenous']: transformations = f'transformation_{category}' category_dict = transform_dict[category] if transformations in category_dict.keys(): for transformation in category_dict[transformations]: transform_df.at[i, 'Category'] = category transform_df.at[i, 'Transformation'] = transformation transform_df.at[i, 'Value'] = str(category_dict[transformation][0]) + ' - ' + str( category_dict[transformation][1]) i += 1 return transform_df def model_metrics(model_dict, is_panel): metrics_df = pd.DataFrame( columns=[ "Response Metric", "Model", "R2", "ADJR2", "Train MAPE", "Test MAPE" ] ) i = 0 for key in model_dict.keys(): target = key.split("__")[1] metrics_df.at[i, "Response Metric"] = format_response_metric(target) metrics_df.at[i, "Model"] = key.split("__")[0] y = model_dict[key]["X_train_tuned"][target] feature_set = model_dict[key]["feature_set"] if is_panel: random_df = get_random_effects( media_data, panel_col, model_dict[key]["Model_object"] ) pred = mdf_predict( model_dict[key]["X_train_tuned"], model_dict[key]["Model_object"], random_df, )["pred"] else: pred = model_dict[key]["Model_object"].predict(model_dict[key]["X_train_tuned"][feature_set]) ytest = model_dict[key]["X_test_tuned"][target] if is_panel: predtest = mdf_predict( model_dict[key]["X_test_tuned"], model_dict[key]["Model_object"], random_df, )["pred"] else: predtest = model_dict[key]["Model_object"].predict(model_dict[key]["X_test_tuned"][feature_set]) metrics_df.at[i, "R2"] = np.round(r2_score(y, pred), 2) adjr2 = 1 - (1 - metrics_df.loc[i, "R2"]) * ( len(y) - 1 ) / (len(y) - len(model_dict[key]["feature_set"]) - 1) metrics_df.at[i, "ADJR2"] = np.round(adjr2, 2) # y = np.where(np.abs(y) < 0.00001, 0.00001, y) metrics_df.at[i, "Train MAPE"] = np.round(smape(y, pred), 2) metrics_df.at[i, "Test MAPE"] = np.round(smape(ytest, predtest), 2) i += 1 metrics_df = np.round(metrics_df, 2) return metrics_df def model_result(slide, model_key, model_dict, model_metrics_df, date_col): placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = model_key.split('__')[0] title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) target = model_key.split('__')[1] metrics_table_placeholder = slide.placeholders[ph_idx[1]] metrics_df = model_metrics_df[model_metrics_df['Model'] == model_key.split('__')[0]].reset_index(drop=True) # Accuracy = 1-mape metrics_df['Accuracy'] = 100 * (1 - metrics_df['Train MAPE']) metrics_df['Accuracy'] = metrics_df['Accuracy'].apply(lambda x: f'{np.round(x, 0)}%') ## Removing metrics as requested by Ioannis metrics_df = metrics_df.drop(columns=['R2', 'ADJR2', 'Train MAPE', 'Test MAPE']) fill_table_placeholder(metrics_table_placeholder, slide, metrics_df) # coeff_table_placeholder = slide.placeholders[ph_idx[2]] # coeff_df = pd.DataFrame(model_dict['Model_object'].params) # coeff_df.reset_index(inplace=True) # coeff_df.columns = ['Feature', 'Coefficent'] # fill_table_placeholder(coeff_table_placeholder, slide, coeff_df) chart_placeholder = slide.placeholders[ph_idx[2]] full_df = pd.concat([model_dict['X_train_tuned'], model_dict['X_test_tuned']]) full_df['Predicted'] = model_dict['Model_object'].predict(full_df[model_dict['feature_set']]) pred_df = full_df[[date_col, target, 'Predicted']] pred_df.rename(columns={target: 'Actual'}, inplace=True) # Create chart data chart_data = CategoryChartData() chart_data.categories = pred_df[date_col] chart_data.add_series('Actual', pred_df['Actual']) chart_data.add_series('Predicted', pred_df['Predicted']) # Set range for y axis min_y = np.floor(min(pred_df['Actual'].min(), pred_df['Predicted'].min())) max_y = np.ceil(max(pred_df['Actual'].max(), pred_df['Predicted'].max())) # Create the chart line_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Actual VS Predicted', 'x_axis': 'Date', 'y_axis': target.title().replace('_', ' ') }, min_y=min_y, max_y=max_y ) return slide def metrics_contributions(slide, contributions_excels_dict, panel_col): # Create data for metrics contributions all_contribution_df = pd.DataFrame(columns=['Channel']) target_sum_dict = {} sort_support_dct = {} for target in contributions_excels_dict.keys(): contribution_df = contributions_excels_dict[target]['CONTRIBUTION MMM'].copy() if 'Date' in contribution_df.columns: contribution_df.drop(columns=['Date'], inplace=True) if panel_col in contribution_df.columns: contribution_df.drop(columns=[panel_col], inplace=True) contribution_df = pd.DataFrame(np.sum(contribution_df, axis=0)).reset_index() contribution_df.columns = ['Channel', target] target_sum = contribution_df[target].sum() target_sum_dict[target] = target_sum contribution_df[target] = 100 * contribution_df[target] / target_sum all_contribution_df = pd.merge(all_contribution_df, contribution_df, on='Channel', how='outer') sorted_target_sum_dict = sorted(target_sum_dict.items(), key=lambda kv: kv[1], reverse=True) sorted_target_sum_keys = [kv[0] for kv in sorted_target_sum_dict] if len([metric for metric in sorted_target_sum_keys if metric.lower() == 'revenue']) == 1: rev_metric = [metric for metric in sorted_target_sum_keys if metric.lower() == 'revenue'][0] sorted_target_sum_keys.remove(rev_metric) sorted_target_sum_keys.append(rev_metric) all_contribution_df = all_contribution_df[['Channel'] + sorted_target_sum_keys] # for col in all_contribution_df.columns: # all_contribution_df[col]=all_contribution_df[col].apply(lambda x: round_off(x,1)) # Sort Data by Average contribution of the channels keeping base first # all_contribution_df['avg'] = np.mean(all_contribution_df[list(contributions_excels_dict.keys())],axis=1) # all_contribution_df['rank'] = all_contribution_df['avg'].rank(ascending=False) # Sort data by contribution of bottom funnel metric bottom_funnel_metric = sorted_target_sum_keys[-1] all_contribution_df['rank'] = all_contribution_df[bottom_funnel_metric].rank(ascending=False) all_contribution_df.loc[all_contribution_df[all_contribution_df['Channel'] == 'base'].index, 'rank'] = 0 all_contribution_df = all_contribution_df.sort_values(by='rank') all_contribution_df.drop(columns=['rank'], inplace=True) # Add title placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = "Response Metrics Contributions" title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) for target in contributions_excels_dict.keys(): all_contribution_df[target] = all_contribution_df[target].astype(float) # Create chart data chart_data = CategoryChartData() chart_data.categories = all_contribution_df['Channel'] for target in sorted_target_sum_keys: chart_data.add_series(format_response_metric(target), all_contribution_df[target]) chart_placeholder = slide.placeholders[ph_idx[1]] if isinstance(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])), float): # Add the chart to the slide bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Response Metrics Contributions', # 'x_axis':'Channels', 'y_axis': 'Contributions'}, min_y=np.floor(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime']))), max_y=np.ceil(np.max(all_contribution_df.select_dtypes(exclude=['object', 'datetime']))), type='V', label_type='per' ) else: bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Response Metrics Contributions', # 'x_axis':'Channels', 'y_axis': 'Contributions'}, min_y=np.floor(np.min(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])).values[0]), max_y=np.ceil(np.max(all_contribution_df.select_dtypes(exclude=['object', 'datetime'])).values[0]), type='V', label_type='per' ) return slide def model_media_performance(slide, target, contributions_excels_dict, date_col='Date', is_panel=False, panel_col='panel'): # Add title placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = "Media Performance - " + target.title().replace("_", " ") title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) # CONTRIBUTION CHART # Create contribution data contribution_df = contributions_excels_dict[target]['CONTRIBUTION MMM'] if panel_col in contribution_df.columns: contribution_df.drop(columns=[panel_col], inplace=True) # contribution_df.drop(columns=[date_col], inplace=True) contribution_df = pd.DataFrame(np.sum(contribution_df, axis=0)).reset_index() contribution_df.columns = ['Channel', format_response_metric(target)] contribution_df['Channel'] = contribution_df['Channel'].apply(lambda x: x.title()) target_sum = contribution_df[format_response_metric(target)].sum() contribution_df[format_response_metric(target)] = contribution_df[format_response_metric(target)] / target_sum contribution_df.sort_values(by=['Channel'], ascending=False, inplace=True) # for col in contribution_df.columns: # contribution_df[col] = contribution_df[col].apply(lambda x : round_off(x)) # Create Chart Data chart_data = ChartData() chart_data.categories = contribution_df['Channel'] chart_data.add_series('Contribution', contribution_df[format_response_metric(target)]) chart_placeholder = slide.placeholders[ph_idx[2]] pie_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, title='Contribution') # SPENDS CHART initialize_data(panel='aggregated', metrics=target) scenario = st.session_state["scenario"] spends_values = { channel_name: round( scenario.channels[channel_name].actual_total_spends * scenario.channels[channel_name].conversion_rate, 1, ) for channel_name in st.session_state["channels_list"] } spends_df = pd.DataFrame(columns=['Channel', 'Media Spend']) spends_df['Channel'] = list(spends_values.keys()) spends_df['Media Spend'] = list(spends_values.values()) spends_sum = spends_df['Media Spend'].sum() spends_df['Media Spend'] = spends_df['Media Spend'] / spends_sum spends_df['Channel'] = spends_df['Channel'].apply(lambda x: x.title()) spends_df.sort_values(by='Channel', ascending=False, inplace=True) # for col in spends_df.columns: # spends_df[col] = spends_df[col].apply(lambda x : round_off(x)) # Create Chart Data spends_chart_data = ChartData() spends_chart_data = ChartData() spends_chart_data.categories = spends_df['Channel'] spends_chart_data.add_series('Media Spend', spends_df['Media Spend']) spends_chart_placeholder = slide.placeholders[ph_idx[1]] pie_chart(chart_placeholder=spends_chart_placeholder, slide=slide, chart_data=spends_chart_data, title='Media Spend') # spends_values.append(0) return contribution_df, spends_df # def get_saved_scenarios_dict(project_path): # # Path to the saved scenarios file # saved_scenarios_dict_path = os.path.join( # project_path, "saved_scenarios.pkl" # ) # # # Load existing scenarios if the file exists # if os.path.exists(saved_scenarios_dict_path): # with open(saved_scenarios_dict_path, "rb") as f: # saved_scenarios_dict = pickle.load(f) # else: # saved_scenarios_dict = OrderedDict() # # return saved_scenarios_dict def optimization_summary(slide, scenario, scenario_name): placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = 'Optimization Summary' # + ' (Scenario: ' + scenario_name + ')' title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) multiplier = 1 / float(scenario['multiplier']) # st.write(scenario['multiplier'], multiplier) ## Multiplier is an indicator of selected time fram ## Doesn't effect CPA opt_on = scenario['optimization'] if opt_on.lower() == 'spends': opt_on = 'Media Spend' details_ph = slide.placeholders[ph_idx[3]] details_ph.text = 'Scenario Name: ' + scenario_name + \ '\nResponse Metric: ' + str(scenario['metrics_selected']).replace("_", " ").title() + \ '\nOptimized on: ' + str(opt_on).replace("_", " ").title() scenario_df = pd.DataFrame(columns=['Category', 'Actual', 'Simulated', 'Change']) scenario_df.at[0, 'Category'] = 'Media Spend' scenario_df.at[0, 'Actual'] = scenario['actual_total_spends'] * multiplier scenario_df.at[0, 'Simulated'] = scenario['modified_total_spends'] * multiplier scenario_df.at[0, 'Change'] = (scenario['modified_total_spends'] - scenario['actual_total_spends']) * multiplier scenario_df.at[1, 'Category'] = scenario['metrics_selected'].replace("_", " ").title() scenario_df.at[1, 'Actual'] = scenario['actual_total_sales'] * multiplier scenario_df.at[1, 'Simulated'] = (scenario['modified_total_sales']) * multiplier scenario_df.at[1, 'Change'] = (scenario['modified_total_sales'] - scenario['actual_total_sales']) * multiplier scenario_df.at[2, 'Category'] = 'CPA' actual_cpa = scenario['actual_total_spends'] / scenario['actual_total_sales'] modified_cpa = scenario['modified_total_spends'] / scenario['modified_total_sales'] scenario_df.at[2, 'Actual'] = actual_cpa scenario_df.at[2, 'Simulated'] = modified_cpa scenario_df.at[2, 'Change'] = modified_cpa - actual_cpa scenario_df.at[3, 'Category'] = 'ROI' act_roi = scenario['actual_total_sales'] / scenario['actual_total_spends'] opt_roi = scenario['modified_total_sales'] / scenario['modified_total_spends'] scenario_df.at[3, 'Actual'] = act_roi scenario_df.at[3, 'Simulated'] = opt_roi scenario_df.at[3, 'Change'] = opt_roi - act_roi for col in scenario_df.columns: scenario_df[col] = scenario_df[col].apply(lambda x: round_off(x, 1)) scenario_df[col] = scenario_df[col].apply(lambda x: convert_number_to_abbreviation(x)) table_placeholder = slide.placeholders[ph_idx[1]] fill_table_placeholder(table_placeholder, slide, scenario_df) channel_spends_df = pd.DataFrame(columns=['Channel', 'Actual Spends', 'Optimized Spends']) for i, channel in enumerate(scenario['channels'].values()): channel_spends_df.at[i, 'Channel'] = channel['name'] channel_conversion_rate = channel[ "conversion_rate" ] channel_spends_df.at[i, 'Actual Spends'] = ( channel["actual_total_spends"] * channel_conversion_rate ) * multiplier channel_spends_df.at[i, 'Optimized Spends'] = ( channel["modified_total_spends"] * channel_conversion_rate ) * multiplier channel_spends_df['Actual Spends'] = channel_spends_df['Actual Spends'].astype('float') channel_spends_df['Optimized Spends'] = channel_spends_df['Optimized Spends'].astype('float') for col in channel_spends_df.columns: channel_spends_df[col] = channel_spends_df[col].apply(lambda x: round_off(x, 0)) # Sort data on Actual Spends channel_spends_df.sort_values(by='Actual Spends', inplace=True, ascending=False) # Create chart data chart_data = CategoryChartData() chart_data.categories = channel_spends_df['Channel'] for col in ['Actual Spends', 'Optimized Spends']: chart_data.add_series(col, channel_spends_df[col]) chart_placeholder = slide.placeholders[ph_idx[2]] # Add the chart to the slide if isinstance(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime'])),float): bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Channel Wise Spends', # 'x_axis':'Channels', 'y_axis': 'Spends'}, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, max_y=np.ceil(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), label_type='$' ) else: # Add the chart to the slide bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Channel Wise Spends', # 'x_axis':'Channels', 'y_axis': 'Spends'}, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, max_y=np.ceil(np.max(channel_spends_df.select_dtypes(exclude=['object', 'datetime'])).values[0]), label_type='$' ) def channel_wise_spends(slide, scenario): placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = 'Channel Spends and Impact' title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) # print(scenario.keys()) multiplier = 1 / float(scenario['multiplier']) channel_spends_df = pd.DataFrame(columns=['Channel', 'Actual Spends', 'Optimized Spends']) for i, channel in enumerate(scenario['channels'].values()): channel_spends_df.at[i, 'Channel'] = channel['name'] channel_conversion_rate = channel["conversion_rate"] channel_spends_df.at[i, 'Actual Spends'] = (channel[ "actual_total_spends"] * channel_conversion_rate) * multiplier channel_spends_df.at[i, 'Optimized Spends'] = (channel[ "modified_total_spends"] * channel_conversion_rate) * multiplier channel_spends_df['Actual Spends'] = channel_spends_df['Actual Spends'].astype('float') channel_spends_df['Optimized Spends'] = channel_spends_df['Optimized Spends'].astype('float') actual_sum = channel_spends_df['Actual Spends'].sum() opt_sum = channel_spends_df['Optimized Spends'].sum() for col in channel_spends_df.columns: channel_spends_df[col] = channel_spends_df[col].apply(lambda x: round_off(x, 0)) channel_spends_df['Actual Spends %'] = 100 * (channel_spends_df['Actual Spends'] / actual_sum) channel_spends_df['Optimized Spends %'] = 100 * (channel_spends_df['Optimized Spends'] / opt_sum) channel_spends_df['Actual Spends %'] = np.round(channel_spends_df['Actual Spends %']) channel_spends_df['Optimized Spends %'] = np.round(channel_spends_df['Optimized Spends %']) # Sort Data based on Actual Spends % channel_spends_df.sort_values(by='Actual Spends %', inplace=True) # Create chart data chart_data = CategoryChartData() chart_data.categories = channel_spends_df['Channel'] for col in ['Actual Spends %', 'Optimized Spends %']: # for col in ['Actual Spends %']: chart_data.add_series(col, channel_spends_df[col]) chart_placeholder = slide.placeholders[ph_idx[1]] # Add the chart to the slide if isinstance(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']]), float): bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Spend Split %', # 'x_axis':'Channels', 'y_axis': 'Spend %'}, min_y=0, max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']])), type='H', legend=True, label_type='per', xaxis_pos='low' ) else: bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Spend Split %', # 'x_axis':'Channels', 'y_axis': 'Spend %'}, min_y=0, max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']]).values[0]), type='H', legend=True, label_type='per', xaxis_pos='low' ) # # # Create chart data # chart_data_1 = CategoryChartData() # chart_data_1.categories = channel_spends_df['Channel'] # # for col in ['Actual Spends %', 'Optimized Spends %']: # for col in ['Optimized Spends %']: # chart_data_1.add_series(col, channel_spends_df[col]) # chart_placeholder_1 = slide.placeholders[ph_idx[3]] # # # Add the chart to the slide # bar_chart(chart_placeholder=chart_placeholder_1, # slide=slide, # chart_data=chart_data_1, # titles={'chart_title': 'Optimized Spends Split %', # # 'x_axis':'Channels', # 'y_axis': 'Spends %'}, # min_y=0, # max_y=np.ceil(np.max(channel_spends_df[['Actual Spends %', 'Optimized Spends %']])), # type='H', # legend=False, # label_type='per' # ) channel_spends_df['Delta %'] = 100 * (channel_spends_df['Optimized Spends'] - channel_spends_df['Actual Spends']) / \ channel_spends_df['Actual Spends'] channel_spends_df['Delta %'] = channel_spends_df['Delta %'].apply(lambda x: round_off(x, 0)) # Create chart data delta_chart_data = CategoryChartData() delta_chart_data.categories = channel_spends_df['Channel'] col = 'Delta %' delta_chart_data.add_series(col, channel_spends_df[col]) delta_chart_placeholder = slide.placeholders[ph_idx[3]] # Add the chart to the slide if isinstance(np.min(channel_spends_df['Delta %']), float): bar_chart(chart_placeholder=delta_chart_placeholder, slide=slide, chart_data=delta_chart_data, titles={'chart_title': 'Spend Delta %', 'y_axis': 'Spend Delta %'}, min_y=np.floor(np.min(channel_spends_df['Delta %'])), max_y=np.ceil(np.max(channel_spends_df['Delta %'])), type='H', legend=False, label_type='per', xaxis_pos='low' ) else: bar_chart(chart_placeholder=delta_chart_placeholder, slide=slide, chart_data=delta_chart_data, titles={'chart_title': 'Spend Delta %', 'y_axis': 'Spend Delta %'}, min_y=np.floor(np.min(channel_spends_df['Delta %']).values[0]), max_y=np.ceil(np.max(channel_spends_df['Delta %']).values[0]), type='H', legend=False, label_type='per', xaxis_pos='low' ) # Incremental Impact channel_inc_df = pd.DataFrame(columns=['Channel', 'Increment']) for i, channel in enumerate(scenario['channels'].values()): channel_inc_df.at[i, 'Channel'] = channel['name'] act_impact = channel['actual_total_sales'] opt_impact = channel['modified_total_sales'] impact = opt_impact - act_impact impact = round_off(impact, 0) impact = impact if abs(impact) > 0.0001 else 0 channel_inc_df.at[i, 'Increment'] = impact channel_inc_df_1 = pd.merge(channel_spends_df, channel_inc_df, how='left', on='Channel') # Create chart data delta_chart_data = CategoryChartData() delta_chart_data.categories = channel_inc_df_1['Channel'] col = 'Increment' delta_chart_data.add_series(col, channel_inc_df_1[col]) delta_chart_placeholder = slide.placeholders[ph_idx[2]] label_req = True if min(np.abs(channel_inc_df_1[col])) > 100000: # 0.1M label_type = 'M' elif min(np.abs(channel_inc_df_1[col])) > 10000 and max(np.abs(channel_inc_df_1[col])) > 1000000: label_type = 'M1' elif min(np.abs(channel_inc_df_1[col])) > 100 and max(np.abs(channel_inc_df_1[col])) > 1000: label_type = 'K' else: label_req = False # Add the chart to the slide if label_req: bar_chart(chart_placeholder=delta_chart_placeholder, slide=slide, chart_data=delta_chart_data, titles={'chart_title': 'Incremental Impact', 'y_axis': format_response_metric(scenario['metrics_selected'])}, # min_y=np.floor(np.min(channel_inc_df_1['Delta %'])), # max_y=np.ceil(np.max(channel_inc_df_1['Delta %'])), type='H', label_type=label_type, legend=False, xaxis_pos='low' ) else: bar_chart(chart_placeholder=delta_chart_placeholder, slide=slide, chart_data=delta_chart_data, titles={'chart_title': 'Increment', 'y_axis': scenario['metrics_selected']}, # min_y=np.floor(np.min(channel_inc_df_1['Delta %'])), # max_y=np.ceil(np.max(channel_inc_df_1['Delta %'])), type='H', legend=False, xaxis_pos='low' ) def channel_wise_roi(slide, scenario): channel_roi_mroi = scenario['channel_roi_mroi'] # Add title placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = 'Channel ROIs' title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) channel_roi_df = pd.DataFrame(columns=['Channel', 'Actual ROI', 'Optimized ROI']) for i, channel in enumerate(channel_roi_mroi.keys()): channel_roi_df.at[i, 'Channel'] = channel channel_roi_df.at[i, 'Actual ROI'] = channel_roi_mroi[channel]['actual_roi'] channel_roi_df.at[i, 'Optimized ROI'] = channel_roi_mroi[channel]['optimized_roi'] channel_roi_df['Actual ROI'] = channel_roi_df['Actual ROI'].astype('float') channel_roi_df['Optimized ROI'] = channel_roi_df['Optimized ROI'].astype('float') for col in channel_roi_df.columns: channel_roi_df[col] = channel_roi_df[col].apply(lambda x: round_off(x, 2)) # Create chart data chart_data = CategoryChartData() chart_data.categories = channel_roi_df['Channel'] for col in ['Actual ROI', 'Optimized ROI']: chart_data.add_series(col, channel_roi_df[col]) chart_placeholder = slide.placeholders[ph_idx[1]] # Add the chart to the slide if isinstance(channel_roi_df.select_dtypes(exclude=['object', 'datetime']), float): bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Channel Wise ROI', # 'x_axis':'Channels', 'y_axis': 'ROI'}, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])) ) else: bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Channel Wise ROI', # 'x_axis':'Channels', 'y_axis': 'ROI'}, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])).values[0] ) # act_roi = scenario['actual_total_sales']/scenario['actual_total_spends'] # opt_roi = scenario['modified_total_sales']/scenario['modified_total_spends'] # # act_roi_ph = slide.placeholders[ph_idx[2]] # act_roi_ph.text = 'Actual ROI: ' + str(round_off(act_roi,2)) # opt_roi_ph = slide.placeholders[ph_idx[3]] # opt_roi_ph.text = 'Optimized ROI: ' + str(round_off(opt_roi, 2)) ## Removing mroi chart as per Ioannis' feedback # channel_mroi_df = pd.DataFrame(columns=['Channel', 'Actual mROI', 'Optimized mROI']) # for i, channel in enumerate(channel_roi_mroi.keys()): # channel_mroi_df.at[i, 'Channel'] = channel # channel_mroi_df.at[i, 'Actual mROI'] = channel_roi_mroi[channel]['actual_mroi'] # channel_mroi_df.at[i, 'Optimized mROI'] = channel_roi_mroi[channel]['optimized_mroi'] # channel_mroi_df['Actual mROI']=channel_mroi_df['Actual mROI'].astype('float') # channel_mroi_df['Optimized mROI']=channel_mroi_df['Optimized mROI'].astype('float') # # for col in channel_mroi_df.columns: # channel_mroi_df[col]=channel_mroi_df[col].apply(lambda x: round_off(x)) # # # Create chart data # mroi_chart_data = CategoryChartData() # mroi_chart_data.categories = channel_mroi_df['Channel'] # for col in ['Actual mROI', 'Optimized mROI']: # mroi_chart_data.add_series(col, channel_mroi_df[col]) # # mroi_chart_placeholder=slide.placeholders[ph_idx[2]] # # # Add the chart to the slide # bar_chart(chart_placeholder=mroi_chart_placeholder, # slide=slide, # chart_data=mroi_chart_data, # titles={'chart_title':'Channel Wise mROI', # # 'x_axis':'Channels', # 'y_axis':'mROI'}, # # min_y=np.floor(np.min(channel_mroi_df.select_dtypes(exclude=['object', 'datetime']))), # min_y=0, # max_y=np.ceil(np.max(channel_mroi_df.select_dtypes(exclude=['object', 'datetime']))) # ) def effictiveness_efficiency(slide, final_data, bin_dct, scenario): # Add title placeholders = slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = slide.placeholders[ph_idx[0]] title_ph.text = 'Effectiveness and Efficiency' title_ph.text_frame.paragraphs[0].font.size = Pt(TITLE_FONT_SIZE) response_metrics = bin_dct['Response Metrics'] kpi_df = final_data[response_metrics].sum(axis=0).reset_index() kpi_df.columns = ['Response Metric', 'Effectiveness'] kpi_df['Efficiency'] = kpi_df['Effectiveness'] / scenario['modified_total_spends'] kpi_df['Efficiency'] = kpi_df['Efficiency'].apply(lambda x: round_off(x, 1)) kpi_df.sort_values(by='Effectiveness', inplace=True) kpi_df['Response Metric'] = kpi_df['Response Metric'].apply(lambda x: format_response_metric(x)) # Create chart data for effectiveness chart_data = CategoryChartData() chart_data.categories = kpi_df['Response Metric'] chart_data.add_series('Effectiveness', kpi_df['Effectiveness']) chart_placeholder = slide.placeholders[ph_idx[1]] # Add the chart to the slide bar_chart(chart_placeholder=chart_placeholder, slide=slide, chart_data=chart_data, titles={'chart_title': 'Effectiveness', # 'x_axis':'Channels', # 'y_axis': 'ROI' }, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, # max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])), type='H', label_type='M' ) # Create chart data for efficiency chart_data_1 = CategoryChartData() chart_data_1.categories = kpi_df['Response Metric'] chart_data_1.add_series('Efficiency', kpi_df['Efficiency']) chart_placeholder_1 = slide.placeholders[ph_idx[2]] # Add the chart to the slide bar_chart(chart_placeholder=chart_placeholder_1, slide=slide, chart_data=chart_data_1, titles={'chart_title': 'Efficiency', # 'x_axis':'Channels', # 'y_axis': 'ROI' }, # min_y=np.floor(np.min(channel_spends_df.select_dtypes(exclude=['object', 'datetime']))), min_y=0, # max_y=np.max(channel_roi_df.select_dtypes(exclude=['object', 'datetime'])), type='H' ) definition_ph_1 = slide.placeholders[ph_idx[3]] definition_ph_1.text = 'Effectiveness is measured as the total sum of the Response Metric' definition_ph_2 = slide.placeholders[ph_idx[4]] definition_ph_2.text = 'Efficiency is measured as the ratio of sum of the Response Metric and sum of Media Spend' def load_pickle(path): with open(path, "rb") as f: file_data = pickle.load(f) return file_data def read_all_files(): files=[] # Read data and bin dictionary if st.session_state["project_dct"]["data_import"]["imputed_tool_df"] is not None: final_df_loaded = st.session_state["project_dct"]["data_import"]["imputed_tool_df"].copy() bin_dict_loaded = st.session_state["project_dct"]["data_import"]["category_dict"].copy() files.append(final_df_loaded) files.append(bin_dict_loaded) if "group_dict" in st.session_state["project_dct"]["data_import"].keys(): channels = st.session_state["project_dct"]["data_import"]["group_dict"] files.append(channels) if st.session_state["project_dct"]["transformations"]["final_df"] is not None: transform_dict = st.session_state["project_dct"]["transformations"] files.append(transform_dict) if retrieve_pkl_object_without_warning(st.session_state['project_number'], "Model_Tuning", "tuned_model", schema) is not None: tuned_model_dict = retrieve_pkl_object_without_warning(st.session_state['project_number'], "Model_Tuning", "tuned_model", schema) # db files.append(tuned_model_dict) else: files.append(None) else: files.append(None) if len(list(st.session_state["project_dct"]["current_media_performance"]["model_outputs"].keys()))>0: # check if there are model outputs for at least one metric metrics_list = list(st.session_state["project_dct"]["current_media_performance"]["model_outputs"].keys()) contributions_excels_dict = {} for metrics in metrics_list: # raw_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["raw_data"] # spend_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["spends_data"] contribution_df = st.session_state["project_dct"]["current_media_performance"]["model_outputs"][metrics]["contribution_data"] contributions_excels_dict[metrics] = {'CONTRIBUTION MMM':contribution_df} files.append(contributions_excels_dict) # Get Saved Scenarios if len(list(st.session_state["project_dct"]["saved_scenarios"]["saved_scenarios_dict"].keys()))>0: files.append(st.session_state["project_dct"]["saved_scenarios"]["saved_scenarios_dict"]) # saved_scenarios_loaded = get_saved_scenarios_dict(project_path) return files ''' Template Layout 0 : Title 1 : Data Details Section {no changes required} 2 : Data Import 3 : Data Import - Channel Groups 4 : Model Results {Duplicate for each model} 5 : Metrics Contribution 6 : Media performance {Duplicate for each model} 7 : Media performance Tabular View {Duplicate for each model} 8 : Optimization Section {no changes} 9 : Optimization Summary {Duplicate for each section} 10 : Channel Spends {Duplicate for each model} 11 : Channel Wise ROI {Duplicate for each model} 12 : Efficiency & Efficacy 13 : Appendix 14 : Transformations 15 : Model Summary 16 : Thank You Slide ''' def create_ppt(project_name, username, panel_col): # Read saved files files = read_all_files() transform_dict, tuned_model_dict, contributions_excels_dict, saved_scenarios_loaded = None, None, None, None if len(files)>0: # saved_data = files[0] data = files[0] bin_dict = files[1] channel_groups_dct = files[2] try: transform_dict = files[3] tuned_model_dict = files[4] contributions_excels_dict = files[5] saved_scenarios_loaded = files[6] except Exception as e: print(e) else: return False is_panel = True if data[panel_col].nunique()>1 else False template_path = 'ppt/template.pptx' # ppt_path = os.path.join('ProjectSummary.pptx') prs = Presentation(template_path) num_slides = len(prs.slides) slides = prs.slides # Title Slide title_slide_layout = slides[0].slide_layout title_slide = prs.slides.add_slide(title_slide_layout) # Add title & project name placeholders = title_slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = title_slide.placeholders[ph_idx[0]] title_ph.text = 'Media Mix Optimization Summary' txt_ph = title_slide.placeholders[ph_idx[1]] txt_ph.text = 'Project Name: ' + project_name + '\nCreated By: ' + username # Model Details Section model_section_slide_layout = slides[1].slide_layout model_section_slide = prs.slides.add_slide(model_section_slide_layout) ## Add title placeholders = model_section_slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = model_section_slide.placeholders[ph_idx[0]] title_ph.text = 'Model Details' section_ph = model_section_slide.placeholders[ph_idx[1]] section_ph.text = 'Section 1' # Data Import data_import_slide_layout = slides[2].slide_layout data_import_slide = prs.slides.add_slide(data_import_slide_layout) data_import_slide = title_and_table(slide=data_import_slide, title='Data Import', df=data_import(data, bin_dict), column_width={0: 2, 1: 7} ) # Channel Groups channel_group_slide_layout = slides[3].slide_layout channel_group_slide = prs.slides.add_slide(channel_group_slide_layout) channel_group_slide = title_and_table(slide=channel_group_slide, title='Channels - Media and Spend', df=channel_groups_df(channel_groups_dct, bin_dict), column_width={0: 2, 1: 5, 2: 2} ) if tuned_model_dict is not None: model_metrics_df = model_metrics(tuned_model_dict, False) # Model Results for model_key, model_dict in tuned_model_dict.items(): model_result_slide_layout = slides[4].slide_layout model_result_slide = prs.slides.add_slide(model_result_slide_layout) model_result_slide = model_result(slide=model_result_slide, model_key=model_key, model_dict=model_dict, model_metrics_df=model_metrics_df, date_col='date') if contributions_excels_dict is not None: # Metrics Contributions metrics_contributions_slide_layout = slides[5].slide_layout metrics_contributions_slide = prs.slides.add_slide(metrics_contributions_slide_layout) metrics_contributions_slide = metrics_contributions(slide=metrics_contributions_slide, contributions_excels_dict=contributions_excels_dict, panel_col=panel_col ) # Media Performance for target in contributions_excels_dict.keys(): # Chart model_media_perf_slide_layout = slides[6].slide_layout model_media_perf_slide = prs.slides.add_slide(model_media_perf_slide_layout) contribution_df, spends_df = model_media_performance(slide=model_media_perf_slide, target=target, contributions_excels_dict=contributions_excels_dict ) # Tabular View contri_spends_df = pd.merge(spends_df, contribution_df, on='Channel', how='outer') contri_spends_df.fillna(0, inplace=True) for col in [c for c in contri_spends_df.columns if c != 'Channel']: contri_spends_df[col] = contri_spends_df[col].apply(lambda x: safe_num_to_per(x)) media_performance_table_slide_layout = slides[7].slide_layout media_performance_table_slide = prs.slides.add_slide(media_performance_table_slide_layout) media_performance_table_slide = title_and_table(slide=media_performance_table_slide, title='Media and Spends Channels Tabular View', df=contri_spends_df, # column_width={0:2, 1:5, 2:2} ) if saved_scenarios_loaded is not None: # Optimization Details opt_section_slide_layout = slides[8].slide_layout opt_section_slide = prs.slides.add_slide(opt_section_slide_layout) ## Add title placeholders = opt_section_slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = opt_section_slide.placeholders[ph_idx[0]] title_ph.text = 'Optimizations Details' section_ph = opt_section_slide.placeholders[ph_idx[1]] section_ph.text = 'Section 2' # Optimization for scenario_name, scenario in saved_scenarios_loaded.items(): opt_summary_slide_layout = slides[9].slide_layout opt_summary_slide = prs.slides.add_slide(opt_summary_slide_layout) optimization_summary(opt_summary_slide, scenario, scenario_name) channel_spends_slide_layout = slides[10].slide_layout channel_spends_slide = prs.slides.add_slide(channel_spends_slide_layout) channel_wise_spends(channel_spends_slide, scenario) channel_roi_slide_layout = slides[11].slide_layout channel_roi_slide = prs.slides.add_slide(channel_roi_slide_layout) channel_wise_roi(channel_roi_slide, scenario) effictiveness_efficiency_slide_layout = slides[12].slide_layout effictiveness_efficiency_slide = prs.slides.add_slide(effictiveness_efficiency_slide_layout) effictiveness_efficiency(effictiveness_efficiency_slide, data, bin_dict, scenario) # Appendix Section appendix_section_slide_layout = slides[13].slide_layout appendix_section_slide = prs.slides.add_slide(appendix_section_slide_layout) if tuned_model_dict is not None: ## Add title placeholders = appendix_section_slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = appendix_section_slide.placeholders[ph_idx[0]] title_ph.text = 'Appendix' section_ph = appendix_section_slide.placeholders[ph_idx[1]] section_ph.text = 'Section 3' # Add transformations # if transform_dict is not None: # # Transformations # transformation_slide_layout = slides[14].slide_layout # transformation_slide = prs.slides.add_slide(transformation_slide_layout) # transformation_slide = title_and_table(slide=transformation_slide, # title='Transformations', # df=transformations(transform_dict), # custom_table_height=True # ) # Add model summary # Model Summary model_metrics_df = model_metrics(tuned_model_dict, False) model_summary_slide_layout = slides[15].slide_layout model_summary_slide = prs.slides.add_slide(model_summary_slide_layout) model_summary_slide = title_and_table(slide=model_summary_slide, title='Model Summary', df=model_metrics_df, custom_table_height=True ) # Last Slide last_slide_layout = slides[num_slides - 1].slide_layout last_slide = prs.slides.add_slide(last_slide_layout) # Add title placeholders = last_slide.placeholders ph_idx = [ph.placeholder_format.idx for ph in placeholders] title_ph = last_slide.placeholders[ph_idx[0]] title_ph.text = 'Thank You' # Remove template slides xml_slides = prs.slides._sldIdLst slides = list(xml_slides) for index in range(num_slides): xml_slides.remove(slides[index]) # prs.save(ppt_path) # save the output into binary form binary_output = BytesIO() prs.save(binary_output) return binary_output