Speeding up a Python datetime comparison to generate values

eatkimchi 0
Viewed 35

I want to do this in a pythonic way without using 1) a nested if statement and 2) the use of iterrows.

I have columns

Date in | Date Out | 1/22 | 2/22 | ... | 12/22
1/1/19    5/5/22
5/5/22    7/7/22

for columns like '1/22', I want to insert a calculated value which would be one of the following:

  1. Not Created Yet
  2. Closed
  3. Open

For the first row, column 1/22 would read "Open" because it was opened in Jan/22. This would continue until column 5/22, in which it would be labeled "Closed."

For the second row, column 1/22 would read "Not Created Yet" until 5/22 which would read "Open" until 7/22 which would have the value "Closed."

I don't need the full table necessarily, but I want to get a count of how many are closed/open/not created yet for every month.

Here is the code I'm using which works, but just takes longer than I think it could:

table={}
for i in mcLogsClose.iterrows():
    table[i[0]] = {}
    for month in pd.date_range(start='9/2021', end='9/2022', freq='M'):
        if i[1]['Notif Date'] <= month:
            if i[1]['Completion Date'] <= month:
                table[i[0]][month]="Closed"
            else:
                table[i[0]][month]="Open"
        else:
            table[i[0]][month]="Not Yet Created"

I then want to run table['1/22'].value_counts()

Thanks for your attention!

comparison analytics datetime pandas python
1 Answers

1. Using a loop

# The date range you are calculating for
min_date = pd.Period("2022-01")
max_date = pd.Period("2022-12")
span = (max_date - min_date).n + 1

# Strip the "Date In" and "Date Out" columns down to the month
date_in = pd.to_datetime(df["Date In"]).dt.to_period("M")
date_out = pd.to_datetime(df["Date Out"]).dt.to_period("M")

data = []
for d_in, d_out in zip(date_in, date_out):
    if d_in > max_date:
        # If date in is after max date, the whole span is under "Not Created" status
        data.append((span, 0, 0))
    elif d_out < min_date:
        # If date out is before min date, the whole span is under "Closed" status
        data.append((0, span, 0))
    else:
        # Now that we have some overlap between (d_in, d_out) and (min_date,
        # max_date), we need to calculate time spent in each status
        closed = (max_date - min(d_out, max_date)).n
        not_created = (max(d_in, min_date) - min_date).n
        open_ = span - closed - not_created
        data.append((not_created, closed, open_))

cols = ["Not Created Yet", "Closed", "Open"]
df[cols] = pd.DataFrame(data, columns=cols, index=df.index)

2. Using numpy

def to_n(arr: np.array) -> np.array:
    """Convert an array of pd.Period to array of integers"""
    return np.array([i.n for i in arr])

# The date range you are calculating for. Since we intend to use vectorized
# code, we need to turn them into numpy arrays
min_date = np.repeat(pd.Period("2022-01"), len(df))
max_date = np.repeat(pd.Period("2022-12"), len(df))
span = to_n(max_date - min_date) + 1

date_in = pd.to_datetime(df["Date In"]).dt.to_period("M")
date_out = pd.to_datetime(df["Date Out"]).dt.to_period("M")

df["Not Created Yet"] = np.where(
    date_in > max_date,
    span,
    to_n(np.max([date_in, min_date], axis=0) - min_date),
)
df["Closed"] = np.where(
    date_out < min_date,
    span,
    to_n(max_date - np.min([date_out, max_date], axis=0)),
)
df["Open"] = span - df["Not Created Yet"] - df["Closed"]

Result (with some rows added for my testing):

  Date In  Date Out  Not Created Yet  Closed  Open
0  1/1/19    5/5/22                0       7     5
1  5/5/22    7/7/22                4       5     3
2  1/1/20  12/12/20                0      12     0
3  1/1/23    6/6/23               12       0     0
4  6/6/21    6/6/23                0       0    12
Code Different0
Related