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.")