Every quarter I need to calculate the cost per lead/enquire (CPE) from real estate portals. From a complete table that has all the enquiries in the quarter, I need to filter first all enquiries that came from a sub-origin “portals” (specific column), then I need to count the ocurrencies for each portal (including the portals that had 0 enquiries), and then I need to divide each portal´s number of enquiries by the money spent in it to find its CPE (in a new created column).

I have been using Google Colab.

First I used this to filter data from enquiries table and then count portal ocurrencies, turning it into a dataframe:

enquiries_q1_24_portals = enquiries_q1_2024[(enquiries_q1_2024[‘Sub-origin’] == ‘Portal’)]
enquiries_q1_24_portals.groupby([‘Origin’]).size().sort_values(ascending=False)
enquiries_q1_24_portals = pd.DataFrame({‘Enquiries’: enquiries_q1_24_portals})
enquiries_q1_24_portals = pd.to_numeric(enquiries_q1_24_portals[‘Enquiries’], errors=’coerce’)

Then I used loc to separate the two necessary columns from dataframe “portal_costs” which has the spent values:

portal_costs = portal_costs.loc[:, [‘Origin’, ‘CostQ124’]]

And then I merged the two dataframes:

cost_per_enquire = pd.merge(enquiries_q1_24_portals, portal_costs, on = “Origin”, how = “inner”)

It worked well although it is still missing the portals with “0” enquiries:

Final dataframe with enquiries and costs

Then I used this to divide the columns “CostQ124” and “Enquiries”:

cost_per_enquire[‘CPE’] = cost_per_enquire[‘CostQ124’]/cost_per_enquire[‘Enquiries’]

My expectation was to see a final table with “Origin”, “Enquiries”, “CostQ124” and a new column “CPE”, but it returned the error below. Could anybody help me, it seems I am almost there but something is still missing…

Of course, if there is a simpler way of doing this I am totally open to it.

Thank you so much!

TypeError Traceback (most recent call last)
/usr/local/lib/python3.10/dist-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp)
170 try:
–> 171 result = func(left, right)
172 except TypeError:

10 frames
/usr/local/lib/python3.10/dist-packages/pandas/core/computation/expressions.py in evaluate(op, a, b, use_numexpr)
238 # error: “None” not callable
–> 239 return _evaluate(op, op_str, a, b) # type: ignore[misc]
240 return _evaluate_standard(op, op_str, a, b)

/usr/local/lib/python3.10/dist-packages/pandas/core/computation/expressions.py in _evaluate_numexpr(op, op_str, a, b)
127 if result is None:
–> 128 result = _evaluate_standard(op, op_str, a, b)
129

/usr/local/lib/python3.10/dist-packages/pandas/core/computation/expressions.py in _evaluate_standard(op, op_str, a, b)
69 _store_test_result(False)
—> 70 return op(a, b)
71

TypeError: unsupported operand type(s) for /: ‘str’ and ‘int’

During handling of the above exception, another exception occurred:

TypeError Traceback (most recent call last)
in <cell line: 27>()
25 cost_per_enquire = pd.merge(enquiries_q1_24_portals, portal_costs, on = “Origin”, how = “inner”)
26
—> 27 cost_per_enquire[‘CPE’] = cost_per_enquire[‘CostQ124’]/cost_per_enquire[‘Enquiries’]
28
29 display (cost_per_enquire)

/usr/local/lib/python3.10/dist-packages/pandas/core/ops/common.py in new_method(self, other)
79 other = item_from_zerodim(other)
80
—> 81 return method(self, other)
82
83 return new_method

/usr/local/lib/python3.10/dist-packages/pandas/core/arraylike.py in truediv(self, other)
208 @unpack_zerodim_and_defer(“truediv“)
209 def truediv(self, other):
–> 210 return self._arith_method(other, operator.truediv)
211
212 @unpack_zerodim_and_defer(“rtruediv“)

/usr/local/lib/python3.10/dist-packages/pandas/core/series.py in _arith_method(self, other, op)
6110 def _arith_method(self, other, op):
6111 self, other = ops.align_method_SERIES(self, other)
-> 6112 return base.IndexOpsMixin._arith_method(self, other, op)
6113
6114

/usr/local/lib/python3.10/dist-packages/pandas/core/base.py in _arith_method(self, other, op)
1346
1347 with np.errstate(all=”ignore”):
-> 1348 result = ops.arithmetic_op(lvalues, rvalues, op)
1349
1350 return self._construct_result(result, name=res_name)

/usr/local/lib/python3.10/dist-packages/pandas/core/ops/array_ops.py in arithmetic_op(left, right, op)
230 # error: Argument 1 to “_na_arithmetic_op” has incompatible type
231 # “Union[ExtensionArray, ndarray[Any, Any]]”; expected “ndarray[Any, Any]”
–> 232 res_values = _na_arithmetic_op(left, right, op) # type: ignore[arg-type]
233
234 return res_values

/usr/local/lib/python3.10/dist-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp)
176 # Don’t do this for comparisons, as that will handle complex numbers
177 # incorrectly, see GH#32047
–> 178 result = _masked_arith_op(left, right, op)
179 else:
180 raise

/usr/local/lib/python3.10/dist-packages/pandas/core/ops/array_ops.py in _masked_arith_op(x, y, op)
114 # See GH#5284, GH#5035, GH#19448 for historical reference
115 if mask.any():
–> 116 result[mask] = op(xrav[mask], yrav[mask])
117
118 else:

TypeError: unsupported operand type(s) for /: ‘str’ and ‘int’