How improve speed/memory of logic when obtaining the unique record of a cross joined table based on the dates of two different columns?

user_14739761 0
Viewed 39

I asked previously about obtaining the unique records of a cross-joined table based on the dates of two different columns? here: Previous Question, Context and Answer

Although the answer provided works:

import numpy as np

# df - your DataFrame

group = df.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'])

def foo(df):
    result = df.loc[df.P_DATE_ENCOUNTER>df.R_DATE_TESTED, ['R_DATE_TESTED', 'R_RESULT']].tail(1).reset_index()
    if not result.empty:
        return result
    else:
        return pd.DataFrame([[np.nan, np.nan, np.nan]], columns=['RECORD_ID','R_DATE_TESTED', 'R_RESULT'])


group.apply(foo)

My actual dataframe has +- 1,5 millions rows and this .apply takes two hours long and uses a lot of memory on the notebook which eventually crashes. Is there a memory-efficient to apply the same logic? I thought maybe using dense-rank or np.where?

pandas rank apply where-clause python
1 Answers

You can speed up the process by using all vectorized Pandas operations (Pandas built-in functions) which have been optimized to run fast by Pandas instead of using .apply() together with custom function which Pandas cannot optimize and run slowly.

We approach by firstly replace values of R_DATE_TESTED and R_RESULT to NaN by Series.where(). Then, use groupby() + last() to get the required aggregated entries, as follows:

  1. Replace values of R_DATE_TESTED and R_RESULT to NaN if R_DATE_TESTED < R_DATE_ENCOUNTER is NOT true, by using by Series.where():
df['R_DATE_TESTED'] = df['R_DATE_TESTED'].where(df['R_DATE_TESTED'] < df['P_DATE_ENCOUNTER'])
df['R_RESULT'] = df['R_RESULT'].where(df['R_DATE_TESTED'] < df['P_DATE_ENCOUNTER'])
  1. Use groupby() + last() to get the most recent (and Non-NaN) entries within each group:
df.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'], as_index=False).last()

Output:

   P_CLIENT_ID P_DATE_ENCOUNTER  RECORD_ID  R_CLIENT_ID R_DATE_TESTED  R_RESULT
0        25835       2016-12-21     302956      25835.0          None       NaN
1        25835       2017-02-21     302963      25835.0          None       NaN
2        25835       2017-04-25     302970      25835.0    2017-03-07      20.0
3        25835       2017-06-21     302977      25835.0    2017-03-07      20.0
4        25835       2017-09-04     302984      25835.0    2017-08-03      20.0
5        25835       2018-01-08     302991      25835.0    2017-08-03      20.0
6        25835       2018-04-03     302998      25835.0    2018-03-23      20.0
7        25835       2018-07-25     303005      25835.0    2018-03-23      20.0

Execution Time Benchmarking

1. Sample data size: 56 rows

Old solution:

%%timeit
group = df.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'])

def foo(df):
    result = df.loc[df.P_DATE_ENCOUNTER>df.R_DATE_TESTED, ['R_DATE_TESTED', 'R_RESULT']].tail(1).reset_index()
    if not result.empty:
        return result
    else:
        return pd.DataFrame([[np.nan, np.nan, np.nan]], columns=['RECORD_ID','R_DATE_TESTED', 'R_RESULT'])


group.apply(foo)


16.5 ms ± 94.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

New solution:

%%timeit
df['R_DATE_TESTED'] = df['R_DATE_TESTED'].where(df['R_DATE_TESTED'] < df['P_DATE_ENCOUNTER'])
df['R_RESULT'] = df['R_RESULT'].where(df['R_DATE_TESTED'] < df['P_DATE_ENCOUNTER'])
df.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'], as_index=False).last()

4.24 ms ± 146 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

With sample data size of 56 rows, new solution is nearly 4x times faster (16.5ms reduced to 4.24ms)

2. Enlarged data size: 1.68 million rows

df2 = pd.concat([df] * 30000, ignore_index=True)

df2.shape
(1680000, 6)       # 1.68 million rows

New solution:

%%timeit
df2['R_DATE_TESTED'] = df2['R_DATE_TESTED'].where(df2['R_DATE_TESTED'] < df2['P_DATE_ENCOUNTER'])
df2['R_RESULT'] = df2['R_RESULT'].where(df2['R_DATE_TESTED'] < df2['P_DATE_ENCOUNTER'])
df2.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'], as_index=False).last()

473 ms ± 7.37 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

New Solution takes less than 1 second to finish the processing.

As the enlarged dataset is mostly duplicated data, the run time may not be linearly proportional to the case for real data. Anyway, this shows the run time for new solution is still reasonable for large dataset.

user_150706970
Related