Find Time difference Created Vs Resolved in dataframe

Question

I have a data frame with 3 columns Column 1 - Created Column 2 - Resolved Column 3 - Issue Type

I'm trying to figure out how much time has passed between Resolved and Created (days, hours, minutes, and seconds). When I try to do the same, I always receive the error below. Could someone please assist in cleaning these data and resolving them?

Sample data in dataframe

enter image description here

Error:

TypeError Traceback (most recent call last) ~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp) 162 try: --> 163 result = func(left, right) 164 except TypeError:

~/opt/anaconda3/lib/python3.9/site-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)

~/opt/anaconda3/lib/python3.9/site-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

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/computation/expressions.py in _evaluate_standard(op, op_str, a, b) 68 _store_test_result(False) ---> 69 return op(a, b) 70

TypeError: unsupported operand type(s) for -: 'str' and 'str'

During handling of the above exception, another exception occurred:

TypeError Traceback (most recent call last) /var/folders/k8/_5616sh16zs5g_n08g2sxk640000gp/T/ipykernel_13729/2306751620.py in ----> 1 Time = jiraDump['Resolved'] - jiraDump['Created']

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/common.py in new_method(self, other) 68 other = item_from_zerodim(other) 69 ---> 70 return method(self, other) 71 72 return new_method

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/arraylike.py in sub(self, other) 106 @unpack_zerodim_and_defer("sub") 107 def sub(self, other): --> 108 return self._arith_method(other, operator.sub) 109 110 @unpack_zerodim_and_defer("rsub")

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/series.py in _arith_method(self, other, op) 5637 def _arith_method(self, other, op): 5638 self, other = ops.align_method_SERIES(self, other) -> 5639 return base.IndexOpsMixin._arith_method(self, other, op) 5640 5641

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/base.py in _arith_method(self, other, op) 1293 1294 with np.errstate(all="ignore"): -> 1295 result = ops.arithmetic_op(lvalues, rvalues, op) 1296 1297 return self._construct_result(result, name=res_name)

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in arithmetic_op(left, right, op) 220 _bool_arith_check(op, left, right) 221 --> 222 res_values = _na_arithmetic_op(left, right, op) 223 224 return res_values

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _na_arithmetic_op(left, right, op, is_cmp) 168 # Don't do this for comparisons, as that will handle complex numbers 169 # incorrectly, see GH#32047 --> 170 result = _masked_arith_op(left, right, op) 171 else: 172 raise

~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/ops/array_ops.py in _masked_arith_op(x, y, op) 106 # See GH#5284, GH#5035, GH#19448 for historical reference 107 if mask.any(): --> 108 result[mask] = op(xrav[mask], yrav[mask]) 109 110 else:

TypeError: unsupported operand type(s) for -: 'str' and 'str'

`jiraDump = pd.read_csv('MongoDB Jira.csv', encoding='latin-1')` `Time = jiraDump['Resolved'] - jiraDump['Created']` — Bem, Jan 18 '23 at 11:03
The problem is that both columns are of type `str`. What you have to do is to convert both of them to `pd.to_datetime` before operating with them. [Reference](https://stackoverflow.com/questions/17134716/convert-dataframe-column-type-from-string-to-datetime) — tomasborrella, Jan 18 '23 at 11:04

score 0 · Accepted Answer · answered Jan 18 '23 at 11:20

you can use parse function from parser module in dateutil library. Once the datetime strings are parsed to datetime objects, you can get differnce

import pandas as pd
from dateutil.parser import parse

data = {'issue_type': ['Deployment', 'Access'],
        'Created': ['Oct 07 2022 10:08:17 AM UTC', 'Sep 12 2022 06:08:24 AM UTC'],
        'Resolved': ['Oct 07 2022 04:56:25 PM UTC', 'Sep 12 2022 04:29:30 PM UTC']}

df = pd.DataFrame(data)
print(df)

df['Created'] = df['Created'].apply(parse)
df['Resolved'] = df['Resolved'].apply(parse)
print(df)

df['delta'] = df['Resolved'] - df['Created']
df

Find Time difference Created Vs Resolved in dataframe

1 Answers1