samurait samurait - 6 months ago 27
jQuery Question

Modify data frame: calculate click through rate

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

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