Iterating over dataframe and replace with values from another dataframe

Question

I have 2 dataframes, df1 and df2, and df2 holds the min and max values for the corresponding columns.

    import numpy as np
    import pandas as pd

    df1 = pd.DataFrame(np.random.randint(0,50,size=(10, 5)), columns=list('ABCDE'))
    df2 = pd.DataFrame(np.array([[5,3,4,7,2],[30,20,30,40,50]]),columns=list('ABCDE'))

I would like to iterate through df1 and replace the cell values with those of df2 when the df1 cell value is below/above the respective columns' min/max values.

jezrael jezrael · Accepted Answer · 2019-12-03T14:10:52

First dont loop/iterate in pandas, if exist some another better and vectorized solutions like here.

Use numpy.select with broadcasting for set values by conditions:

np.random.seed(123)
df1 = pd.DataFrame(np.random.randint(0,50,size=(10, 5)), columns=list('ABCDE'))
df2 = pd.DataFrame(np.array([[5,3,4,7,2],[30,20,30,40,50]]),columns=list('ABCDE'))

print (df1)
    A   B   C   D   E
0  45   2  28  34  38
1  17  19  42  22  33
2  32  49  47   9  32
3  46  32  47  25  19
4  14  36  32  16   4
5  49   3   2  20  39
6   2  20  47  48   7
7  41  35  28  38  33
8  21  30  27  34  33

print (df2)
    A   B   C   D   E
0   5   3   4   7   2
1  30  20  30  40  50

#for pandas below 0.24 change .to_numpy() to .values
min1 = df2.loc[0].to_numpy()
max1 = df2.loc[1].to_numpy()
arr = df1.to_numpy()

df = pd.DataFrame(np.select([arr < min1, arr > max1], [min1, max1], arr), 
                  index=df1.index, 
                  columns=df1.columns)
print (df)
    A   B   C   D   E
0  30   3  28  34  38
1  17  19  30  22  33
2  30  20  30   9  32
3  30  20  30  25  19
4  14  20  30  16   4
5  30   3   4  20  39
6   5  20  30  40   7
7  30  20  28  38  33
8  21  20  27  34  33
9  12  20   4  40   5

Another better solution with numpy.clip:

df = pd.DataFrame(np.clip(arr, min1, max1), index=df1.index,  columns=df1.columns)
print (df)
    A   B   C   D   E
0  30   3  28  34  38
1  17  19  30  22  33
2  30  20  30   9  32
3  30  20  30  25  19
4  14  20  30  16   4
5  30   3   4  20  39
6   5  20  30  40   7
7  30  20  28  38  33
8  21  20  27  34  33
9  12  20   4  40   5

Iterating over dataframe and replace with values from another dataframe

1 Answers