Optimize Assign Value to Cells of a Column based on a couple of conditions in Pandas Data frame

user_3949511 0
Viewed 55

I am trying to assign values to some new columns in my data frame based on some conditions. But it is taking very long to execute.

First up, I tried using itertuples

for n in df1.itertuples():
    if (df2[(df2['x'] == n.x) & (df2['y'] == n.y)].empty):
        df1['new_col1'][n.Index] = 0.00
    else:
        df1['new_col'][n.Index] = df2[(df2['x'] == n.x) & (df2['y'] == n.y)]['value']

I also tried the same logic using map function

def foo(x,y):
    if (df2[(df2['x'] == x) & (df2['y'] == y)].empty):
        return 0.00
    else:
        return df2[(df2['x'] == x) & (df2['y'] == y)]['value']
map(foo, df1['x'],df1['y'])

Now, I am sure my code is nowhere near optimized, I tried multiple ways to optimize but they keep throwing one error or the other.

Any leads on how to optimize the code and reduce the execution time for the same.

pandas dataframe numpy python
1 Answers

Use pd.merge:

df1 = df1.merge(df2[['x', 'y', 'value']].rename(columns={'value': 'new_col'}),
                on=['x', 'y'], how='left').fillna({'new_col': 0})
print(df1)

# Output
   x   y  new_col
0  1  11      0.0
1  2  12     22.0
2  3  13     23.0

Setup:

df1 = pd.DataFrame({'x': [1, 2, 3], 'y': [11, 12, 13]})
df2 = pd.DataFrame({'x': [2, 3, 4], 'y': [12, 13, 14], 'value': [22, 23, 24]})
print(df1)
print(df2)

# Output
   x   y
0  1  11
1  2  12
2  3  13

   x   y  value
0  2  12     22
1  3  13     23
2  4  14     24
user_152399510
Related