How to calculate the click-through rate

Question

Here is an example, I have this data;

    datetime    keyword COUNT
0   2016-01-05  a_click 100
1   2016-01-05  a_pv    200
2   2016-01-05  b_pv    150
3   2016-01-05  b_click 90
4   2016-01-05  c_pv    120
5   2016-01-05  c_click 90

and I'd like to transform it to this data

    datetime    keyword ctr
0   2016-01-05  a       0.5
1   2016-01-05  b       0.6
2   2016-01-05  c       0.75

I can transform data with dirty codes but I'd like to do it the elegant way.

Answer 1

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   ctr
datetime   keyword                  
2016-01-05 a          100  200  0.50
           b           90  150  0.60
           c           90  120  0.75