How to calculate the click-through rate
You could:
df['action'] = df.keyword.str.split('_').str.get(-1)df['keyword'] = df.keyword.str.split('_').str.get(0)df = df.set_index(['datetime', 'keyword', 'action']).unstack().loc[:, 'COUNT']df['ctr'] = df.click.div(df.pv)action click pv ctrdatetime keyword 2016-01-05 a 100 200 0.50 b 90 150 0.60 c 90 120 0.75
An alternative by using groupby:
df2['key_word'] = df2.apply(lambda x: x.keyword.split('_')[0], axis=1)df2['key_action'] = df2.apply(lambda x: x.keyword.split('_')[1], axis=1)def compute_ctr(g): ctr = g[g.key_action == 'click'].COUNT.values[0] / g[g.key_action == 'pv'].COUNT.values[0] result = {'datetime': g.iloc[0,0], 'ctr': ctr} return pd.Series(result)rslt = df2.groupby('key_word').apply(compute_ctr)rslt.reset_index(inplace=True, drop=False)print(rslt) ctr datetime keyword0 0.5 5/1/2016 a1 0.6 5/1/2016 b2 0.75 5/1/2016 c