Compare two pandas DataFrames in the most efficient way

Question

Let's consider two pandas dataframes:

import numpy as np
import pandas as pd

df = pd.DataFrame([1, 2, 3, 2, 5, 4, 3, 6, 7])

check_df = pd.DataFrame([3, 2, 5, 4, 3, 6, 4, 2, 1])

If want to do the following thing:

1. If df[1] > check_df[1] or df[2] > check_df[1] or df[3] > check_df[1] then we assign to df 1, and 0 otherwise
2. If df[2] > check_df[2] or df[3] > check_df[2] or df[4] > check_df[2] then we assign to df 1, and 0 otherwise
3. We apply the same algorithm to end of DataFrame

My primitive code is the following:

df_copy = df.copy()
for i in range(len(df) - 3):
    moving_df = df.iloc[i:i+3]
    if (moving_df >check_df.iloc[i]).any()[0]:
        df_copy.iloc[i] = 1
    else:
        df_copy.iloc[i] = -1
df_copy


    0
0   -1
1   1
2   -1
3   1
4   1
5   -1
6   3
7   6
8   7

Could you please give me a advice, if there is any possibility to do this without loop?

Answer 1

IIUC, this is easily done with a rolling.min:

df['out'] = np.where(df[0].rolling(N, min_periods=1).max().shift(1-N).gt(check_df[0]),
                     1, -1)

output:

   0  out
0  1   -1
1  2    1
2  3   -1
3  2    1
4  5    1
5  4   -1
6  3    1
7  6   -1
8  7   -1

to keep the last items as is:

m = df[0].rolling(N).max().shift(1-N)
df['out'] = np.where(m.gt(check_df[0]),
                     1, -1)
df['out'] = df['out'].mask(m.isna(), df[0])

output:

   0  out
0  1   -1
1  2    1
2  3   -1
3  2    1
4  5    1
5  4   -1
6  3    1
7  6    6
8  7    7