How To Replace Values In A Range In A Pandas Dataframe With Another Value In The Same Dataframe Based On A Condition

I want to replace values within a range of columns in a dataframe with a corresponding value in another column if the value in the range is greater than zero. I would think that a

Solution 1:

Not sure how to do it in Pandas per se, but it's not that difficult if you drop down to numpy.

---

If you're lucky enough so that your entire DataFrame is numerical, you can do so as follows:

import numpy as np

m = df.as_matrix()
>>> pd.DataFrame(
    np.where(np.logical_or(np.isnan(m), m > 0), np.tile(m[:, [4]], 5), m), 
    columns=df.columns)
    A   B   C   D   column_with_value_I_want
0   22  22  22  22  22
1   0   0   0   0   15
2   0   0   0   0   90
3   10  10  10  10  10
4   0   0   0   0   NaN
5   0   557     557     0   557

---

• as_matrix converts a DataFrame to a numpy array.
• np.where is numpy's ternary conditional.
• np.logical_or is numpy's or.
• np.isnan is a check if a value is not nan.
• np.tile tiles (in this case) a 2d single column to a matrix.

---

Unfortunately, the above will fail if some of your columns (even those not involved in this operation) are inherently non-numerical. In this case, you can do the following:

for col in ['A', 'B', 'C', 'D']:
    df[col] = np.where(df[col] > 0, df[col], df.column_with_value_I_want)

which will work as long as the 5 relevant columns are numerical.

This uses a loop (which is frowned upon in numerical Python), but at least it does so over columns, and not rows. Assuming your data is longer than wider, it should be OK.