Extract the last row in a dataframe with a timestamp before some given time

Question

Related questions: I need to take specific row from different df to generate new df. How can I make my code faster?

I want to take the last data before the specified time from different time intervals df:

generate_data is for demo purpose, selecte_time and get_result_df is for production application.

select_time and get_result_df needs to be run a few million times in total.

How can I make get_result_df execute faster? My code is as follows:

import numpy as np
import datetime

import pandas as pd

np.random.seed(2022)
durations = ['T', '5T', '15T', '30T', 'H', '2H', 'D', 'W', 'BM']
datas = {}
time_selected = None


def generate_data():
    global durations, datas
    start_dt = '2018-01-01'
    end_dt = '2022-05-02'
    for duration in durations:
        datas[duration] = pd.DataFrame(index=pd.date_range(start_dt, end_dt, freq=duration))
        datas[duration]['duration'] = duration
        datas[duration]['data'] = np.random.random(len(datas[duration])) * 100

    return


def selecte_time():
    global time_selected
    start_dt = datetime.datetime(2018, 3, 1)
    end_dt = datetime.datetime(2022, 5, 2)
    idx = pd.date_range(start_dt, end_dt, freq='T')
    time_selected = np.random.choice(idx)
    return time_selected


def get_result_df():
    global durations, datas, time_selected
    t_df = {}
    col = ['duration', 'data']
    for duration in durations:
        df = datas[duration]
        t_df[duration] = df[df.index <= time_selected][col].iloc[-1]
    df = pd.DataFrame(t_df[duration] for duration in durations)

    return df


def main():
    generate_data()
    selecte_time()
    df = get_result_df()
    print(df)


if __name__ == '__main__':
    main()

On my computer, the running time of get_result_df() is 204ms, how can I speed up the running speed of get_result_df()?

%timeit get_result_df()
204 ms ± 4.1 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

I optimized it, and the running time was reduced to 53ms. Is there any room for improvement?

def get_result_df():
    global durations, datas, time_selected
    t_df = {}
    col = ['duration', 'data']
    for duration in durations:
        df = datas[duration]
        dt = df.index.to_numpy()
        dt1 = dt[dt <= time_selected][-1]
        t_df[duration] = df[df.index == dt1][col].iloc[-1]
    df = pd.DataFrame(t_df[duration] for duration in durations)
    return df

%timeit get_result_df()
53.3 ms ± 7.75 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Answer 1

Don't use np.random; it's deprecated in favour of the new generator methods in the family of default_rng.

durations = [ should be DURATIONS = (, i.e. a capitalised tuple.

Don't leave datas or time_selected in the global namespace.

data is already a plural, so don't write datas.

Add PEP484 type hints.

Don't use np.random.random with a post-multiply; once you have an RNG instance call uniform passing 100 for your maximum.

selecte is spelled select.

selecte_time is deeply inefficient: you create an array of millions of elements, only to select one and throw the works away. Instead, calculate a random datetime between your two endpoints.

get_result_df is also deeply inefficient. Your inner loop should be using a bisection of the kind that search_sorted offers. Neither left nor right exactly matches what you're doing, so you have to check and conditionally decrement after the bisection.

col needs to go away.

I'm not convinced that it's a good idea to pass a generator expression like this:

df = pd.DataFrame(t_df[duration] for duration in durations)

into the DataFrame constructor. You can build up lists for your new index and data columns, and pass those in directly.

Add unit tests.

Suggested

Covering some of the above,

import numpy as np
import datetime
import pandas as pd
from pandas import Timestamp

rand = np.random.default_rng(seed=0)
DURATIONS = ('T', '5T', '15T', '30T', 'H', '2H', 'D', 'W', 'BM')


def generate_data() -> dict:
    datas = {}
    start_dt = datetime.date(2018, 1, 1)
    end_dt = datetime.date(2022, 5, 2)

    for duration in DURATIONS:
        datas[duration] = pd.DataFrame(index=pd.date_range(start_dt, end_dt, freq=duration))
        datas[duration]['duration'] = duration
        datas[duration]['data'] = rand.uniform(low=0, high=100, size=len(datas[duration]))

    return datas


def select_time() -> np.datetime64:
    start_dt = datetime.datetime(2018, 3, 1)
    end_dt = datetime.datetime(2022, 5, 2)
    range_hours = (end_dt - start_dt) / datetime.timedelta(hours=1)
    hour_selected = int(rand.integers(range_hours))
    time_selected = start_dt + datetime.timedelta(hours=hour_selected)
    return np.datetime64(time_selected)


def get_result_df(datas: dict, time_selected: np.datetime64) -> pd.DataFrame:
    index = []
    data = []

    for duration in DURATIONS:
        df = datas[duration]
        y = df.index.searchsorted(time_selected)
        while True:  # Executes between 1 and 2 times
            row = df.iloc[y, :]
            if row.name <= time_selected:
                break
            y -= 1
        index.append(row.name)
        data.append(row.data)

    df = pd.DataFrame(
        {'duration': DURATIONS, 'data': data},
        index=index,
    )
    return df


def main() -> None:
    datas = generate_data()
    time_selected = select_time()
    df = get_result_df(datas, time_selected)

    assert df.shape == (9, 2)
    assert tuple(df.duration) == DURATIONS
    assert tuple(df.index) == (
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 20:00:00'),
        Timestamp('2018-07-16 00:00:00'),
        Timestamp('2018-07-15 00:00:00'),
        Timestamp('2018-06-29 00:00:00'),
    )

    assert np.allclose(
        df.data,
        (
            2.41440894, 28.33886947, 56.0277365, 92.21785259, 84.13760397,
            44.99816704, 20.3228723, 6.17753546, 83.78495657,
        ),
    )

    print(df)


if __name__ == '__main__':
    main()

O(1) interpolation

Even a bisection is overkill. You already know that the temporal index is linear, so you can simply interpolate. This is O(1) in time.

import numpy as np
import datetime
import pandas as pd
from pandas import Timestamp

rand = np.random.default_rng(seed=0)
DURATIONS = ('T', '5T', '15T', '30T', 'H', '2H', 'D', 'W', 'BM')


def generate_data() -> dict:
    datas = {}
    start_dt = datetime.date(2018, 1, 1)
    end_dt = datetime.date(2022, 5, 2)

    for duration in DURATIONS:
        datas[duration] = pd.DataFrame(index=pd.date_range(start_dt, end_dt, freq=duration))
        datas[duration]['duration'] = duration
        datas[duration]['data'] = rand.uniform(low=0, high=100, size=len(datas[duration]))

    return datas


def select_time() -> np.datetime64:
    start_dt = datetime.datetime(2018, 3, 1)
    end_dt = datetime.datetime(2022, 5, 2)
    range_hours = (end_dt - start_dt) // datetime.timedelta(hours=1)
    hour_selected = int(rand.integers(range_hours))
    time_selected = start_dt + datetime.timedelta(hours=hour_selected)
    return np.datetime64(time_selected)


def get_result_df(datas: dict, time_selected: np.datetime64) -> pd.DataFrame:
    index = []
    data = []

    # Assuming that get_result_df has no knowledge of generate_data.
    # If it does, just pass these endpoints in.
    start_dt, end_dt = datas['T'].index[[0, -1]]
    target_fraction = (time_selected - start_dt)/(end_dt - start_dt)

    for duration in DURATIONS:
        df = datas[duration]
        y = int(target_fraction * len(df.index))
        for time, datum in df.data.iloc[y::-1].items():  # Executes between 1 and 2 times
            if time <= time_selected:
                index.append(time)
                data.append(datum)
                break

    df = pd.DataFrame(
        {'duration': DURATIONS, 'data': data},
        index=index,
    )
    return df


def main() -> None:
    datas = generate_data()
    time_selected = select_time()
    df = get_result_df(datas, time_selected)

    assert df.shape == (9, 2)
    assert tuple(df.duration) == DURATIONS
    assert tuple(df.index) == (
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 21:00:00'),
        Timestamp('2018-07-16 20:00:00'),
        Timestamp('2018-07-16 00:00:00'),
        Timestamp('2018-07-15 00:00:00'),
        Timestamp('2018-06-29 00:00:00'),
    )

    assert np.allclose(
        df.data,
        (
            2.41440894, 28.33886947, 56.0277365, 92.21785259, 84.13760397,
            44.99816704, 20.3228723, 6.17753546, 83.78495657,
        ),
    )

    print(df)


if __name__ == '__main__':
    main()