Script 1479: Update Monthly Budgets via Gsheet

Purpose

The script updates monthly budgets for strategies by matching them with concatenated values from a Google Sheet.

To Elaborate

The Python script is designed to update the monthly budget allocations for various strategies by matching them with specific entries in a Google Sheet. It achieves this by first loading data from a primary data source and a secondary Google Sheet. The script then constructs a unique key by concatenating the ‘Country’, ‘Channel’, and ‘Vendor’ columns from the Google Sheet. This key is used to find corresponding strategies in the primary data source. Once a match is found, the script updates the monthly budget for that strategy using the budget data from the Google Sheet. The script is structured to handle different months by dynamically selecting the appropriate budget column based on the current month.

Walking Through the Code

  1. Data Loading and Preparation
    • The script begins by loading data from two sources: a primary data source and a secondary Google Sheet.
    • The Google Sheet data is processed to set the correct headers and prepare it for matching operations.
  2. Month and Budget Column Selection
    • The script determines the current month and selects the corresponding budget column from the Google Sheet using a predefined dictionary of month headers.
  3. Key Construction and Matching
    • A unique key is created by concatenating ‘Country’, ‘Channel’, and ‘Vendor’ columns from the Google Sheet.
    • The script iterates over each strategy in the primary data source, attempting to match it with the constructed key from the Google Sheet.
  4. Budget Update
    • If a match is found, the script updates the strategy’s monthly budget in the primary data source with the value from the Google Sheet.
    • If no match is found, a message is printed indicating the absence of a match for that strategy.
  5. Completion
    • The script concludes by confirming that the monthly budgets have been updated.

Vitals

  • Script ID : 1479
  • Client ID / Customer ID: 1306928241 / 60269279
  • Action Type: Bulk Upload
  • Item Changed: Strategy
  • Output Columns: Strategy, Target
  • Linked Datasource: M1 Report
  • Reference Datasource: Google Sheets
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-11-05 19:35
  • Last Updated by dwaidhas@marinsoftware.com on 2024-11-08 20:22
> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

#
## name: Update Monthly Budgets via Gsheet
## description:
##  This script updates the monthly budget for each strategy by matching strategies 
##  from the report with concatenated values (Country_Channel_Vendor) in the second sheet of the Google Sheet.
## 
## author: Dana Waidhas
## created: 2024-11-05
##

# Setup timezone (adjust according to your requirement)
CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=+8))  # Adjust this offset as necessary

# Get today’s date for month detection
today = datetime.datetime.now(CLIENT_TIMEZONE).date()
month_column = today.strftime('%b')  # Abbreviation of current month, e.g., 'Nov'

# Primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CLIENT = 'Client'
RPT_COL_CURRENCY = 'Currency'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_TARGET = 'Target'
RPT_COL_CONSTRAINT_TYPE = 'Constraint Type'

# Load secondary data from Google Sheets

# Reference data source (second sheet in Google Sheets)
# First, ensure you load the budget data from the correct tab
budget_rows = dataSourceDict["2_2"].iloc[2:4]  # Adjust to the actual rows containing the budgets
# Use row 3 as headers
headers = budget_rows.iloc[0]  # Assuming row 3 has headers
gSheetsDf = dataSourceDict["2_2"].iloc[3:]  # Data starts from row 4
gSheetsDf.columns = headers.str.strip().str.lower()  # Set row 3 as headers, clean up whitespaces and lowercase
gSheetsDf = gSheetsDf.reset_index(drop=True)

# Debug: Print the headers and the first few rows of the Google Sheets DataFrame
print("Headers from Google Sheets:", gSheetsDf.columns.tolist())
print("Sample data from Google Sheets:\n", gSheetsDf.head())

# Assign the month headers directly from specific cells
month_headers = {
    'Nov': budget_rows.iloc[0, 4],  # E3
    'Dec': budget_rows.iloc[0, 5],  # F3
    'Jan': budget_rows.iloc[0, 6],  # G3
    'Feb': budget_rows.iloc[0, 7],  # H3
    'Mar': budget_rows.iloc[0, 8],  # I3
    'Apr': budget_rows.iloc[0, 9],  # J3
    'May': budget_rows.iloc[0, 10], # K3
    'Jun': budget_rows.iloc[0, 11], # L3
    'Jul': budget_rows.iloc[0, 12], # M3
    'Aug': budget_rows.iloc[0, 13], # N3
}

# Example of accessing the budget for a specific month
month = 'Nov'  # Change this to the desired month as needed
monthly_budget_column = month_headers[month]

# Concatenate Country, Channel, and Vendor columns to form the matching key
gSheetsDf['concatenated_key'] = gSheetsDf['country'] + "_" + gSheetsDf['channel'] + "_" + gSheetsDf['vendor']

# Loop through each row in the report DataFrame and match strategy
for idx, row in inputDf.iterrows():
    strategy = row[RPT_COL_STRATEGY]
    
    # Find the matching row in Google Sheets based on the strategy
    gsheet_match = gSheetsDf[gSheetsDf['concatenated_key'] == strategy]
    
    # If a match is found, update the monthly budget for the strategy
    if not gsheet_match.empty:
        budget_value = gsheet_match.iloc[0][monthly_budget_column]
        outputDf.at[idx, RPT_COL_TARGET] = budget_value
    else:
        print(f"No match found for strategy: {strategy}")

print("Monthly budgets updated.")

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus