I want to achieve following output as shown in Expected Output column right now I am doing this with the help of excel by applying following formula =IF(A2="Solved",C2-B2,TODAY()-B2)
How I will able to do this using python
| Status | Requested | Solved | Expected Output |
|---|---|---|---|
| Solved | 28-Apr-22 | 28-Apr-22 | 0.03 |
| Solved | 29-07-2022 18:43 | 29-07-2022 18:54 | 0.01 |
| Solved | 29-07-2022 18:43 | 29-07-2022 18:53 | 0.01 |
| Closed | 29-07-2022 18:23 | 29-07-2022 18:49 | 4.23 |
| Solved | 29-07-2022 18:09 | 29-07-2022 18:49 | 0.03 |
| Closed | 29-07-2022 17:51 | 29-07-2022 18:48 | 4.26 |
| Closed | 29-07-2022 17:32 | 29-07-2022 18:30 | 4.27 |
| Solved | 29-07-2022 17:29 | 29-07-2022 18:26 | 0.04 |
CodePudding user response：
Below the expected output will be displayed in days:
import pandas as pd
import numpy as np
from datetime import date
array1 = {'Status': ['Solved', 'Solved', 'Closed', 'Solved','Solved', 'Solved', 'Closed'],
'Requested': ['20000101', '20010101', '20020101', '20030101', '20040101', '20050101', '20060101'],
'Solved': ['20000103', '20010106', '20020108', '20030111', '20040112', '20050109', '20060101']}
df = pd.DataFrame(array1)
df['Requested'] = df['Requested'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d')) ##
df['Solved'] = df['Solved'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d')) ##
today = pd.to_datetime(date.today())
df['Expected Output'] = np.where(df['Status'] == 'Solved', (df['Solved'] - df['Requested']) / np.timedelta64(1, 'D'),
(today - df['Requested']) / np.timedelta64(1, 'D'))
df
Status Requested Solved Expected Output
0 Solved 2000-01-01 2000-01-03 2.0
1 Solved 2001-01-01 2001-01-06 5.0
2 Closed 2002-01-01 2002-01-08 7519.0
3 Solved 2003-01-01 2003-01-11 10.0
4 Solved 2004-01-01 2004-01-12 11.0
5 Solved 2005-01-01 2005-01-09 8.0
6 Closed 2006-01-01 2006-01-01 6058.0
The two lines marked with ## should be changed based on your input dates format