<jupyter_start><jupyter_text>Distinct value counts/frequencies of each column to determine if there are any columns with only a single value/all different values<jupyter_code>pd.DataFrame(Ilog_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), 
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
df_groupby_CampaignId=Ilog_df.groupby('CampaignId')
df_groupby_CampaignId.ngroups
df_groupby_CampaignId.size()
campaign1 = Ilog_df[Ilog_df['CampaignId'] == 'awbu4q4']
pd.DataFrame(campaign1.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), 
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
campaign1_groupby_site=campaign1.groupby('Site')
campaign1_groupby_site.size()

df1=campaign1.groupby('Site').agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
df1['Rank']=df1.index
df1.Rank+=1
df2=df1[['Rank','Site','Impression','engagement','engRate']]
df2
df2.dtypes
def convertToJSON(df):
        resultJSON = df.to_json(orient='table')
        return(resultJSON)
result=convertToJSON(df2)

df1=Ilog_df.groupby('AdFormat').agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
df1['Rank']=df1.index
df1.Rank+=1
df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df2

df1=Ilog_df.groupby(['CampaignId','AdFormat']).agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
#df1['Rank']=df1.index
#df1.Rank+=1
#df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df1
def encodeAdFormat(x):
    if(x=='300x250'):
        return 0
    else:
        return 1


Ilog_df['AdFormat1']=Ilog_df['AdFormat'].apply(encodeAdFormat)


Ilog_df.sample()
df1=Ilog_df.groupby('AdFormat1').agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
df1['Rank']=df1.index
df1.Rank+=1
df2=df1[['Rank','AdFormat1','Impression','engagement','engRate']]
df2
df1=Ilog_df.groupby(['click']).agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
#df1['Rank']=df1.index
#df1.Rank+=1
#df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df1
df1=Ilog_df.groupby(['video-end']).agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
#df1['Rank']=df1.index
#df1.Rank+=1
#df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df1
df1=Ilog_df.groupby(['video-start']).agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
#df1['Rank']=df1.index
#df1.Rank+=1
#df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df1
df1=Ilog_df.groupby(['FoldPosition']).agg({'Unnamed: 0': 'count', 'engagement': 'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1.sort_values(by='engRate', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
#df1['Rank']=df1.index
#df1.Rank+=1
#df2=df1[['Rank','AdFormat','Impression','engagement','engRate']]
df1
campaign2 = Ilog_df[Ilog_df['CampaignId'] == 'fiwemi8']
df1=campaign2.groupby('Site').agg({'Unnamed: 0': 'count', 'engagement': 'sum','video-start':'sum','video-end':'sum','click':'sum','AdFormat1':'sum'})
df1.reset_index(inplace=True)
df1['engRate']=df1['engagement']/df1['Unnamed: 0'] 
df1['Score']=(df1['Unnamed: 0']/100)*((df1['engagement']*10 +df1['AdFormat1']+(df1['video-start']+df1['video-end'])+df1['click'])/df1['Unnamed: 0'])
df1.sort_values(by='Score', ascending=False, inplace=True,ignore_index=True)
df1.rename(columns={'Unnamed: 0':'Impression'},index={'ONE':'Row_1'},inplace=True)
df1['Rank']=df1.index
df1.Rank+=1
df2=df1[['Rank','Site','Impression','engagement','engRate','Score']]
df2<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup