Performance issue on updating a Pandas DataFrame with Series based on DateRange

Question

I have two Pandas data frames: one with Daily data and one with Weekly data. I want to add the weekly data to each row of the daily data for each group of column A.

For example, for each row on the daily data frame from 2022/07/04 to 2022/07/09, I want to add the weekly data from 2022/07/04 for each group of column A and so on.

The code below reproduces the desired result:

1. Generate the data

import pandas as pd
import numpy as np

def generate_df(date_range):
    tf_dict = []
    for A in range(0,4000):
        for d in date_range:
            tf_dict.append({"A":A, 
                               **{f"B_{i}":np.random.randint(0,10) for i in range(1,250)}, 
                               "datadate": d})

    return pd.DataFrame(tf_dict)

# Daily Dataframe
daily_range = pd.date_range(start='1/1/2022', end='2/15/2022', freq='D')
df_daily = generate_df(daily_range)

# Weekly Dataframe
weekly_range = pd.date_range(start='1/1/2022', end='2/15/2022', freq='W')
df_weekly = generate_df(weekly_range)
df_weekly = df_weekly.add_prefix("higher_tf_")

Note that I took a range of 4000 for A for simplification. But with real data, A is close to 8000

2. Create the date range on the weekly data frame so it makes it easier to filter on the daily data frame

dfs = []
for i, dfg in df_weekly.groupby("higher_tf_A"):
    dfg = dfg.sort_values("higher_tf_datadate")
    dfg["higher_tf_next_date"] = dfg["higher_tf_datadate"].shift(-1)
    dfs.append(dfg)
df_weekly = pd.concat(dfs)

(I added the next date to the previous row, so I have a date range on the same row)

3. For each weekly data, create a mask grouping on 'A' and the date range. Then update the daily rows with the weekly data.

%%time
for index, row in df_weekly.iterrows():
    mask = (df_daily["A"]==row["higher_tf_A"]) & \
        (df_daily['datadate'] >= row['higher_tf_datadate']) & \
        (df_daily['datadate'] < row['higher_tf_next_date'])
    
    df_daily.loc[mask, row.index] = row.values

Results of %%time: CPU times: user 1min 59s, sys: 4.34 s, total: 2min 3s Wall time: 2min 4s

How can I improve the last code to decrease execution time?

Note that the timeframes can change (e.g. Hourly and daily, minutes and hours, ...)

Answer 1

Broadly speaking, you've missed two of the most important mantras in Pandas:

• Don't use loops. No, seriously. Don't use loops.
• There's a thing for that.

Your generate_df is very slow. Replace it with a two-dimensional random matrix initialisation in one pass. Certainly A, and probably datadate should be index levels and not columns. There should probably also be an index level for B producing a dataframe with a triple-level index and one column, but that's beyond the scope of this question.

Don't use m/d/yyyy format; use ISO 8601.

Don't build up a dfs list only to call concat on it, don't call iterrows, and don't do manual date range comparisons. Make one call to merge_asof.

Don't use np.random.randint; that's deprecated.

Suggested

import pandas as pd
import numpy as np
from numpy.random import default_rng


rand = default_rng(seed=0)


def generate_df(
    freq: str,
    start: str = '2022-01-01', end: str = '2022-02-15',
    n_a_vals: int = 4000, n_b_cols: int = 250,
) -> pd.DataFrame:
    date_range = pd.date_range(start=start, end=end, freq=freq, name='datadate')
    index = pd.MultiIndex.from_product((
        pd.Series(data=np.arange(n_a_vals), name='A'),
        date_range,
    ))
    return pd.DataFrame(
        rand.integers(size=(len(index), n_b_cols), low=0, high=10),
        columns=[f'B_{i}' for i in range(n_b_cols)],
        index=index,
    )


df_daily = generate_df(freq='D')
df_weekly = generate_df(freq='W')

merged = pd.merge_asof(
    left=df_daily.sort_values('datadate'),
    right=df_weekly.sort_values('datadate'),
    by='A', on='datadate',
    suffixes=('', '_higher_tf'),
).set_index(['A', 'datadate']).sort_index()

print(merged)

Output

                 B_0  B_1  ...  B_248_higher_tf  B_249_higher_tf
A    datadate              ...                                  
0    2022-01-01    8    6  ...              NaN              NaN
     2022-01-02    2    2  ...              4.0              4.0
     2022-01-03    0    3  ...              4.0              4.0
     2022-01-04    1    7  ...              4.0              4.0
     2022-01-05    5    0  ...              4.0              4.0
...              ...  ...  ...              ...              ...
3999 2022-02-11    7    0  ...              3.0              1.0
     2022-02-12    0    5  ...              3.0              1.0
     2022-02-13    8    8  ...              5.0              1.0
     2022-02-14    5    8  ...              5.0              1.0
     2022-02-15    1    9  ...              5.0              1.0

[184000 rows x 500 columns]

Runs in about three seconds.