application_vnd.jupyter.stderr_output_1.png
import numpy as np # linear algebra

import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

df1 = pd.read_excel('/kaggle/input/dash-assignment/DFP.xlsx', 'Data')
df1

df = df1

df['position'] = np.nan
list = ['top']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['position'].iloc[my_ind] = 'top'
list = ['bottom']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['position'].iloc[my_ind] = 'bottom'
list = ['middle']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['position'].iloc[my_ind] = 'middle'
list = ['leaderboard']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['position'].iloc[my_ind] = 'leaderboard'
list = ['passback']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['position'].iloc[my_ind] = 'passback'

df['story'] = np.nan
df['story'] = df.AD_UNIT_NAME.str.split('_', expand=True)[2]

df['amp_or_non_amp'] = 'Nonamp'
list = ['amp']
val = df['AD_UNIT_NAME'].apply(lambda x: any([k in x for k in list]))
my_ind = [i for i in val.index if val[i]]
df['amp_or_non_amp'].iloc[my_ind] = 'Amp'

df1 = pd.read_csv('/kaggle/input/dash-assignment/Actual_eCPM.csv')
df1

df = pd.merge(df, df1, on='LINE_ITEM_NAME', how='right')
val = df[df['Actual_eCPM'] == '-'].index
df['Actual_eCPM'].iloc[val] = 0
df['Actual_eCPM'] = df['Actual_eCPM'].astype(str).astype('float64')
df['Actual_Revenue'] = 0
Total_Impr = df['Impressions'].sum()
df['Actual_Revenue'] = Total_Impr * df['Actual_eCPM']

df.dtypes