Script 509: Memira NO Offline Import

Purpose

Python script to merge and manipulate dataframes, and output the result.

To Elaborate

The Python script takes two dataframes, inputDf and gSheetsDf, and merges them based on the columns ‘Ad’ and ‘Keyword’. It then renames and manipulates certain columns, sets a new column based on conditions, and outputs the resulting dataframe.

Walking Through the Code

  1. The script starts by defining column names as constants.
  2. It assigns the first dataframe from dataSourceDict to the variable inputDf, and the second dataframe to gSheetsDf.
  3. The script merges the two dataframes using the ‘Ad’ and ‘Keyword’ columns, and assigns the result to mergedDf.
  4. The script renames certain columns in mergedDf using the defined constants.
  5. It fills NaN values with empty strings in the ‘Destination URL’ and ‘Suggested URL’ columns of mergedDf.
  6. It checks if the ‘Destination URL’ and ‘Suggested URL’ columns contain the string ‘utm_marin’ and assigns the result to boolean variables.
  7. The script sets the ‘FinalDestURL’ column of mergedDf based on conditions using np.where().
  8. It renames the ‘FinalDestURL’ column to ‘BULK_COL_CUSTOM_PARAMETERS’.
  9. It fills NaN values with empty strings in the ‘BULK_COL_CUSTOM_PARAMETERS’ column of mergedDf.
  10. The script assigns mergedDf to outputDf.
  11. It prints the tableized version of outputDf.

Vitals

  • Script ID : 509
  • Client ID / Customer ID: 1306925577 / 60269545
  • Action Type: Bulk Upload
  • Item Changed: Ad
  • Output Columns: Account, Campaign, Group, Creative ID, Media sub-type, Media Type, Display URL, Custom Parameters, Unique ID
  • Linked Datasource: M1 Report
  • Reference Datasource: Google Sheets
  • Owner: Tom McCaughey (tmccaughey@marinsoftware.com)
  • Created by Tom McCaughey on 2023-11-07 16:14
  • Last Updated by Tom McCaughey on 2024-05-03 08:29
> 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
#Offline format script
# Define the column names as constants
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_CREATIVE_ID = 'Creative ID'
BULK_COL_MEDIA_SUBTYPE = 'Media sub-type'
BULK_COL_MEDIA_TYPE = 'Media Type'
BULK_COL_DISPLAY_URL = 'Display URL'
BULK_COL_CUSTOM_PARAMETERS = 'Custom Parameters'
BULK_COL_UNIQUE_ID = 'Unique ID'

inputDf = dataSourceDict["1"]
gSheetsDf = dataSourceDict["2_1"]  # gsheets dataframe (first sheet)

# Merge dataframes based on 'Ad' and 'Keyword'
mergedDf = pd.merge(gSheetsDf, inputDf, left_on='F', right_on='Keyword', how='left')

mergedDf = mergedDf.rename(columns={
    'A': BULK_COL_UNIQUE_ID,
    'B': BULK_COL_CAMPAIGN,
    'E': BULK_COL_GROUP,
    'F': BULK_COL_CREATIVE_ID,
    'D': BULK_COL_MEDIA_SUBTYPE,
    'C': BULK_COL_MEDIA_TYPE,
    'G': BULK_COL_DISPLAY_URL
})

# If you want to replace NaN values with an empty string in the URL columns
mergedDf['Destination URL'].fillna('', inplace=True)
mergedDf['Suggested URL'].fillna('', inplace=True)

DestURL_contains_utm_marin = mergedDf['Destination URL'].str.contains('utm_marin', case=False, na=False)
SuggestURL_contains_utm_marin = mergedDf['Suggested URL'].str.contains('utm_marin', case=False, na=False)

# Set FinalDestURL based on conditions
mergedDf['FinalDestURL'] = np.where(DestURL_contains_utm_marin, mergedDf['Destination URL'],
                                    np.where(SuggestURL_contains_utm_marin, mergedDf['Suggested URL'], 'https://www.memira.se/'))

# Rename the FinalDestURL column to BULK_COL_CUSTOM_PARAMETERS
mergedDf = mergedDf.rename(columns={'FinalDestURL': BULK_COL_CUSTOM_PARAMETERS})

# If you want to replace NaN values with an empty string in the BULK_COL_CUSTOM_PARAMETERS column
mergedDf[BULK_COL_CUSTOM_PARAMETERS].fillna('', inplace=True)

outputDf = mergedDf

print(tableize(outputDf))

Post generated on 2024-05-15 07:44:05 GMT

comments powered by Disqus