How to Fill a Column With the Value of Another Column Based on a Condition on Some Other Columns

how to fill a column with the value of another column based on a condition on some other columns?

Here's a verified solution:

idx_ = df[(df['col4'] < df['col1']) & (pd.notnull(df['col2']))].index
df.loc[idx_,'col4'] = df['col1']
df


+---+------+------+------+------+
|   | col1 | col2 | col3 | col4 |
+---+------+------+------+------+
| 0 |   22 | NaN  |   23 |   56 |
| 1 |   12 | 54.0 |   22 |   36 |
| 2 |   48 | NaN  |    2 |   45 |
| 3 |   76 | 32.0 |   13 |   76 |
| 4 |   23 | NaN  |   43 |    8 |
| 5 |   67 | 54.0 |   56 |   67 |
| 6 |   16 | 32.0 |   32 |   16 |
| 7 |    3 | 54.0 |   64 |    8 |
| 8 |   67 | 4.0  |   23 |   67 |
+---+------+------+------+------+

How to fill column based on condition taking other columns into account?

There is no need for a for-loop as you can use vectorized solutions in this case. Three options on how to solve this problem:

# option 1
test_df$vec3 <- +(test_df$vec1 <= 25 | test_df$vec1 >= 75)

# option 2
test_df$vec3 <- as.integer(test_df$vec1 <= 25 | test_df$vec1 >= 75)

# option 3
test_df$vec3 <- ifelse(test_df$vec1 <= 25 | test_df$vec1 >= 75, 1, 0)

which in all cases gives:

   vec1 vec2 vec3
1     5    1    1
2     6    2    1
3    61    3    0
4    20    4    1

....

47    3   47    1
48   55   48    0
49   44   49    0
50   97   50    1

(only first and last four rows presentend)

Fill values in pandas column with condition involving 2 other columns

The problem is not with np.where, the problem is that you are comparing the value directly against np.nan using !=

>>> np.nan == np.nan
False

So, use a function/method that allows you to check if the value is nan or not:

>>> df.C = df.B.where(df.A.notna(), np.nan)

            A         B         C
0       greek  0.030809  0.030809
1      indian  0.545261  0.545261
2         NaN  0.470802       NaN
3         NaN  0.716640       NaN
4  australian  0.148297  0.148297

Conditionally fill column values based on another columns value in pandas

You probably want to do

df['Normalized'] = np.where(df['Currency'] == '$', df['Budget'] * 0.78125, df['Budget'])

Pandas df: fill values in new column with specific values from another column (condition with multiple columns)

You can left join the dataframe to itself using col1 on the left side & col2 on the right side.

rename col3 from the right side of the join to col4 and drop the rest of the right side columns
example:

df = df.merge(df, left_on='col1', right_on='col2', how='left', suffixes=('', '_'))
df = df.rename(columns={'col3_': 'col4'})
df = df[['col1', 'col2', 'col3', 'col4']]

df looks like:

  col1 col2  col3  col4
0    a    b     1   NaN
1    b    c     2   1.0
2    c    d     3   2.0
3    d    e     4   3.0

Pandas: How to set values from another column based on conditions column-wise

Use indexing and map to replace letters:

df.iloc[:, 2:] = df.apply(lambda x: x[2:].map(x[:2]), axis=1)
print(df)

# Output:
   A  B  i  j  y  z
0  1  2  2  1  1  1
1  2  3  3  2  3  3

Setup:

df = pd.DataFrame({'A': [1, 2], 'B': [2, 3], 'i': ['B', 'B'],
                   'j': ['A', 'A'], 'y': ['A', 'B'], 'z': ['A', 'B']})
print(df)

# Output:
   A  B  i  j  y  z
0  1  2  B  A  A  A
1  2  3  B  A  B  B

Details:

For each row, apply the following function over index axis so x contains the whole row at each iteration:

Map the value from the third column (x[2:] <- i, j, y, z) to the index from the two first columns (x[:2] <- A, B) like a dictionary (a Series can act as dictionary, check the map method)

For the first iteration:

A    1  # <- index A
B    2  # <- index B
i    B  # <- value B
j    A  # <- value A
y    A  # <- value A
z    A  # <- value A
Name: 0, dtype: object

SQL Add column with conditional values from another column

select does not change the data in tables. This looks like an use case for a temporary view but unfortunately SQL Server does not support temporary views.

So instead of first creating and filling #tempt with the structure and data from [dbo].[MainTable] then adding three columns to it and then filling them (using update) with calculated values better add and calculate them within the select list of the initial query which creates #tempt. You will have it all done in one go. Here is my suggestion:

select *,
  case when Called in ('Missed', 'No Answer', 'Voicemail', 'Disconnected') then 1 else 0 end as IsColumn,
  case when left(specific_column_a, 2) = '__' then 1 else 0 end as IsTest, 
  case when left(specific_column_b, 2) = '__' then 1 else 0 end as ThisColumn 
into #tempt
from [dbo].[MainTable];

You may see this SO thread too.

Automatically fill a column based on some criteria from other columns

Non-VBA Solution

Consider two formulas:

1. =SWITCH(MATCH(MAX(B2:D2),B2:D2,0), 1, "R", 2, "SF", 3, "DF")
2. IF(MAX(B2:D2)>=90,"C",IF(MAX(B2:D2)>=80,"A",IF(MAX(B2:D2)>=60,"T","")))

---

The first formula will provide you with the column index (based on the input range) that is associated with the MAX value. You can then use SWITCH to convert the index - in this instance, we convert to a string. The conversion is as follows:

Column Index:Output = 1:R, 2:SF, 3:DF

The second formula is nested IF statements which gets evaluated from left to right. Once your criteria is met, the rest of the formula will not be evaluated so you only need to check for Greater Than criteria rather checking for In Between as you explained.

---

If you want the output in one cell you can just combine the formulas with & as shown below. I have also shared a photo of the setup I used so you can relate the formula to the ranges

A2 = SWITCH(MATCH(MAX(B2:D2),B2:D2,0), 1, "R", 2, "SF", 3, "DF") & IF(MAX(B2:D2)>=90,"C",IF(MAX(B2:D2)>=80,"A",IF(MAX(B2:D2)>=60,"T","")))

---

Related Topics