How to calculate click speed

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 would like to convert 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 convert data with dirty codes, but I would like to do it in an elegant way.

Answer 1

You can:

 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 

Answer 2

Alternative with 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 keyword 0 0.5 5/1/2016 a 1 0.6 5/1/2016 b 2 0.75 5/1/2016 c