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
##
## name: Difference Column Percentages
## description:
##  Calculates the difference between SBA allocation and % of total pub cost
## 
## author: Dana Waidhas
## created: 2024-03-19
## 

# Load input data into DataFrame
inputDf = dataSourceDict["1"]

# primary data source and columns
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_SBA_STRATEGY = 'SBA Strategy'
RPT_COL_SBA_ALLOCATION = 'SBA Allocation'
RPT_COL_OF_TOTAL_PUB_COST = '% of Total Pub. Cost'
RPT_COL_PERCENTAGE_DIFFERENCE = 'Percentage Difference'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PERCENTAGE_DIFFERENCE = 'Percentage Difference'

# user code start here
# Check unique values in 'Percentage Difference' column
unique_values = inputDf[RPT_COL_PERCENTAGE_DIFFERENCE].unique()
print("Unique values in 'Percentage Difference' column:", unique_values)

# Check for missing values in 'SBA Allocation' and '% of Total Pub. Cost' columns
missing_values_allocation = inputDf[RPT_COL_SBA_ALLOCATION].isnull().sum()
missing_values_pub_cost = inputDf[RPT_COL_OF_TOTAL_PUB_COST].isnull().sum()

if missing_values_allocation > 0 or missing_values_pub_cost > 0:
    print("Warning: There are missing values in either 'SBA Allocation' or '% of Total Pub. Cost' column. Please handle them before continuing.")

# Define a function to clean and convert values to float
def clean_and_convert(value):
    try:
        # Remove non-numeric characters and convert to float
        cleaned_value = float(''.join(filter(lambda x: x.isdigit() or x == '.' or x == '-', str(value))))
        return cleaned_value
    except ValueError:
        return float('nan')

# Remove non-numeric characters and convert 'Percentage Difference' column to float
inputDf[RPT_COL_PERCENTAGE_DIFFERENCE] = inputDf[RPT_COL_PERCENTAGE_DIFFERENCE].apply(clean_and_convert)

# Calculate difference only if there are no missing values
if missing_values_allocation == 0 and missing_values_pub_cost == 0:
    # Calculate difference
    inputDf[BULK_COL_PERCENTAGE_DIFFERENCE] = (inputDf[RPT_COL_OF_TOTAL_PUB_COST] - inputDf[RPT_COL_SBA_ALLOCATION]) * 100

# Round the 'Percentage Difference' column to two decimal places
inputDf[RPT_COL_PERCENTAGE_DIFFERENCE] = round(inputDf[RPT_COL_PERCENTAGE_DIFFERENCE], 2)

# Define output column names
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_PERCENTAGE_DIFFERENCE = 'Percentage Difference'

# Select required columns for output
outputDf = inputDf[[RPT_COL_CAMPAIGN, RPT_COL_ACCOUNT, RPT_COL_PERCENTAGE_DIFFERENCE]]

# Rename columns
outputDf.columns = [BULK_COL_CAMPAIGN, BULK_COL_ACCOUNT, BULK_COL_PERCENTAGE_DIFFERENCE]


# Format 'Percentage Difference' column to display with a percentage symbol
outputDf[BULK_COL_PERCENTAGE_DIFFERENCE] = outputDf[BULK_COL_PERCENTAGE_DIFFERENCE].astype(str) + '%'

# Output
print(outputDf)