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
- The script starts by defining column names as constants.
- It assigns the first dataframe from
dataSourceDict
to the variableinputDf
, and the second dataframe togSheetsDf
. - The script merges the two dataframes using the ‘Ad’ and ‘Keyword’ columns, and assigns the result to
mergedDf
. - The script renames certain columns in
mergedDf
using the defined constants. - It fills NaN values with empty strings in the ‘Destination URL’ and ‘Suggested URL’ columns of
mergedDf
. - It checks if the ‘Destination URL’ and ‘Suggested URL’ columns contain the string ‘utm_marin’ and assigns the result to boolean variables.
- The script sets the ‘FinalDestURL’ column of
mergedDf
based on conditions usingnp.where()
. - It renames the ‘FinalDestURL’ column to ‘BULK_COL_CUSTOM_PARAMETERS’.
- It fills NaN values with empty strings in the ‘BULK_COL_CUSTOM_PARAMETERS’ column of
mergedDf
. - The script assigns
mergedDf
tooutputDf
. - 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